Why use Excel Pivot Tables? If you want to get insights from your data or create reports really fast, you're going to need Pivot Tables. Let's say you receive this data set, you need to figure out
the total sales by product and get them in order so you can see which products
generate the most sales. You also want to figure out which customer accounts
for 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 be
in a tabular data format like you see here. Each column has a header. You shouldn't have empty
columns 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 considered
in 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 into
an official Excel table. This has the added advantage that whenever I get new data added, my Pivot Tables will
automatically 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 that
this table has headers, you can go with OK. And if there's automatic table layer that Excel gives you bothers
you 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 this
Table Name box here. Now that we have a table, we can automatically
summarize with a Pivot Table, or go back to Insert
and click on PivotTable. Check what happens now, we get the name of our table. As our table expands with new data they will automatically be
included 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 on
Recommended PivotTables. These are actually quite smart, they might just give you
exactly what you're looking for, or they might help you get a faster start. So for example, here I
have the sum of sales USD by customer name, one of the reports I want to
create has sales USD in it. So I'm going to go with that, just select it and click on OK, you get your Pivot Table
inserted on a new sheet. This is the result of my table, it does all the aggregations for me, I didn't have to write a
single formula to do this. Now on the side here we
get the Pivot Table fields. But you can see I have them organized where
the fields are on one side, and the boxes that define
the 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 more
space for your fields here, you can go and select Fields
Section and Area Section, side by side. Now, what you see here are all
the headers of your dataset. That's why each column
needs to have a header. What Excel went ahead and did, when you inserted your Pivot Table, is that it put the sales
USD in the value section, and customer name in the row section. Now, if for some reason, you don't want to see
the sum of sales USD, but instead you'd rather
see 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 your
best friend in a Pivot Table. If I right mouse click, I can also select Value
Field Settings from here, I'm going to put this to Sum and go with OK. Now, another thing you can get
to 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 formats
the underlying cell, Number Formats stays with your
Pivot Table as it expands. So I'm going to go with Number, use 1000 Separator and
zero decimal places. Now, you can adjust and
update this Pivot Table as you see fit. So for example, if I wanted to look at
the different regions, I could decide to add
the 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 case
doesn't really make sense, because I just have two items, and you can also leave it on All. Now, if you decide that you
don't want to have Region in the filter, you can bring it to the column section. And now we have customer
name by region, here. We also automatically get the
grand totals on this side. Now, if you have another level, so let's say we're going to
bring 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, you
might decide to deactivate it for either to rows or the columns or for everything if you
don'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 do
the 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 headers
for 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 everything
and fills in the gaps. Now I'm just going to remove that. And now let's take a
look 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 put
everything 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 here
somewhere 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 anyone
to see sum of sales USD, let's say I just want
them to see sales USD. When I press Enter Excel doesn't like it if a header here is identical
to a field name here. So if you want it to be identical, what you need to do is either
add 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 update
the 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 Pivot
Table 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 have
Autofit column with an update, let's take away that checkmark and go with OK. Now, one thing I originally wanted was to get the sales
percentage 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 other
options that you have. If you get a chance, go ahead and try these because they can come in handy
when you're creating reports. Now, I'm going to call this Sales % Okay, so our first report is done. Before we test whether
this updates properly if we add new data to our table, let's go ahead and create
our 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 Table
formatting 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 Tables
are actually done. But I just want to show you
one thing before we wrap up. And that's how easy it
is 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 mouse
click 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 connect
it 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 to
our Pivot Table report and all we have to do is pick
any of these Pivot Tables, right mouse click and Refresh. Because they have the same pivot cache, they're all going to update
automatically together. We can see the new customer added here and our new product added in the middle, because we have our sales data
sorted in descending order. Okay, so this is how easy it is to create Pivot Tables in Excel. That was a quick
introduction to Pivot Tables, but with all the explaining, I was a bit slower than it
takes 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 the
benefits of Pivot Tables. Pivot Tables help you get
answers from your data, without you having to
write 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 visualize
these 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 see
you in the next video. (upbeat music)
