Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Google Drive & Apps course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

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 Tables Solution
Link Pivot Tables
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
Login to download
  • 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.

Gift this course