About this lesson
Introduction to Pivot Tables and how to effectively implement them.
Exercise files
The exercises below will open in a new tab. When signed in to your Google account, go to File --> Make a copy to get an editable copy of the file.
Pivot TablesLink Pivot Tables Solution
Link
Quick reference
Pivot Tables
Pivot tables are a useful tool to organize and analyze data.
When to use
When you have a large amount of data and want to look for relationships between different parts of the data.
Instructions
Create a pivot table
- Select the data you want to include
- Click “Data” - “Pivot table”
NOTE: Every column of data must have a header
Row and Column fields
- Add field - Choose data to add to a row or column
- Order - Sort the data in ascending or descending order
- Sort by - Sort the data by row, by column, or by the “Values” field
- Show totals - Display the totals for this row or column
Values field
- Add field - Choose data to fill the center of the table. Data will automatically be added to the appropriate cell for the current rows and columns
- Summarize by - Choose how the data will be summarized. Data can be counted, added, multiplied, averaged, and so on.
Filter field
- Add field - Choose data to filter from the table
- Show - Choose which data to display in the table
- 00:04 Both sorting and filtering your information within a spreadsheet
- 00:08 are an excellent way to find the information that you're looking for.
- 00:13 But perhaps one of the most powerful ways to view your data
- 00:16 is by applying a pivot table.
- 00:19 Let's take a look at how we can create a pivot table and
- 00:23 view our data in a few new and very powerful ways.
- 00:28 To do so, again, we want to start by selecting all of the data within our table
- 00:33 and it is very important that you make sure that you select the header
- 00:37 row as well when creating your pivot table.
- 00:40 Next, we're gonna select Data > Pivot table.
- 00:46 Now, you will notice that we are brought to a brand new sheet.
- 00:50 Whenever you create a pivot table,
- 00:52 you will be adding a new sheet as a part of your workbook.
- 00:57 Now it doesn't look like much yet, in fact, it's very blank at the moment.
- 01:01 So let's see how we can fill in this pivot table
- 01:04 by making selections here on the right.
- 01:07 The first three selections, rows, columns, and
- 01:11 values are most important in creating your pivot table.
- 01:15 The filtering options functions, very much like we saw in the last module.
- 01:20 So let's start by adding some information here.
- 01:23 I'd like to see all of my representatives listed here in the row section.
- 01:28 So I'm gonna select Add field > Rep. And now you see
- 01:32 the names of all of my reps, all of the names that we saw in the previous table.
- 01:38 And, of course, I have a few other options.
- 01:40 I can see here or make changes on, whether I want to see them in ascending or
- 01:44 descending order.
- 01:45 I'm gonna leave it in the default ascending order.
- 01:48 Next, I need to decide on which columns I would like to see
- 01:52 on the top portion of my pivot table.
- 01:54 I'm gonna select Add Field, and in this case I want to see the individual items.
- 02:01 And here you see those five items, Binder, Desk, Pen, Pen Set, and Pencil.
- 02:07 My last decision is to choose the value, what would I like to see
- 02:12 which intersects both my reps here and the items on the top?
- 02:17 I have all of the options available to me here,
- 02:21 but in this case I'd like to see the total cost here.
- 02:24 I'm gonna select Total, and now we can see the total cost that
- 02:29 the representative has sold as it pertains to those individual items.
- 02:35 And in the final column, titled Grand Total, we see those totals here.
- 02:41 So now, I've got a different view,
- 02:44 something that I could not see on my original table.
- 02:48 But now I can take a look and see at a glance as to who has sold the most,
- 02:54 and where did those sales come from.
- 02:57 Of course I can also view this value data in a few different ways as well.
- 03:03 Instead of seeing the overall sum, perhaps I'd like to just see a count.
- 03:08 By selecting the Summarize option here, I'm going to select Count.
- 03:13 And now I can see how many of those items have been sold.
- 03:18 For example, I can see that Jones has sold a total of 6 items,
- 03:23 whereas it looks like about half of my representative have sold only 1 item.
- 03:29 And I can reference the table as a whole to see which
- 03:32 of those items they have sold.
- 03:35 So try and experiment with creating a pivot table.
- 03:39 It may take some time to get familiar with which row data and
- 03:43 which column data you would like to see.
- 03:46 But pivot tables can be a very powerful way to not only filter your information,
- 03:52 but see that information in a whole new light.
Lesson notes are only available for subscribers.