One of the best things about Excel is the fact that a single worksheet can hold so much
information. In fact, you could fill billions of cells if you needed that much room.
A file with that much data could get pretty overwhelming, but there's a feature called
PivotTables that can help. PivotTables let you summarize and manipulate your data, without
actually changing anything in the worksheet. Let's take a look at how they work.
In this example, I have some company sales data, with columns for the salesperson, region,
account, order amount, and month. And I need to find the answer to a very specific question:
What is the amount sold by each salesperson?
Because each salesperson has data on more than one row, I'd normally have to add these
up in order to find the answer. But a PivotTable can do this for us automatically, which will
make the task much, much easier.
First you need to select your data. You can just click any cell if it's formatted as a
table; otherwise you need to select all the cells yourself (and don't forget your column
headers).
When you're ready, go to the Insert tab… then click the PivotTable command.
You'll usually want to place your PivotTable on a new worksheet, so make sure this option
is selected. Then click OK… and a new sheet will appear.
Over on the left is our blank PivotTable (it's ok that there's nothing there yet). To the
right is a list of fields that have actually been pulled from our column headers.
This is where we control what is or isn't displayed in the PivotTable. But before we
continue, let's take another look at that question:
What is the amount sold by each salesperson?
This question tells us exactly which fields to use to find out the answer: Salesperson
and Order Amount.
All you have to do is check the box next to the field… and it'll be added to one of
the areas below—in this case, the Rows category. Another method is to drag and drop the field
into place; we'll put this one under Values.
If we go back to the PivotTable… now we can see the information we were looking for;
in other words, the amount sold by each salesperson. So our question has been answered just by
choosing what fields to include.
One of the best things about PivotTables is how easy they are to modify, so you can view
and analyze different things. Let's say, for example, that you wanted to know each person's
monthly sales, not just their total sales.
Just drag the Month field… down here to Columns… and this will add a column for
each month in the worksheet.
If we wanted to know what each region sold, we could take out the Month and Salesperson
fields… and then drag Region… down to Rows.
So each time we change the fields, the PivotTable is just combining the source data in a different
way, so we can view the information we need. And we're doing all this without adding or
changing anything in the original worksheet.
You can even experiment by dragging the fields into different areas, but some combinations
work better than others. For instance, if I move Order Amount over to Columns or Rows…
you can see that it doesn't really give us any useful data. Since Order Amount contains
numerical values, it really does work best in the Values area.
Finally, if we click the arrow next to Order Amount… and go to Value Field Settings…
we can change the function to something other than SUM. Instead we could count the number
of cells; calculate the average; or use any of the other options here. Some of these functions
will be more useful than others depending on your data, so that's just something to
keep in mind.
In our next video, "Doing More with PivotTables," we're going to talk about Report Filters.
We'll also take a look at Slicers and PivotCharts, which can give you even more ways to work
with your PivotTable.