Excel PivotTables Made Easy - And Why Things Go Wrong!

if you're new to pivot tables you might think they're scary or complicated so in this video i'm going to show you how easy pivot tables are to build now be sure to watch to the end where i cover the common mistakes that people make which leads them to thinking pivot tables are difficult here i've got some sales data categorized by order id customer id salesperson then we have order priority sku ship mode order date and sale amount naturally i'm interested in analyzing these sales amounts by the different criteria now i could use formulas like sumifs and countifs but it's way quicker to use a pivot table in other words a pivot table is an easy way to summarize large amounts of data before we can create a pivot table i want to point out the important attributes of this data first notice each column has a column label and there's only one row of column labels there aren't any merged cells or columns without a label and the column labels become the fields you can work with in your pivot table the data here is in what we call a tabular layout that is each column contains a single type of data and i don't have multiple columns containing the sales amounts broken out by month or sales person for example that's the job of the pivot table now as i scroll down you can see i also don't have any subtotals or grand totals or any blank rows now i've formatted this data in an excel table and you can see it's called table1 and while this is optional the benefits of this is that when you add new data you don't need to update the pivot table's reference to the data because the table range automatically grows to include it and we'll see that in a moment all right we're ready to insert our first pivot table start by selecting your data or if you have a contiguous range of data like mine or formatted in a table just select one cell and then excel will be able to find the bounds of that table if you're new to pivot tables you might like to try the recommended pivot tables available on the insert tab of the ribbon this opens the dialog box with some examples based on your data in the left you get the small thumbnails and clicking on one gives a slightly bigger view in the right hand pane if there's one there that you like simply click ok and it will be inserted on a new worksheet alternatively you can click a blank pivot table and it will open up a new worksheet ready for you to build a pivot table i'm going to click cancel and we'll use the pivot table button on the insert tab and this will allow me to choose where i place the pivot table you can see it's automatically detected my table and has placed the table name in the table range field here i can choose to put my pivot table on a new worksheet now i recommend this but in this example i'm going to place it on an existing worksheet because i want to put it just here beside the data so we can see it in context i'll click ok and excel has put a placeholder in the worksheet and on the right hand side we have the field list i'm just going to left click and drag it out over here so that i can zoom in notice the field list simply contains the column names from the source data the bottom half of the fill pane contains the areas in the pivot table to build the pivot table simply left click and drag the fields into the pivot table areas let's say i want to summarize the sale amount by salesperson and did you notice there i just checked the box for sale amount and excel has automatically put that in the value area it's done that because my sale amount column has numbers in it so it's assuming that i want to add that up and it's assumed correctly and with that i have my sales amounts by salesperson this is the equivalent of a sumif formula except i haven't had to write any formulas the pivot table has done all the hard work for me now let's say i change my mind and i want to see the sales people across the columns and the sales broken down by date i can left click and drag to move the sales people from the rows to the columns area and then i'm going to drag the order date down into the row area notice the dates have been grouped by month and i now have a new field called month in my field list excel 2016 onward does this grouping automatically but you can change it by the group menu just right click on one of the date cells and then group and in here you can see there are other grouping options alternatively you can ungroup by the right-click menu as well i'm going to leave it as is now i think you'll agree that so far it's been super easy to create this summary table and move the sales person fill from the rows to the columns but imagine if you created this table using formulas and then you needed to change the layout it'd be a nightmare now another great tool we have in pivot tables is the ability to change the aggregation by default it's set to sum the data but if i right click i've got summarize values by and you can see sum is checked but i can also count average max min product and in more options we also have count numbers standard deviation and variances let's take a look at counting i'll click ok and now it's counting the sales amounts by salesperson and month of course most reports aren't static you're likely to get new data that you want to include in your pivot table report we can see here this one only goes up to november on this sheet here i've got some data for december so let me select that i'm just going to ctrl c to copy it we'll go back to the sheet where my source data is i'm going to go to the very end of the table and on the next empty row i'm going to ctrl v to paste you can see the table has expanded and if we go to the very end of the table you can see it's got the table icon in the bottom right corner that's indicating to me that my table range has updated to include the december data and now all i need to do is go back to my pivot table right click and refresh and if you watch here we're going to get new data for december and it's that easy and this is because i referenced the table as the source data for my pivot table another thing we can do is nest row and column labels for example let's say we want to see the ship mode and order priority i'm just going to drag the date fields out and then check ship mode and order priority and now i have the count of sales amounts by ship mode and order priority of course i can right click and change the aggregation to average let's say for example and now my data is averaged the great thing about summarizing your data with pivot tables aside from how quick and easy they are to build and change is there's no risk of formula errors can you imagine building this with formulas well with that said let's look at some common reasons why people find pivot tables difficult the main reason for pivot table problems is the wrong data layout here i've got the same data but instead of the values all in one column they're spread across separate columns for each month essentially this data is already pivoted now notice there's also a column for the grand total of the rows and if i scroll down to the very end you can see there's a grand total for the columns now you can insert a pivot table using this data format but you'll quickly find that it doesn't work the way you'd expect let's take a look i'm going to insert a pivot table and we'll place it on this worksheet here that i've got set up the first thing you're going to notice is in the field list we have a separate field for every month and the grand total so we'll go ahead and build the same pivot table we want it by salesperson and then we want the sales amounts of january february march april may june you've got to check them all one by one which is annoying in itself i'll just move the field list down a little not only is it very inefficient to build pivot tables like this but it means you also can't use some of the other pivot tools available for example notice there's no grand total for the rows now i had a grand total in my source data so i can check the box for that field and add it but it's not ideal notice i also have a row containing blank this is the grand total row in my source data so if we go back and have a look at the bottom of my table i've got a grand total row so it's effectively double counting my data because the sum of bob john and richard's sales is 503 479 47 for january then it's counting the grand total row and double counting it in the pivot table grand total and the reason it's blank is because in the source data there's no sales person on the grand total row another problem is if i want to change the aggregation to say average i have to right click summarize values by average and then i have to do it again for february march april and may and so on and you get the idea not to mention the grand total is wrong as well now if you build pivot tables using data in this type of format it can be very frustrating because you're able to build what looks like a working pivot table but it results in lots of annoying issues that can leave those new to pivot tables completely baffled to the point they end up thinking pivot tables are complicated another common problem people run into is what i call the semi-report layout this is where your data is in a partial report format here you can see this data has some nested column labels spread over two rows it's already been pivoted so the units and order amounts are in separate columns for the different countries and there are columns for the totals plus it has a blank column smack bang in the middle of it so you simply cannot build a pivot table from this data remember you can't have any blank columns and your column labels must be in one row the correct layout for this data looks like this where you have a column for the country label and then the units and order amounts are in a column each and from here you can easily create the layout shown in the semi report let's take a look we'll insert a pivot table i'm going to pop it here so that we can see it beside the data now i'm just going to hide this so that we have more room so i want order date and i don't want it grouped so i'm just going to control z to ungroup it straight away then order id salesperson we want the countries across the top and then i want to sum the units and the order amounts now it's done some nesting in the layout so if we go to the design tab i'm going to choose tabular format and we're going to turn off the subtotals and there we have pretty much exactly the same report as we see here except created with a pivot table and i can do things like repeat these labels so right click field settings layout and print repeat item labels and we can spend a little bit of time formatting the headings but for the most part it's exactly the same report except now i can do much more with the data because my source data is in the correct layout so you can see when your data is formatted even slightly wrong it can make building pivot tables difficult the good news is that data in the wrong format is easily fixed with power query there's a video coming up next and a link in the video description on how to use power query to fix incorrect data layouts using the unpivot tool so be sure to check that out well i hope you're excited to try building your own pivot tables you can get started by downloading the excel file for this lesson from the link here if you like this video please give it the thumbs up and subscribe to my channel and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 81,897

Rating: 4.9826903 out of 5

Keywords: excel pivottables, pivottables, pivot tables

Id: vQlFiLUaw4k

Length: 13min 18sec (798 seconds)

Published: Wed Sep 09 2020