WEBVTT
Kind: captions
Language: en

00:00:11.420 --> 00:00:15.720
One of the best things about Excel is the
fact that a single worksheet can hold so much

00:00:15.720 --> 00:00:16.930
information.

00:00:16.930 --> 00:00:21.040
In fact, you could fill billions of cells
if you needed that much room.

00:00:21.040 --> 00:00:24.900
A file with that much data could get pretty
overwhelming, but there's a feature called

00:00:24.900 --> 00:00:27.720
PivotTables that can help.

00:00:27.720 --> 00:00:31.529
PivotTables let you summarize and manipulate
your data, without actually changing anything

00:00:31.529 --> 00:00:33.280
in the worksheet.

00:00:33.280 --> 00:00:35.800
Let's take a look at how they work.

00:00:35.800 --> 00:00:42.530
In this example, I have some company sales
data, with columns for the salesperson, region,

00:00:42.530 --> 00:00:46.600
account, order amount, and month.

00:00:46.600 --> 00:00:50.190
And I need to find the answer to a very specific
question:

00:00:50.190 --> 00:00:55.960
What is the amount sold by each salesperson?

00:00:55.960 --> 00:01:00.159
Because each salesperson has data on more
than one row, I'd normally have to add these

00:01:00.159 --> 00:01:02.530
up in order to find the answer.

00:01:02.530 --> 00:01:09.150
But a PivotTable can do this for us automatically,
which will make the task much, much easier.

00:01:09.150 --> 00:01:11.380
First you need to select your data.

00:01:11.380 --> 00:01:16.149
You can just click any cell if it's formatted
as a table; otherwise you need to select all

00:01:16.149 --> 00:01:20.000
the cells yourself (and don't forget your
column headers).

00:01:20.000 --> 00:01:27.210
When you're ready, go to the Insert tab…
then click the PivotTable command.

00:01:27.210 --> 00:01:31.140
You'll usually want to place your PivotTable
on a new worksheet, so make sure this option

00:01:31.140 --> 00:01:33.119
is selected.

00:01:33.119 --> 00:01:38.490
Then click OK… and a new sheet will appear.

00:01:38.490 --> 00:01:42.969
Over on the left is our blank PivotTable (it's
ok that there's nothing there yet).

00:01:42.969 --> 00:01:47.810
To the right is a list of fields that have
actually been pulled from our column headers.

00:01:47.810 --> 00:01:52.079
This is where we control what is or isn't
displayed in the PivotTable.

00:01:52.079 --> 00:01:56.640
But before we continue, let's take another
look at that question:

00:01:56.640 --> 00:02:00.690
What is the amount sold by each salesperson?

00:02:00.690 --> 00:02:08.530
This question tells us exactly which fields
to use to find out the answer: Salesperson

00:02:08.530 --> 00:02:11.450
and Order Amount.

00:02:11.450 --> 00:02:15.880
All you have to do is check the box next to
the field… and it'll be added to one of

00:02:15.880 --> 00:02:20.480
the areas below—in this case, the Rows category.

00:02:20.480 --> 00:02:28.160
Another method is to drag and drop the field
into place; we'll put this one under Values.

00:02:28.160 --> 00:02:33.129
If we go back to the PivotTable… now we
can see the information we were looking for;

00:02:33.129 --> 00:02:36.480
in other words, the amount sold by each salesperson.

00:02:36.480 --> 00:02:41.430
So our question has been answered just by
choosing what fields to include.

00:02:41.430 --> 00:02:45.989
One of the best things about PivotTables is
how easy they are to modify, so you can view

00:02:45.989 --> 00:02:48.590
and analyze different things.

00:02:48.590 --> 00:02:52.209
Let's say, for example, that you wanted to
know each person's monthly sales, not just

00:02:52.209 --> 00:02:54.430
their total sales.

00:02:54.430 --> 00:03:00.620
Just drag the Month field… down here to
Columns… and this will add a column for

00:03:00.620 --> 00:03:03.560
each month in the worksheet.

00:03:03.560 --> 00:03:07.959
If we wanted to know what each region sold,
we could take out the Month and Salesperson

00:03:07.959 --> 00:03:17.170
fields… and then drag Region… down to
Rows.

00:03:17.170 --> 00:03:21.790
So each time we change the fields, the PivotTable
is just combining the source data in a different

00:03:21.790 --> 00:03:24.790
way, so we can view the information we need.

00:03:24.790 --> 00:03:30.090
And we're doing all this without adding or
changing anything in the original worksheet.

00:03:30.090 --> 00:03:34.650
You can even experiment by dragging the fields
into different areas, but some combinations

00:03:34.650 --> 00:03:36.519
work better than others.

00:03:36.519 --> 00:03:41.660
For instance, if I move Order Amount over
to Columns or Rows… you can see that it

00:03:41.660 --> 00:03:44.900
doesn't really give us any useful data.

00:03:44.900 --> 00:03:51.310
Since Order Amount contains numerical values,
it really does work best in the Values area.

00:03:51.310 --> 00:03:59.099
Finally, if we click the arrow next to Order
Amount… and go to Value Field Settings…

00:03:59.099 --> 00:04:02.900
we can change the function to something other
than SUM.

00:04:02.900 --> 00:04:07.849
Instead we could count the number of cells;
calculate the average; or use any of the other

00:04:07.849 --> 00:04:09.939
options here.

00:04:09.939 --> 00:04:13.709
Some of these functions will be more useful
than others depending on your data, so that's

00:04:13.709 --> 00:04:18.430
just something to keep in mind.

00:04:18.430 --> 00:04:22.400
In Part 2 of this video, we're going to talk
about Report Filters.

00:04:22.400 --> 00:04:26.830
We'll also take a look at Slicers and PivotCharts,
which can give you even more ways to work

00:04:26.830 --> 00:04:27.830
with your PivotTable.