GoSkills
Help Sign up Share
Back to course

Pivot Tables

Quiz me Quiz Compact player layout Large player layout
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

Facebook Twitter LinkedIn WhatsApp Email

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.

Filtering Data
03m:13s
Functions and Formulas
04m:09s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement