Learn how to use Excel Pivot Tables in 15 minutes

flight so a pivot table can summarize that data in many ways let's start a pivot table so I'm going to click on one cell that's not blank doesn't matter which one I'm going to click on the insert tab up at the top insert then I'll click uh where it this pivot table here and then we'll say from the table range and then usually it guesses the proper range for you in this case it did it says from A1 to j204 now if you want to pick a different range you can do that but usually that is the correct range and then typically we'll put the pivot table on a new worksheet so I'll click okay notice how we're on a new sheet now we start on the sheet that was called pivot table data and now we're on sheet one or the next available sheet but the data still comes in the sheet that's called pivot table data notice your field names on the right hand side that's why I kept on saying it was so important to have the field names on the top of the data because the pivot table uses this okay the whole point of the pivot table is to summarize your data let's say I want to get a summary by office that's going to give you a big clue so I'll pick up the word office here and drag the the word office into the rows just like that now we see the pivot table on the left and each row is a different office office if I want to get a subtotal for each office then I'll move something into the value section here usually in the value section we have some kind of number field for that reason I'm going to scroll down in the fields and pick up the word total and drag the total into the value section now I have a subtotal for each office so I mooved the office field into the rows and the total field into the value section now have a subtotal for each office so we're already starting to get some results from the pivot table now this is a pivot table that we're looking at on the left but usually it has the columns going across as well now these sections over here are all optional you don't have to put anything in those sections but many times we'll have something in the column section so I'll pick up the word Airline from the fields and drag it into the columns like that now this is what people would usually call a pivot table when it has the columns and the rows I think it gives you a lot of information I have a cross reference for each office for each Airline I have Road totals on the right if I scroll down I have column totals at the bottom and even a grand total so there's a basic pivot table and now let's go further with it you can move your fields around to your different sections to to get different pivot tables and each section could have as uh as many fields as you want so this commission field is going to say yes or no whether that sales salesperson got commissioned for that sale or not so I'll pick up the word commission and drag it down here into the rows below the office now look what happened on the left each office is broken down into the Nos and yeses so we have a new lower of detail so each of these sections could have as many fields as you want and it's going to take them in the top down order notice in the row section office is first and commission is second let's see what happens if I switch those fields I'll pick up the word office and drag it below the word commission within the Rose now commission is uh first look what happened on the left now all the NOS are together and all the yeses are together so the order does matter it's going to take them into the ton order let's see what happens if I move the word commission into the columns I'll pick up the word commission from the rows and drag it to the columns now the Nos and yeses are going across for each Airline so you can see what happens when you have more than one field in each section uh then you have more layers of detail in your pivot table each section could have as many fields as you want and it's going to take them in the top down order now I'm going to remove the commission field for the columns I'll just pick up commission and drag it back to the fields here and now it's back to the original way so it's pretty easy to move your Fields Back and Forth now let's talk about how we're going to change the calculation that you see on your pivot table if you notice in cell A3 it says sum of total so these are all sums I'm going to come over here to the right and click on the word sum of total in the value section I'm going to click on sum of total and I'll pick on value field settings and then we have this window here we have sum count average maximum minimum product standard deviation and variance pretty soon I'll show you uh more than that so I'll pick on the account now if you follow my mouse I'm going to change the custom name to the work account whatever you type here in the custom name will show up over here on the the table so I'll click okay now we have counts instead of sums we have a different report let's try that again I'm going to click on the word count here in the value section and I'll go back to where it says value field settings and we're back to this window so I'll pick in the word sum now I'll change the custom name to the word sum and another thing I can do in this window is change the number format for that field so I'll pick in the word number format and then you have your classic number format window so I'll choose currency and click okay I'll click okay again and now we're back to sums and we have currency format what if you wanted to see the sum and the count at the same time then you would move more Fields over here into the value section so I'll pick up the word total again from the fields and drag total down here into the values a second time notice now I have uh two sums so I want to change one of those I'm going to come over here to the right and click on the second sum of the value section see the second sum I'm going to click that and click on value field settings now I'll make that one a count and change the custom name to the word count and I'll click okay so notice now I have the sum and the count so when you want more than one calculation in your pivot table you'll move more Fields over here into the value section each one could have their own value field settings and you could have as many as you want now let me show you other calculations that are built into the pivot table so I'll click in the word count this time in the value section and I'll cck in the word value field settings and we're back to this window this time I'll picking the word THM now if you follow my mouse I'm going to click where it says show values as right there show values as and then we get this other window I'm going to click on this pull down and then you get other calculations so you should try some of at some point there some pretty good ones in there I'm going to choose percent of column total and I'll change the custom name to say percent of total so when you're on this window if you pick in the first tab you get things like sum count and average if you click on the second tab then you can click on this pull down and you'll get other calculations I choose percent of column total on this one there I could also change the custom name which is going to wind up on the pivot table and you can also change the number format so I'll click okay now if you notice for each Airline I have the original sum and the percent of total so when you want more than one calculation in your pivot table you'll move more Fields over here into the value section each one could have their own value field settings and you can have as many as you want so that'll help you with your calculations in your pivot table now I'm going to remove the percent of total I'll pick up the word percent of total from the values and just drag it back to your uh Fields good now it's also important to know how to sort the pivot table you can sort on any column you can also sort on the rows so let's say I want to sort by the grand total I'm going to right click on a number in the grand total column right click and then picking the word sort and I'll say sort largest the smallest and then the bigger grand total are up at the top going down from there so you can sort on any column just right click on the number in the column that you want to sort and you pick in the word sort in a pivot table we can also sort across notice how the airlines in row four are going across alphabetically What If instead I want the airlines to go across where Minneapolis had their best airline going across from there so I'm going to right click on a number in the Minneapolis row and picking the word sort this time I'm going to say more sort options and then top to bottom means we're going to sort the column and left to right means we're going to sort the row so I'll choose left to right and click okay now Minneapolis had their best airline with USA and then United and then Delta so just right click on any number and picking the word sort and then we'll say more sort options and then here you can get smallest to largest or largest to smallest top to bottom means you're going to sort the column and left to right means you're going to sort the row so I'll click okay now if you follow my mouse over here I'm going to click on the pull down where it says row labels then I can sort and filter the office so I'll say sort A to Z there and I'll click on the pull that where it say column labels and I'll say sort A to Z there now everything is normal order so the normal sort and filter is built right in now here's another way to filter the pivot table it's called a slicer so when you click on the pivot table I'm going to click in the pivot table analyze tab up at the top and then here I'll pick on insert slicer and your slicers come from your Fields so I'll choose uh the destination and the commission you can choose as many as you want there I'll click okay then the slicers give you another way to filter the pivot table so if I click on Cho it's only going to show me the ones from Chicago or in this case it chose Dallas and you can use those with your other filters as well so that gives you another way to filter the data that's called a slicer to get all the record back I'll click on the uh the X over here now one more way to filter the pivot table is called a timeline and a timeline allows you to do a date range so I'll click on the pivot table I'll click on the pivot table analyze tab I'll pick on insert timeline and a timeline is always based on a date field so I'll choose a flight date and I'll click okay here's the timeline I'm going to move it over here notice how it's organized by month but I can click on the word months there and then you can organize it by year quarter month or a day so I'll choose years and now it's organize by year so I'll choose 2014 and the numbers changed again so the timeline allows you to do a filter by a date range and we have our other slicers and the other filters over here to get to the exact records that we're looking for to get everything back again I'm going to click on the X on the slicer windows and pick on the X on the in this course I want to show you how you can size your data with a pivot table so a pivot table can take a huge amount of data and summarize it in many ways now you want your your data to look like this it's very important that your field names are up at the top we can call those column headed field names and if you scroll down it should be one continu of data there shouldn't be any blank rows into the Bottom now this could be thousands and thousands of rows you want that to be continuous with no blank rows into the bottom and no blank columns in this course I want to show you how you can summarize your data with a pivot table so a pivot table can