Tip:
Highlight text to annotate it
X
One of the best things about Excel is that it lets
you deal with really large amounts of data.
In fact, if you needed to, you could have billions
of cells in a single spreadsheet.
But no matter how much data you have, it can still
be surprisingly easy to manage,
using a very special feature called PivotTables.
PivotTables let you summarize and manipulate the
data, without changing the original spreadsheet.
If you've never used PivotTables before, they might seem a little intimidating at first,
but once you get the hang of it, you'll be able to easily make PivotTables
using just a few mouse clicks.
In this example, I have some company sales data,
and there are columns for Salesperson, Region, Account, Order Amount, and Month.
And I need to answer the question, "What is the
amount sold by each salesperson?".
Now because each salesperson appears on multiple rows,
I would need to add up the rows to find the answer.
But a PivotTable can do this automatically.
You'll first need to select the data.
If it's formatted as a table, you can just click
any cell,
and if it's not, you'll need to make sure you
select everything, including the column headers.
And then from the Insert tab, click the PivotTable command.
And you'll usually want to place the PivotTable in
a New Worksheet.
And click OK.
Over on the left is our blank PivotTable.
And on the right is the Field List.
All of these field names are just taken from our
column headers.
Below that are the four areas where we can add
fields to build the PivotTable.
Now before we select any of the fields, let's look
at the original question again:
"What is the amount sold by each salesperson?"
This question tells us exactly which fields we need to use to answer it:
Salesperson and Order Amount.
When you click these, they will be added to the
Row Labels and Values areas below.
And now on the left we have a PivotTable where we
can see the amount sold by each salesperson.
So now our question has been answered,
and if we want we can modify the PivotTable to answer more questions.
If we want to know how much each person sold each
month,
we can drag the Month field into Column Labels, and this will add columns for each
month.
Or, if we wanted to know how much each region sold,
we could just take the Month and Salesperson fields out, and then drag Region into Row
Labels.
So each time we change the fields, the PivotTable is just combining and adding the source data
in a different way to give us the answer that we need.
You can experiment by dragging the fields into
different areas,
but you may find that some combinations don't work as well.
For example, if I move Order Amount to Column Labels or Row Labels,
it doesn't really give me any useful data.
Since Order Amount contains numerical values, it
really works best in the Values field.
Finally, if you click on the Order Amount field
and go to Value Field Settings,
you can change the function, so that instead of calculating the Sum,
it can Count the cells, or find the Average.
And there are many other functions that you can
use.
In Part 2, we're going to talk about Report Filters,
and we'll also talk about how to add slicers and PivotCharts,
which will give you even more ways to manipulate the PivotTable.