Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Intermediate Excel!
You have completed Intermediate Excel!
Preview
Finishing the Pivot Table
2:43 with Tyler TallonNow let's try building our pivot table by dragging fields into the four pivot table boxes.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Okay, now the fun part, we get to
start dragging fields into the boxes.
0:00
One important thing to
remember is that the order
0:05
in which fields are added in each box will
affect how it displays in the pivot table.
0:07
It's usually best to start with
the broadest category followed
0:12
by logical subsets.
0:15
Let's practice navigating
the pivot table here a bit.
0:17
Let's move State down to Rows,
we can just click on it, and then drag and
0:20
release once you are in
the box you want to go in.
0:24
Then let's place city in the rows
box also, right after state.
0:27
Now let's say we want to look
at sales dollars by quarter,
0:34
let's drag quarter to the columns box.
0:38
And then sales dollars
to the sum Values box.
0:43
Now I usually like to format the data
because I hate how messy it looks without
0:50
formatting.
0:54
Let's highlight the data,
right-click > Format Cells, And
0:56
then select number with comments and
no decimals.
1:01
There, that looks better.
1:08
Okay, now let's say we want to
see the sales by month also.
1:12
Drag month right after
quarter in the Columns box.
1:18
Now let center the months column headers.
1:21
Not mandatory but
I think it looks better centered.
1:24
Now we are getting somewhere.
1:30
Here we can see sales by month for
each city and also the state totals.
1:32
We also have quarterly totals and
grand totals for
1:37
the first two quarters of the year.
1:40
Now remember we have two products in
our data, product a and product b.
1:42
And since we haven't selected product yet
for any of the boxes,
1:47
it will default to show
the total of both products.
1:50
Let's try dragging the product
down to the filter box.
1:53
Remember, the filter works just how it
sounds, it let you filter, in case,
1:57
between products.
2:02
Right now, it shows all.
2:04
But if I go up and select product A,
2:06
you will see the data
change in the pivot table.
2:07
And now, we are just looking at sales for
just product day.
2:11
Now, let's choose product B.
2:14
Let's say we want to see units
sold instead of sales dollars.
2:17
The way we will do that, select
Units Sold, and move to sum Values box.
2:20
And we can either keep it like this where
we can see both sales and units sold.
2:25
Or remove sales dollars by the dragon or
unchecking the sales dollars.
2:30
All right, that covers some of the basic
functionality for a pivot table.
2:35
In the next video,
we'll discuss a few more tips and tricks.
2:40
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up