Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Building a Pivot Table
2:42 with Tyler TallonWhen setting up a pivot table, it’s always best to start with the end goal in mind. If you have the end goal in mind it will save you time and will guide your strategy on how you set up your pivot table.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
When setting up a pivot table, it's always
best to start with the end goal in mind.
0:00
Is there a specific question
you are trying to answer?
0:05
Are you just wanting to slice and dice
the data to explore any relationships?
0:07
Are you wanting to create a summary
report for a presentation?
0:12
If you have the end goal in mind,
it will save you time and
0:15
guide your strategy on how
you set up your pivot table.
0:18
All right, let's go back to our example
spreadsheet to start building our
0:21
pivot table.
0:25
Here, we have our example spreadsheet, and
0:26
we've confirmed the data are in
their appropriate format.
0:29
And again,
if you would like to follow along,
0:32
you can find the link to this
spreadsheet in the project files.
0:34
After we have confirmed the appropriate
format, the next thing we need
0:37
to do is create a table,
by clicking somewhere in the data range.
0:41
And then going to the Insert tab and
selecting Table.
0:45
If you've already clicked
somewhere inside the range,
0:49
then the range should set itself for
the table.
0:52
So just make sure that My Table's
headers are selected, then hit OK.
0:54
Creating a table like this is
not absolutely necessary, but
0:59
it is a best practice.
1:03
And if you later add a row to your data,
do refresh, which I'll show you later,
1:04
will add the new row to your
pivot table automatically.
1:09
That way, you don't have to respecify
your range each time a row or
1:12
a column gets added.
1:16
Now you can go up under Table Name and
name your table.
1:17
Let's name this one store sales.
1:21
The name has to start with a letter or
underscore and it can't include any spaces
1:24
or be the same as an existing
name already in the workbook.
1:29
Now on the Insert tab, select Pivot Table,
and for the table range,
1:34
it automatically populates with
our table name, StoreSales.
1:38
If for some reason it doesn't,
you can just type it in here.
1:42
Now, choose where you want
the pivot table to be placed,
1:45
in your worksheet or existing worksheet.
1:48
If you choose existing worksheet,
you'll need to choose which worksheet or
1:52
tab you want to place the pivot table in.
1:56
I usually choose new worksheet as this
keeps the work book a little cleaner in
1:59
my opinion.
2:02
So that's what we will do here.
2:04
Now you can see, the blank pivot
table on our new worksheet.
2:05
And if you go to the right,
2:09
you'll notice there are four areas
in which you can drag fields.
2:10
The first is Filters,
which accesses dropdown so
2:14
you can filter to wherever you want
including in the pivot table below.
2:17
The second is Columns, so
I'll add column headers here.
2:21
The next one is Rows, and we'll add row
headers here, subcategories will nest.
2:25
And the fourth one is sum Values, we'll
add any fields that should be summed here.
2:32
In the next video, we'll start adding
our fields and see it all come together.
2:38
You need to sign up for Treehouse in order to download course files.
Sign up