Like it came with the class. If you have yet to do that, go ahead and pause the video and grab that and come right back. Alrighty. Welcome back. Hopefully you grabbed that sample file and you're ready to go. I'm currently on the creating table sheet of this file. And let's go ahead and start talking about a list, a list of data in Excel. So list of data is simply data that you put on a spreadsheet. And what we did here is we put in some headers and each header describes the data below it. Notice how we have email there's their email address. Notice they have a phone extension. We have a phone extension and location describes the location, what building.
They're in and so on and so forth. Now this list of. We have a bunch of tools up here that we can use to analyze it. Like maybe in our data tab, we can go ahead and turn on a filter which we'll cover, or we can sort the data alphabetically or numerically, or we could split up the data. Now a list of the data is great, but Microsoft Excel has this data analytic tool known as a table. So what we can do is we can convert a list into a table. We could insert a table into Excel and convert this list into a table..
Now, what does that do for us? What does converting a list of data do? Well, when you convert it to a table, it's a data analytic tool. You get a set of features that you did not have with the list to analyze that data for yourself. There's several ways you can convert this to a table. More importantly, there's just about two that I'm going to show you and one's faster than the other. And I just want to show you the difference between the two. So really quick, in order to convert your list into a table, you should have three properties for your list of data. Property. Number one, to convert this list into a table is you should go ahead and have.
Headers headers describing the data, set below, notice how we have pay rate. And then we have higher date, location, and phone extension, email, and so on and so forth. So these headers, they have to be unique amongst each other. Meaning I can't have to hire dates. I can't have to pay rates. That doesn't mean I can't have a pay rate one or a pay rate zero. I add a little numerical value at the end, but they must be unique amongst each. Now that's guideline one uniqueness amongst the headers..
So that way our functions and our calculations can come out correctly. When we use the table. Now that we have the guidelines, let's go ahead and see how we can convert this list into a table. So just in case you forgot, let's do a little wrap up of those three guidelines, guideline. Number one, we must have. And the headers have to be unique compared to each other. Guideline. Number two is your headers must be unique compared to the dataset and guideline. Number three, you must have a complete data set for the calculations using.
The table to come out correctly. Alrighty. Let's format this list into a table. So we have to insert the data analytic tool known as a table into Excel. Notice how we have an insert tab here. So everyone let's do this together, but first be sure your cursor is inside of the dataset. Like, so, and then go ahead and run up to your insert tab and give it a click inside the insert tab. I just want to expose you to the tables command. Where you can find in the third option, the data analytic tool known as the table..
I'm going to go ahead and hover over it to expose the definition that Microsoft gave it, create a table to organize and analyze a related data tables, make it easy to sort filter and format data within a sheet. Sounds nice. I'm going to go ahead and click that option again. I want to point out that my cursor is within the dataset and give it a click just like that. I got to create table box here and because I had my cursor inside of that complete dataset, it found the entirety of the data. And notice how there's a check mark box here, asking if.
Your table does have headers. And in fact it does. So I'm going to leave that on and hit. Okay. But before we hit, okay guys, and gals, let's make sure that we know the quick way of doing things and the long way in case we forget the quick way. So let's go ahead and X out the create table box. Notice how I hovered over here. And it said table control, plus T in prints. That's because this has a keyboard shortcut to activate that icon on your screen, without you going to the insert tab and clicking on it..
So let's say you're on your home tab and you remember the fact that if I hold control and the letter T you will have the ability to activate the insert tabs, create table icon. And it just did that for us. Now that we know the short way of creating a table, let's all go ahead and hit, okay. To make this list into a table. All right. Alrighty. Now that we converted this, listen to a lovely table, let's go ahead and discuss some features. This data analytic tool has to offer. The first thing we want everyone to do is now that we have this list into a table, I want to point out that we have this lovely table..
Posts Related:
Formatting has banded rows. Let's go ahead and click inside of the table and notice how we have a design tab that.
And give it a click. This design tab has all the tools the table has to offer. Now, if you go ahead and click away from the table, the design tab will go away. It's one of those contextual features, a contextual tab based off the context you're using the software and may or may not be there. Now, I'm going to go ahead and click on the table. And based off this context, I have a design tab inside of the design tab. I want to point out one of the features that this table has to offer is a filter..The filter button can be turned off. I'm going to go ahead and turn that off. Go ahead and try that too. Just to give it a click and notice how these little arrows went away from each of my header names. Cause that was the filter option. So let's turn back on the filter and go to these arrows and notice how the filter is not only a filter down here. Notice how you can unselect an option and then filter for the criteria as you want. So all the employees getting paid 9 25 and nine 50. And go ahead and hit. Okay. And it filtered the table. And the only indicator that you had at this table is filtered is.
That little funnel right there. And if you send a file filtered, they'll receive it filtered. So they can't accidentally miss out a lot of work. If you leave a file filtered without letting them know that, Hey, this is filtered. So another giveaway that this file is filtered is notice how the table goes from 3 21 35 and 36, because all this filtering is doing is hiding rows from us based off the criteria we give it. So let's go back to this filter here, and let's clear the filter now is how the.
Option to clear the filters right there. And I want to just expose you to another form of filtering. This table has to offer, which is known as a slicer. So I'm going to go ahead and go to the design tab of the table and give it a click. If it's not there, just to make sure your cursor's inside that table and go to insert slicer inside of the tools command. I'm going to go ahead and give that a click. And this time I'm going to build a slicer for the location column here. And matter of fact, I'm going to do one for the department too..
Cause you can do two at once. And once you're satisfied, go ahead and click. Okay, I'm going to go ahead and give that a click. Now, these slicers are another way of filtering this data set here. So check it out. I'm going to move these to the side, just to zoom out of my spreadsheet a bit as well. And if I want to build her, if I would like to filter for my location, I can just select one of these options and it filters my table. Now, if I want her to do more than one option, I'd turn on the multi-select. Go ahead and try that out and select another option. And then you can go ahead and use this one to filter for departments..
Notice how it's also applying the funnel here. I don't recommend to turn off the design tabs filter button and the design tab. I don't recommend to turn those off because it lets it know what's called them as being filtered here using these slices. So a table is a great way to go ahead and sort your data alphabetically with these filter options, as well as a great way to filter the table, both filtering using the filter button as well as a visual way of filtering using the slicers. Go ahead and play around with the table and the slicer a bit and see.
What you can build the table to be based off the criteria as you gave it. Alrighty. Hopefully you're having fun playing around with those slicers, the purity. Cool and nifty. If you haven't been exposed to them yet, now let's go ahead and clear the filters from these slicers just by hitting that little X in the side of the slicers and make sure to turn off the multi-select just to keep them clean and consistent. And now that we have the ability to filter data, I want to let you know that this table does have the ability to find the duplicate values within it and remove them. Notice how in the design tab, go ahead and put your cursor inside the table.
And go to the design tab and give it a click in the design tab you have. Some tools and one of the options is removing a duplicate. So let's go ahead and build a duplicate in this table so I can show you how that can be done. I'm going to scroll all the way to the bottom of my table, here to the 40th row. And I'm going to go ahead and select row 40, and I'm going to go to my home here and I'm just going to copy the row and then I'm going to go to row 41. And I'm just going to go ahead and paste the row. Alrighty. Now, once I pasted the row, I'm going to go ahead and extend my table..
Cause if you remember, my table went to I 40 and notice how that little blue indicator right there stopped itself. So I'm going to go ahead and extend my table to go into row 41 there, because sometimes it does not extend itself. Like it just did it there. And now that I have my table extended, I'm going to go ahead and go to my design tab. I just want to show you the option to remove duplicates. So delete duplicate rows for me. You can pick which columns should be checked for duplicate information. So remove duplicates, notice that we have every column over here..
You don't have to have every column selected. You can use one of the columns that uniquely describe each of the rows.
So notice how my employee IDs describe each row based off this unique value. If I find an employee ID that's not unique, that's going to imply that I have to have the same employee in there. So you're allowed to go ahead and unselect everything and just search based off one criteria, that one unique value. And once you go ahead and do that and you hit, okay, go ahead and do that yourself again. This was in the design tab under remove duplicates, and then we got here..Okay. It says one duplicate value found and removed 37 values remain. I'm going to click. Okay. And show you that it removed everything pertaining to. That duplicate value. So it removed a second instance of 1999 and it removed everything related to it. Go ahead and give that a try, adding in a duplicate and removing it. And it's not that you could have done this without converting your list of data into a table so that you have the design tab, because notice how the option to remove duplicates is in the design tab..
I want to just let you know that you also have the option remove duplicates in your data tab. That's if you had a list of data and you had not converted it into a table, you would have used this route inside of the data tab should want to go ahead and expose you to that. Go ahead and add a duplicate into the table and remove it just to get a muscle memory and for that. And if you have time, go ahead and pause the video and maybe try it out. What they list of data instead of a table, looking at the custom sorting sheet on.
The sample file provided for the class. I want to go ahead and cover a useful feature in Excel. Now, sometimes there'll be building a spreadsheet and you want to build additional columns of data. Like for example, notice how I have a last name column, and I have a first name column over here. I want to build a full name column, looking at it and looking at how many employees I have. Okay. 52. That can be a pretty tedious task to write out all these full names..
And if you know a various other functions you can use, you can use concatenate or concat, but that together, but since Excel 2013, there's this feature known as FlashFill that I want to expose you to. So we can automatically just fill in all these data sets with the values we want within a flash. If we give it one example, I want to go ahead and expose you to the fact that in your data tab, that's all go to the data tab by giving it a. We have some data tools. Yeah, I know. I said data and then I said data. I do that all the time..
Well, in the data tools, you have an option for FlashFill. I'm going to go ahead and show you what the definition Microsoft gave it as FlashFill shortcuts control E for enter automatically fill in values, enter a couple of examples you want as an output and keep the active cell in the column you want filled. Let's go ahead and run through an example of that. So everyone let's go ahead and go to cell D four here, and let's give it an example of what we want. We want to put Hazel and we want to put Gorton, I'm going to hit.
Enter it on this blank cell. I gave it my example. I want it to go ahead and do the rest for me. So let's all go to our data tab and just give a clash. Fill. Pretty cool. How it did that for us, fill that every single name for us, how this worked is it looked over to the column, to the left hand side of it and try to see if you're doing a pattern here. It's like, oh, you did this in this together. I think you're going to do the same thing here..
And it just replicated it for us now that we expose you to that. I want to show you what else FlashFill can do. Cause it could save you time if you know this. So let's all undo this action by going up here and clicking, undo or hitting the shortcut control plus Z to undo the FlashFill. Now that we undid the FlashFill, let's give it another example. This time I want to capitalize the first name and I want to leave the last name as is. So this time I'm going to hit enter, and I know the shortcut it's control plus the letter E..
I'm not going to run up to my data tab and click on FlashFill because I'm just going to use the shortcut instead. So you should try that too. So I'm gonna hit control E and now I just showed you that, uh, not only does it find patterns within the spreadsheet and mimics them for you, it also can do formatting for that pattern as well. Like capitalizing the word, go ahead and get flash Villa, try, expose yourself to it, and think of all the great ways you can save a bunch of time using FlashFill on your spreadsheets. We are currently on the custom sorting sheet of the sample soil provided for you in this class..
So sometimes you'll be analyzing data and you'll realize that if you sort the data certain way, it'll be quicker for you to retrieve the information you're asking for. For example, if you're a manager trying to contact employees, it'd be nice to have a list of all the employees. Days off put together so that you're not contacting employees on their days off, or maybe if you're a manager dealing with a bunch of employees across different locations to have it sorted based off the location here and see all the employees in one location together. Or if you need to see a bigger picture and you see all the employees across.
All locations, it'd be nice to have an alphabetical list of everyone inside of this dataset and alphabetical order. So that way you have a quick way of finding people. What you're asking is to sort the data set. And I want to let you know that if you have a filter icon on the list, meaning if you go to your data tab and turn on the filter icon, go ahead and do that for yourself. You have these arrows that pop up that are filters, and we discussed earlier that there also sort options for the column below. Now that's one way of sorting, but I want to let you know that in your data tab,.
If you did not have this filter on, you also have the ability to sort columns. Notice you have a Z to a and an a to Z. Let's go ahead and run through an example of how this works with the full name column here. I want to sort it alphabetically. All you have to do is you have to put your cursor inside of the column. You don't have to drag and highlight a range. You don't have to sit there and select the entire column. Just the cursor, just that cell address box should be inside of the column that you want sorted. Once you have that in there, you're going to run up to the data tab..
It had a to Z, I want to point out that it sorted every single piece of data pertaining to that column with it. So we have everything in order still. It's just alphabetical based off the full name. Now that we have the ability to sort a certain column. What if we want it to sort based stuff, priority levels. Meaning I first wanted to sort on my full name and then after I have all my full names in alphabetical order, I want it to sort based off the locations put together, or maybe I want it to first have all my. Zones put together. And then after I see all my zones in order, try to put the.
Locations in the best order possible based off of those zones. And then once those are all in order, I want to go ahead and maybe see that list in alphabetical order with the employee's full names. So what you're asking for is the first sort on the zone, then go ahead and sort based off the location after the zone, and then after all that, make that list in alphabetic order, based off the full name, you're asking for a multi-level sort pretty easy to do. It's actually right next to the H to Z and Z to AI icons. Notice how we have a big, old sort box here. This is where you go ahead and do multi-level sorts..
Let's go ahead and do that exact example. So first by zone then by location, then after we do that, let's go ahead and do it by full name. So I'm going to click that icon sort in my data tab and I have the full name column sort right over here. It's already in there full name based off the cells values, not the color, not the. In alphabetical order. I'm going to go ahead and add in a second level of sorting, except I want to go ahead and do this one based off the zone that we were talking about, the cells values a to Z, but I said that I wanted my full name..
That'd be the last level. I want it to be first priority zone. So let's go ahead and click this up arrow to move that priority up first, give it a click. Great. So the zone full name. Well, I still need that extra location level here. So I'm going to go ahead and add in that third level, you should, to that level, I'm going to use zone and this time I'm already location and then full name and then I'm going to hit, okay. So now what this data said is all the zones are going to be put together. This is everyone in Alaska..
Now, after that, it's going to put all the buildings together. This is all building ones. There may be some discrepancies here because of course it's based off the zone for. But the reason why I did this as typically, if they're in the same zones, good chance to get me in the same building right now, considering the fact there's only three buildings after all that it's an alphabetical order. So the first person in that zone's name is going to be starting with an E that's an alphabetical order. So go ahead and play around with multi-level sorting in the data tab and see what ways you can sort the data to make it useful for you..
Alrighty. We are currently on the subtotal sheet provided in the sample file. If you have yet to grab that, go ahead and grab that and go to the subtotal sheet and get started. Alrighty. Hopefully you grabbed the file on your back. So let me give a little scenario for you. You're having a lovely day at work. Not much work to do. Matter of fact, it's Friday and it's around that time where you were kind of checking out, but your manager comes in and he's like, Hey, I have a spreadsheet. It's actually our pear wine distribution sales or margins or quantities current. At the current state, I just pulled it from the database and I need you to find, build some data for me..
I want you to find all our red sales of wine. And then after that, I want you to find maybe all our white sales of wine. And I want you to build this summary sheet so that we can see all our subtotals for the different categories that we have. And you're sitting there as like great. Now I have to go sit there and turn on a filter and then filter for the reds and find a calculation for sales and margin or quantity. And then I have to go ahead and do the same thing for whites. And that's going to take so long because I have to do six different calculations and six different filters, unless you know how to use a feature.
To calculate the subtotals for you. Now, I want to point out the fact that subtotally how it works before we even dive into it. I just want to discuss how it works. So subtotally, there's this feature in Excel known as sub subtotally. And what it allows us to do is we get to get a. And the software is going to run through the column and it's gonna be like, okay. Okay. Okay. And once it notices a change in that column's value. So right here, it went from red to white. It's going to say, what function do you want me to run on another column?.
Do you want me to sum the sales from this point? Do you want me to sum or maybe find the average of the margins or do you want me to find the max of the quantity or the sum of the quantity? What function do you want me to run on another column? So this being said, in order for subtotal linked to find all the reds and all the lights together, we first need to sort our columns that we want to find the subtotal for. So we want to sort the type column. So let's go ahead and do that. So step one, for every sub totaling, as you must have that column. For it to calculate the subtotal correctly..
So that's, before we even touch that icon, we show you where it is you call them has to be sorted. And if you do need to refer to the sorting videos, it's right before this, to see how you can sort a column. This is just the single level. Sor I'm just going to go ahead and click my cursor in here and just go up to my data tab and I'm going to sort it from a to Z. And now that I did the sort, I'm ready to expose you to the sub totaling option in Excel. Well, we're going to deal with data. So it's still going to be in the data tab. I want to point out that you have a command group all the way to.
The right, with an outline option, outline the outline command group. And one of the options over here is up total. So what the column sorted, don't forget that first step, that important step that's in the data tab a to Z. Let's all. Go ahead and click on subtotal. Now, keep in mind. It didn't really matter where my cursor was. It didn't have the, have the whole data set highlighted, as you can see it highlighted it for me the day the cursor or the cell address box, of course did have to be inside of the dataset just anywhere within the dataset..
Now we get to fill in the sub total box instead of calculating each one of those subtotals ourselves using the various methods that we know. So in each change in let's all, go ahead and change that the type, because that was the column we just sorted and that's the column, this is all red. And then it breaks the white when we need it to. And we want to go ahead and sum up. So I'm going to go ahead and do the sales, the margin and the quantity. If I did have subtotals that I had done before this, I have the option to replace those subtotals with these current subtotals. Since I don't have one to begin with, it doesn't matter that.
This is checked marked on. So I'm just going to leave it. You can too, and I'm going to just hit. Okay. And you should too, now that we hit, okay. I want to expose you to the fact that on the left-hand side, You have three different views of the workbook views. We call them. I'm going to go ahead and click on the first view here. Now it shows you the grand total and I'm no, of some of us are thinking, why is it show pound signs that it not work? The pound signs just mean the columns too small to show you the values within it just quickly go up and between the column and do two clicks, two clicks,.
And I'll show you the value inside of it. So, first sheet is going to show you the grand total. This is all the total inside the type column. We have 45,000 in sales, 21,000 in margin. And that's our quantity. There's for red and white. Let's all. Go ahead and expose ourselves to view number two, by giving it a click you number two is actually the breakdown thing we wanted. The red sails, the white, and then the grand total, and each value we wanted..
Now view number three. This is pretty cool. It's actually the complete dataset, but if you go ahead and scroll. You're going to notice a, an extra row. Well, few extra rows added in, so I'm just scrolling through it, going down the reds. Oh, there we go. Once the red ended it plopped in on row 180 1 red total sales margin and quantity. Let's go ahead and scroll through it. You're going to notice down here, we have the white, and then we have the grand total. So these are all the three views that we have. And all we needed to do was know that in our data tab, we have this lovely ability.
To calculate subtotals on a sorted column. Go ahead and play around with sub totaling, try out other columns to subtotal like that. Let's see here, try another column to subtotal. Maybe go ahead and do the groups and see the breakdowns of each group. And then after that, maybe try the part. Let's go ahead and pause the video, play around with it and see what other subtotals you could come up with and expose yourself to the three. You're currently on the charting one sheet on the sample file provided for the class. You have yet to grab that, go ahead and pause the video.
And get that come right back. Welcome back. So let's go ahead and start talking about some charts and Excel 2019 first things. First, I'm going to highlight this data set. I'm just going to use a shortcut. I'm going to click my cursor within the dataset, hold control and hit the letter a for all, to highlight all of the data set. And let's talk about inserting some charts and notice how we have an insert tablets, all go to it. Let's give it click on insert. I want to expose you to the charts command group. We have various types of charts that you can insert to analyze the data set. Visually you can go ahead and do a column, align a pie chart, all.