Excel Pivot Tables in 15 Minutes!

Data Translates
219 followers
Did you know that data has now surpassed oil and gold as the most valuable commodity that we have
on earth? And with such a high value placed on data, employees who have really solid data analysis
skills are in high demand. And I want you to be one of those people. And what's one of the most
valuable tools that you can learn in Excel when it comes to data analysis? Well, it's pivot tables,
of course. So if you're a beginner, or you just want to pick up some tips and tricks,
then keep watching. We're going to smash out pivot tables in under 15 minutes.
So the data that we're going to be using in this example is just some basic sales data for two
stores, Computech and Microworld. It's a reasonably large data set. If I press control down arrow to
jump to the last row, you can see it's just over 21,500 rows of data. Control up arrow to jump to
the top. Now, the first thing we need to do here before we create our pivot table is put our data
into an Excel table. And you'll see why this is so important at the end of this video. So all
we need to do here is click in the data, press control T, and then we need to specify where the
data is for our table and if that table has headers. Well, I can see here that Excel has
picked up the correct data range. I can see my marching ants around the outside. And yes,
my table does have headers. Let's click on OK. And I now have my data in a table.
Notice that I also have a brand new contextual ribbon and that is called table design. And this
contains everything I'm going to need in order to manage and format this table. And these are
contextual ribbons in Microsoft, meaning that they only appear when you need them. So when I'm
clicked in my table, I can see this ribbon. If I click outside of the table, that ribbon disappears.
Don't forget, you can also change the table style. So if you don't particularly like this yellow
color or whatever color you have applied, I can click the dropdown and let's do something a bit
nicer. Let's go for this green color. And the final thing I'm going to do here is I'm going
to name my table. And again, this is so important, particularly if you have lots of different tables
in your workbook and you want to be able to identify that. So on the table design ribbon
in the properties group at the beginning, you can see Excel has given my table a very generic
and default name of table four. So it's not particularly meaningful. So let's change that.
I'm going to call this sales underscore data. Very important when you're naming things in Excel,
you can't have any spaces. So separate with an underscore or make it all one word.
Now that we have our data in a table, it's time to create our pivot table.
Now, if we're already clicked on that table design ribbon in the tools group,
we have a summarize with pivot table button. Alternatively, you could go to the insert ribbon
and in the tables group, we have a pivot tables button. Either of these is going to take you
to exactly the same window. The first thing we need to define here is the range that we want
to use to create our pivot table. Now, because I've got my data in an Excel table and I've named
it, you can see that Excel has picked up that this is the data I want to use. So that is correct.
No changes required there. I then need to specify where I want to place my pivot table report. Do I
want it on a new worksheet? Do I want to add it to an existing worksheet or do I want to add it to
the data model? Now, something I would recommend is that you always keep your pivot tables,
your pivot charts, any kind of analysis that you're doing separate from your source data.
So I'm going to put my pivot table reports on a new worksheet. Let's click on okay. You can see
I get a new worksheet at the bottom called sheet 4. And what do I currently have on my sheet? Well,
a whole lot of nothing. I have a blank pivot table report waiting for me to add fields into.
Couple of things to note here. Take a look at our ribbons. I now have an additional two
contextual ribbons, pivot table analyze and pivot table design. Once again, these will only be
available if I'm clicked inside my pivot table. If I click outside, those ribbons disappear.
Now, also when you click inside your pivot table report, you should see this little pane pop open
on the right hand side, showing you all of the pivot table fields. Now, if for some reason this
doesn't pop up and this can happen quite frequently, it might mean that you've accidentally closed this
pane down. If that does happen, you can easily get this back by going to the show group on the pivot
table analyze ribbon and just clicking field list to toggle that back on. So what is this pane all
about? What is it showing? Well, take a look at what we have in here. We have all of those column
headings from our source data. And then underneath, we have four different areas, filters, columns,
rows, and values. And the way that we build a pivot table report is simply by dragging and
dropping these different fields into any one of these four areas. And where you drag these fields
to really depends on the type of analysis that you want to do. So what are you interested in?
What are you trying to analyze? What metrics are important to you? What does your manager want to
know? The answer to all of these questions will determine which fields you place in which boxes.
So let's start out with something really simple. Maybe I want to see how many sales each of the
managers has generated. So for this, I'm going to need the manager field and also the sales field.
So I'm going to grab manager, drag and drop it into rows, check out my pivot table report.
I now have all of the managers listed in the rows. Currently, it's still not showing me too
much. So let's change that. Let's grab our sales field and drop it down into values. And very
quickly, I've managed to summarize the sum of the sales for each of the managers. And that in its
most basic form is a pivot table. So now that we have a very basic pivot table, it's time to start
adding in some more fields and pivoting them. So maybe I now want to see this report broken down,
not only by manager, but also by category. So I can grab product category. And let's drop this
into columns this time and see what happens to our report. Well, it adds the categories into
the columns. So I can now see the sum of sales by manager and by category. What if I don't like this
Well, I can grab category and let's drag it into rows above manager to pivot our report and look
at this data in a different layout. Now I'm summarizing by category first and then by all
of the managers. I can grab category field and drag it underneath manager to see that analysis again
in a different way. I can even grab category and drag it up to the filters area. So this time I
have a filter dropdown. So if I'm only interested maybe in seeing the sum of sales for certain
products, so maybe cameras, games, and laptops, I can just select those and my pivot table report
is going to update. So lots of different ways, numerous different options that you can view this
data. I could grab another field. Let's grab store, drop that into columns. And that data is now
summarized again in an entirely different way. Now I'm going to grab store. I'm just going to drag
that out so that we just have our managers and our categories in this dropdown. Because the next
thing I want to talk about is number formatting. Now, one thing you might notice here is that we
have the sum of sales, but there's no formatting applied. So we can't see a currency icon. We can't
see decimal places. So the way that we do this is we click in the data, right click our mouse,
and go down to value field settings. And from here, we want to select number format,
and then we can go in and apply the correct formatting. So I'm going to say I want accounting
formatting. I'm going to take those decimal places down to zero, and I'm happy to leave this on the
dollar symbol. Let's click on OK and OK again. And now that looks a little bit better. And the cool
thing about doing this this way is that if I was to pivot my fields, let's move manager to columns,
that formatting is always going to travel with the number no matter where I move that field to.
Currently, you can see that I'm summarizing my sales by the sum. So I'm seeing the totals for
each of the managers. But what if I want to do something a bit different? Maybe I want to see
the count of sales, like how many sales have they generated? Maybe I want to see the average.
Well, I can do that in pivot tables by changing the way that I'm summarizing my data.
So if we go all the way down to this values area, currently it says sum of sales. Now if I want to
change that, let's click the drop down, value field settings, and I can choose to summarize
my data in all of these different ways. So I could choose count, or I could choose average.
Let's click on OK, and you can now see the average of those sales in the pivot table instead.
Now, what if I want to see the averages and also the sum, and not one or the other? Well,
no problem. All we need to do is grab another sales field and drag it down into the values area.
So let's do that. Let's drag and drop. By default, it does a sum. So now I can see the sum
and the average. Just remember, when you do add another field in, you are going to need to
reapply that number formatting. So let's very quickly do that. Take those decimal places down
to zero, click on OK, and OK again. And I could carry on going, adding more sales fields,
and changing the way that I'm summarizing. I could choose count, I could choose min,
I could choose max. So this really helps you visualize your data in different ways.
Now in your pivot table report, you can also change these headings that you see just here.
So maybe instead of average of sales, I just wanted to say average sales or something
completely different. I can simply double click the heading, and I can then modify it in this
custom name area. So I'm going to delete out of, and click on OK. Let's do the same for this one,
and remove the of. And if I want to, I can change row labels. Let's make this manager,
and hit enter. So very simple to change those heading labels. Now one thing you'll notice is
that when you create a pivot table, you're going to see a grand total at the bottom.
And this can sometimes be super useful. It's added automatically for you.
Now I'm going to rearrange my pivot table very slightly just here. I'm going to drag category,
and I'm just going to drop it into rows. Because what you can also see are subtotals. And you're
going to see these at the top of each of the categories. So here I can see the subtotal for
cameras. Now the way that I generally tend to read data, I prefer my subtotals to be at the bottom.
So you can change the position of your subtotals, and your grand totals. And you can even turn both
of these off and on if you don't want to display them in the pivot table. So we do this by jumping
up to the design tab. In the layout group, we have subtotals. And I'm going to say show all
subtotals at the bottom of the group. I'm also going to turn off grand totals for rows and
columns just to remove those. Now it might be that once you've created your pivot table,
you want to display that data in a pivot chart to make it really visual and easy for people
to interpret. Now you have to be a bit mindful when you're creating charts, you don't want to
have too much data in your charts. So it's completely confusing. So I'm going to refine
this data a little bit and make it suitable for a basic column chart. So I'm going to remove
category and manager. And you can see how easy it is to remove fields. We can simply drag and drop
them. And let's start again. I'm going to have sales in values and we're going to use store this
time in rows. Let's apply our number formatting again by going into value field settings.
I'm going to take those decimal places down to zero. And now I'm going to create a little pivot
chart based off of this data. So once again, up to pivot table analyze in the tools group, we have
pivot chart. Now I'm going to keep this super simple. I am going to do a video about charts
and pivot charts in general a bit later on. So watch out for that one. But for this example,
we're just going to create a very basic clustered column chart. Now I'm going to do a little bit of
formatting here. I'm going to remove these gray labels. I don't really need them. So let's right
click, hide all field buttons on charts. I'm also going to remove this legend because it's sort of
sitting there, not really doing anything. So let's get rid of that. And I'm just going to give my
chart a quick title. We're going to say sales by store. Now, of course, there is a lot more
formatting we could do with this chart. But for the time being, for this example, we're going to
leave it there. Now, the final thing I want to show you here is how easy it is to update your
data because data in general doesn't remain static, particularly if this is sales data.
We're going to be adding to this data every single month. So currently my data set runs
from January to June. And if you take a look at the bottom, I also have another worksheet that
shows me the July sales data. So I want to incorporate this into my data set and I want
my pivot table and my pivot chart to update. And this is extremely simple. All I'm going to do here
is click in my data, Control A to select all. Control C to copy that data. We're then going to
jump back to our original store data. Control down arrow to jump to the last row. And then
we're going to do Control V to paste that data in. And I'm very quickly just going to delete out
this heading row. Now, because we put our data in a table, tables have auto expand capabilities.
So that means when you add in any new data, the table is going to expand. That data is going to
become part of that table. And then anything that you've created based off of that table data,
so pivot tables, pivot charts, will automatically include that data. However, there is one little
button that we need to click to get our pivot tables and our pivot charts to refresh. So let's
go back to our pivot table. Now you can click on either of these, either the pivot table or the
pivot chart, up to pivot table analyze. And you can see in the middle here, we have a refresh
all option. Now watch the chart very carefully because you should see it will change very
slightly and update with that July data. So let's click refresh all. And there we go.
