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
In this video, we'll introduce you to pivot tables, a powerful tool used to summarize data.
Google Sheets Changes
- To add a Pivot Table:
Insert -> Pivot Table
"Select All" keyboard shortcut
- Ctrl-A (PC)
- Cmd-A (Mac)
Pivot Table
- Summarizes data from a larger table or database using sums, averages, or other useful statistics.
- Rearranges (or "pivots") data to bring attention to important information.
- Consists of row and column headings, along with data values and filters.
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
Welcome, in this video we'll
introduce you to pivot tables,
0:00
a powerful tool used to summarize data.
0:04
Let's set the stage.
0:07
You are the product manager for
0:09
a retail chain with nearly 50
stores across 14 US states.
0:11
We have six months of sales data for
two products in particular,
0:14
boxing gloves and hand wraps.
0:18
In short, we have a lot of data.
0:21
Can we organize it more efficiently?
0:24
For example, it would be helpful to
have totals by state or by quarter.
0:26
The answer, of course, we can use
a pivot table to summarize our data.
0:31
What is a pivot table?
0:37
A pivot table summarizes data from
a larger table or database using sums,
0:39
averages, or other useful statistics.
0:43
A pivot table rearranges, or pivots, data to
bring attention to important information.
0:47
A pivot table consists of row and column
headings, along with data values and
0:53
filters.
0:58
For this exercise, select the Sales Data
tab at the bottom of the workbook.
1:00
I want all the data on this worksheet so
I can select columns B to J.
1:05
But there are a few empty rows
at the bottom of this worksheet,
1:10
which creates a minor issue
when creating my pivot chart.
1:13
So let's use a keyboard shortcut to
select only the cells that contain data.
1:17
Click the cell within the data,
doesn't really matter where.
1:22
The Select All keyboard shortcut is
Ctrl A on a PC or Command A on a Mac.
1:27
Now all the data is selected, but none
of the blank rows or columns around it.
1:34
Click the data menu and
choose pivot table.
1:40
The data range should read
'Sales Data'!B1:J589 and
1:45
should be set to insert to new sheet.
1:51
If not, repeat the previous steps or
make these changes manually.
1:56
Once this is correct, click Create.
2:02
A new worksheet will be created.
2:05
Let's rename this worksheet.
2:08
Click the small arrow on the tab and
choose Rename.
2:10
I'll rename this worksheet
Sales Pivot Table.
2:14
This worksheet is
the shell of a pivot table.
2:22
And the editor on the right allows to
add row headings, column headings, and
2:25
data values to our pivot table.
2:30
We can also filter on
specific data if we choose.
2:33
Next to Rows, click Add,
then choose State.
2:36
Next to Columns, click Add,
then choose Quarter.
2:45
Next to Values, choose Add,
then choose Units Sold.
2:55
What does this show us?
3:00
Let's pick a state, AZ.
3:02
In Arizona, we sold,
5,900 units in quarter one.
3:05
That's combined boxing gloves and hand
wraps in January, February, and March.
3:10
We sold 5,200 units in quarter two.
3:17
In total, we sold over 11,000
units in the state of Arizona.
3:21
Let's drill down further.
3:27
Next to rows, click Add,
then choose City.
3:29
Next to columns, click Add,
then choose Month.
3:36
Now, we get a detailed breakdown of
the units sold in each month, for
3:43
each city, within each state.
3:48
Take a moment to process the details,
pause the video if you need to.
3:51
Ready to move on?
3:56
Great.
3:58
What if we want to also
display sales in dollars?
3:59
Next values, click Add,
then choose Total Sales.
4:03
It's a bit cluttered now, so
4:10
this is where I would make some decisions
on what I would convey in my pivot table.
4:11
This table doesn't need sales and
unit sold.
4:15
From the value section,
4:19
click the X next to the Units Sold
to remove it from our pivot table.
4:21
It's easy to add it back
if we change our minds, or
4:28
we can always create another
pivot table with units sold.
4:31
I prefer to display only one
set of values per pivot table.
4:35
Maybe I don't need monthly sales totals.
4:39
Remove month from the column section.
4:42
This is a clean, concise pivot table.
4:48
One last thing, let's add a filter.
4:52
Next to filter, click Add,
then choose region.
4:55
The current status is showing all items.
5:06
Click the drop down arrow and there are
some checkmarks next to all four regions.
5:10
Let's display only East and South.
5:17
Click on Midwest to remove the check.
5:20
Then click on West to remove that check.
5:25
Click OK.
5:29
So this final pivot
table displays the total
5:32
sales by city per quarter in our
eastern and southern stores.
5:36
Pretty cool?
5:42
There are so many options.
5:43
For instance, we can list our
states in descending order.
5:45
We can display and remove totals.
5:52
Uncheck show totals from quarter and
the grand total column disappears.
5:56
Check out the different
summary options for values.
6:02
We can display average
sales instead of sum,
6:05
the data changes along with
my heading in cell A1.
6:10
Three quick changes,
a completely new pivot table.
6:16
Feel free to make a pivot
table of your own.
6:21
Try different combinations of rows,
columns, values, and filters.
6:23
So much power in so few mouse clicks,
have fun experimenting.
6:28
In the next video, we will use data
validation to fix and prevent data errors.
6:35
Stay tuned.
6: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