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
Other Pivot Table Tips and Tricks
2:52 with Tyler TallonNow that we’ve learned the basics for building and navigating a pivot table let’s look at a few more tips and tricks.
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
Now that we've learn the basics for
building and navigating a pivot table.
0:00
Let's look at a few more tips and tricks.
0:03
Earlier we had mentioned that it was a
best practice to set up your data as table
0:06
before you create the pivot table,
in case you need that add a row or column.
0:10
Let's try that.
0:14
Say we open a new store in June and
need to add that to the data.
0:15
We can insert a line here
right below Houston Texas and
0:19
fill in cells for
a new store in Abilene Texas.
0:23
And let's say we know that we
sold 1,000 units for $4,000.
0:30
Now if we go back over
to a pivot table and
0:39
right-click somewhere inside the pivot and
0:42
hit refresh, you will see
the Abilene store shows up now.
0:46
Another cool thing about pivot tables
is that you can sort in ascending or
0:51
descending order.
0:55
So if you want to see the states
with the highest unit cells,
0:57
you can click on the Total and
then go to the Data tab, Sort, and
1:00
click Largest to Smallest, or descending.
1:05
You'll see under the example that
California has sold the most units.
1:08
Then you can also click on a Total for
one of the cities, and Sort descending.
1:11
And all the cities will now be sorted for
each state.
1:16
We discussed previously how
to use the filter field
1:19
to filter between what you want to see.
1:22
You can also do that wherever there
a drop down on the pivot table.
1:25
For instance, you can select
the Row Labels drop down and
1:29
choose a few states if you
are wanting to narrow down your list.
1:33
Now let say, you don't want to see
the sum of sales or unit sold but
1:39
prefer to see the average.
1:43
Under the Analyze tab,
select Field Settings.
1:45
You'll notice there is
several options here,
1:51
Sum, Count Average, Max, Min, and Product.
1:55
Let's select Average.
2:00
Another cool thing is the different
layouts in the design tab.
2:06
If you click on Design,
you can see all the different options.
2:09
Let's select a few and see how they
change the look of the pivot table.
2:13
I wish we had the time to cover all
the functionality of the pivot table, but
2:23
there are a few we'll have to leave out.
2:27
But I hope you explore some on your own,
and
2:29
maybe check out the slicer
function found in the Analyze tab.
2:31
Or try adding a calculated field if
you want to know the cost per unit for
2:35
each store.
2:39
Or play around with conditional
formatting, or generate a pivot chart, or
2:40
several other advanced features.
2:45
I've got a few of my favorites
linked below in the teachers notes.
2:47
As you can see, the pivot table
can be a very useful tool and
2:50
I hope you have fun exploring.
2:54
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