Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)

nd get them in order so you can see which productsgenerate the most sales. You also want to figure out which customer accountsfor the highest percentage of total sales. We can get this done faster than it takes to make a cup of coffee. Let's get started. (upbeat music) In this example, we have sales and quantity data by product, customer and company. Now, the first thing you need to do before you insert a Pivot Table is to make sure that your data is organized in a proper format. This means it should bein a tabular data format like you see here. Each column has a header. You shouldn't have emptycolumns in the middle and you also shouldn't have empty rows in the middle of your data set. And I do think you shouldn't have all total values in the middle of this data set. Once you can check all these boxes, you're ready to insert your Pivot Table. To do that, you can go to Insert, and select PivotTable from here. But check this out for the Range is hard coding it to SAS3 SKS108. This is until where I have data. If I add more lines to this, they will not be consideredin my end report, I will have to update my range and I'd rather avoid this step. So what I need to do before I insert a Pivot Table is to turn my data set intoan official Excel table. This has the added advantage that whenever I get new data added, my Pivot Tables willautomatically reflect that the moment I press Refresh,. And all I have to do is click on this button here that's called Table or use the shortcut key Ctrl+T. Excel figures out thatthis table has headers, you can go with OK. And if there's automatic table layer that Excel gives you bothersyou like it bothers me, you can go and remove it, just select None. But what I also prefer to do is to give my table a name so that I can recognize it later on. You can do it from thisTable Name box here. Now that we have a table, we can automaticallysummarize with a Pivot Table, or go back to Insertand click on PivotTable. Check what happens now, we get the name of our table. As our table expands with new data they will automatically beincluded in our Pivot Table. But before I do this, I want to show you even an easier way of inserting a Pivot Table, and that's to click onRecommended PivotTables. These are actually quite smart, they might just give youexactly what you're looking for, or they might help you get a faster start. So for example, here Ihave the sum of sales USD by customer name, one of the reports I want tocreate has sales USD in it. So I'm going to go with that, just select it and click on OK, you get your Pivot Tableinserted on a new sheet. This is the result of my table, it does all the aggregations for me, I didn't have to write asingle formula to do this. Now on the side here weget the Pivot Table fields. But you can see I have them organized wherethe fields are on one side, and the boxes that definethe layout of my Pivot Table on the other side. That's an option you can select here. The default is the first one, Fields Section that Stacked. Now, if you want to have morespace for your fields here, you can go and select FieldsSection and Area Section, side by side. Now, what you see here are allthe headers of your dataset. That's why each columnneeds to have a header. What Excel went ahead and did, when you inserted your Pivot Table, is that it put the salesUSD in the value section, and customer name in the row section. Now, if for some reason, you don't want to seethe sum of sales USD, but instead you'd rathersee something else, for example, the average, you can click on this drop down, go to Value Field Settings and change your selection from here. So I go with Average and OK. Now, you can also change that from here, right mouse click is yourbest friend in a Pivot Table. If I right mouse click, I can also select ValueField Settings from here, I'm going to put this to Sum and go with OK. Now, another thing you can getto with a right mouse click is the Number Format. So let's say I want to add 1000 separator to my numbers here, I can select Number Format. So don't select Format Cells because that just formatsthe underlying cell, Number Formats stays with yourPivot Table as it expands. So I'm going to go with Number, use 1000 Separator andzero decimal places. Now, you can adjust andupdate this Pivot Table as you see fit. So for example, if I wanted to look atthe different regions, I could decide to addthe region in the filter. And then when I click on this drop down, I can select a specific region. Or I can activate multi select, which in this casedoesn't really make sense, because I just have two items, and you can also leave it on All. Now, if you decide that youdon't want to have Region in the filter, you can bring it to the column section. And now we have customername by region, here. We also automatically get thegrand totals on this side. Now, if you have another level, so let's say we're going tobring product description to the rows here, we also get subtotals. Now, here's where you might want to update the design of your Pivot Table. So for example, under Grand Totals, youmight decide to deactivate it for either to rows or the columns or for everything if youdon't want to see it at all. So if I go with Off, it completely removes my grand totals. If I just activate it for Columns Only, I get the grand total on the bottom here. You might also want to dothe same thing for subtotals. If I select Do Not Show Subtotals, it's going to remove them from here. Now, as for the layout of your report, the layout that I personally prefer is to show in a tabular form. This gives me column headersfor each of the fields here. Now, another thing you might want to do is to fill in the gaps here. Under report layout, you could select Repeat All Item Labels, and that repeats everythingand fills in the gaps. Now I'm just going to remove that. And now let's take alook at the button here. So you have the ability to collapse fields or expand fields. If you want to take away this ability, you can go to PivotTable Analyze, and under Show click on this toggle here, and it takes away the Buttons, if you click again, it brings them back. Now, the field list, this one here, if for some reason you close that, and you want to get it back, you can do that by going back to Show and selecting the fields list from here. Now, I'm just going to puteverything back to the way I had it. So I don't want region. Whenever you don't want something, you can just kick it out, just drag and drop it heresomewhere in the fields list. I also don't want the product description. So I just want customer name by sales USD. One other thing you might want to do is to update the header here. So I don't want anyoneto see sum of sales USD, let's say I just wantthem to see sales USD. When I press Enter Excel doesn't like it if a header here is identicalto a field name here. So if you want it to be identical, what you need to do is eitheradd a space after the name or before the name, or you can completely change the name. So we could just call this Sales instead of Sales USD. Also for Grand Total, I can just call this Total. Okay, so let's also updatethe design of this Pivot Table by selecting a different style. So you have a lot of options here and you even have the ability to create your own Pivot Table style, if you'd like. Now, one thing I'd like to do here is to sort this, so just right mouse click,Sort, Largest to Smallest. Now, notice that the PivotTable columns collapse, there is an option for this, you can actually remove Autofit if you don't want this to happen. So right mouse click, go to Pivot Table options, on the bottom here you haveAutofit column with an update, let's take away that checkmark and go with OK. Now, one thing I originally wanted was to get the salespercentage by customer as compared with the total. And let's say I also want to see the complete sales values here. Well, what I can do is to bring in the Sales USD a second time into my Values field. But now check this out, I'm going to right mouse click, Show Values As and select % of Grand Total. But check out all the otheroptions that you have. If you get a chance, go ahead and try these because they can come in handywhen you're creating reports. Now, I'm going to call this Sales % Okay, so our first report is done. Before we test whetherthis updates properly if we add new data to our table, let's go ahead and createour second Pivot Table. The way I personally prefer to do that is to copy an existing Pivot Table, go to the side and paste it in and then adjust what I need. This gives me a head start because the Pivot Tableformatting comes with. So in this second report, I don't need the sales percentage, what I want is the product by sales and I want it sorted. So let's right mouse click, Sort, Largest to Smallest. Okay, so my Pivot Tablesare actually done. But I just want to show youone thing before we wrap up. And that's how easy itis to add Slicer buttons to your Pivot Table. So for example, let's say for region, instead of having it in the filter here and selecting from drop down, what we could do is add it as a slicer. So while it's in the field list, you can right mouseclick and Add a Slicer. And it adds these buttons here that you can organize anywhere you want. And when you click on it, it filters your report to show the values in your slicer. Now, for your slicer you also get slicer options here so you can update the design for this, you can also update how you want it shown by adjusting the number of columns, the height and the width of your slicer. Now, if you insert a slicer, and you want to connectit to another Pivot Table, you just have to go to the Pivot Table that doesn't have that slicer, go to PivotTable Analyze, under Filter Connections place a checkmark for the slicer. This is our region slicer, so when I click on OK, this one is also connected to my new slicer. In case you want to select all regions, you can activate multi select here, or you just hold down Control while you make your selection. Okay, so as a last step, we're just going to make sure that everything updates automatically the moment we get new data in here. So I'm just going to expand this, let's add a new product, let's also add a new customer. Now let's go back toour Pivot Table report and all we have to do is pickany of these Pivot Tables, right mouse click and Refresh. Because they have the same pivot cache, they're all going to updateautomatically together. We can see the new customer added here and our new product added in the middle, because we have our sales datasorted in descending order. Okay, so this is how easy it is to create Pivot Tables in Excel. That was a quickintroduction to Pivot Tables, but with all the explaining, I was a bit slower than ittakes to make a cup of coffee. But I was probably just in time for you to enjoy coffee. Before I sign off, let's summarize thebenefits of Pivot Tables. Pivot Tables help you getanswers from your data, without you having towrite complex formulas. It's very fast to create a Pivot Table and it's really easy to use one. It helps you find relationships between your data and you can also visualizethese have with a pivot chart. But that's a topic for another video. Thank you for tuning in. Make sure you subscribe, hit that notification bell, and I'm going to seeyou in the next video. (upbeat music)