Table of data before being used in a Pivot Table

Google Sheets Pivot Tables – Basic Tutorial

This tutorial will walk you through the steps of creating your first Pivot Table. The linked file used in these steps can be found in this Pivot Table Course with the source data and the Pivot Table shown in the pictures. The course contains an additional twelve videos with linked files, notes, and quizes all about Pivot Tables.

Start with Good Data

Table of data before being used in a Pivot Table
Table of data before being used in Pivot Table

The table of data in the image above is a good example of what makes good data for a Pivot Table. It has headers and the names of the headers describe the data that’s underneath it. The Sales Rep header is on a column that contains the names of the sales reps (duh). More importantly, there are no breaks in this data meaning that there are no blank lines. Also, the table is so large that you can’t just look at it and get the information. If the table of data was small, there could be no need for a Pivot Table since you could see all of your answers by eyeballing the data yourself.

Video Explanation

Create Your Pivot Table

Go to Data then Pivot table
Go to Data then Pivot table

Google Sheets – Learn the Basics

Basics Course Thumbnail

Learn all of the foundational skills necessary to create and use spreadsheets.

  • Create and style your sheet
  • Use cell references
  • Create formulas
  • Sort and filter
  • Share and protect
  • Analyze with pivot tables

10 downloadable videos, LINKED GOOGLE SHEETS for you to copy and use, quizzes, and built-in notes. Lifetime access to the course.

Learn More

Make sure that you have selected a cell within the source table of data then go to the menu, select Data, and Pivot table as shown in the image above. That gives you a blank slate that you’re going to work with. Sheets will “suggest” different Pivot Table configurations using artificial intelligence to as shown in the image below. Unless you have very simple data, and you just want to have it summarized by one dimension, these are not going to guess what you want because there are so many different combinations. But, if you do just try one of these and click on it, it’s going to build a table for you which can be helpful if you have simple needs.

Suggested Pivot Table Configurations
Suggested Pivot Table Configurations

Add Data to the Pivot Table

Now we will start building out our Pivot Table. If we want to analyze the data by Sales Rep first, and get the Sales Reps names going down the left-hand column, this is where it gets a little bit confusing and it may be more clear to watch in the video. You want the Sales Reps names in the leftmost column, but you want the name of each salesperson to be in a row. You need to add the Sales Rep for Rows even though these rows will be filling the first column. Sheets will fill them into the Pivot table in alphabetical order.

For the columns, you want the Ship Mode, and again, this is a little bit confusing. It’s going to be your row of headers, but each column is going to have the data in it so it’s called Columns. Let’s add the Ship Mode.

Add Sales Rep as a Row
Add Sales Rep as a Row
Add Ship Mode as a Column
Add Ship Mode as a Column

You’ll notice, each time you add a field, it’s asking if you want to show the totals or not. Let’s leave both of those checked and there will be a Grand Total for the Sales Rep and a Grand Total for the Ship Mode.

Leave on the Show totals option
Leave on the Show totals option

The Values field is going to be what it shows you in the middle of your pivot table. For this table, we will be looking at the number of sales, not the dollar amount. Go to values and add our Sales column. It’s the field with the dollar amounts in it. Sheets defaults to summing dollar amounts. We want to count each one of them as one so we are going change the function from SUM to COUNT.

Add the COUNT of Sales
Add the COUNT of Sales
Basic Pivot Table
Basic Pivot Table

Add the Dates

Now that we have Sales Rep and we have a count by each Ship Mode, the last thing we want to do is look at the data by year. Let’s have the dates on the left-hand side to the right of Sales Reps. Remember this is called rows, even though it’s a column. Let’s add another column of rows, and we are going to make it the date.

Add Date as Another Row
Add Date as Another Row
Pivot Table with Too Much Detail
Pivot Table with Too Much Detail

Group the Dates

This isn’t what you want, right? We want it by year but, in order to do that, you had to add the dates. What you can do is right click on any of the dates, and create a pivot data group, and select Year. More detailed information on how to summarize dates in a pivot table can be found here.

Pivot Table Grouping by Year
Pivot Table Grouping by Year

This is going to summarize the data by year because Google Sheets recognizes the data as valid dates. It can extract the Year and summarize by just that.

Pivot Table Grouping by Year
Pivot Table Grouping by Year

Filter by Date

Let’s say you’re just looking for 2018. Let’s go back to the right, scroll down to the filters. We’re going to add a filter for the date. This will be a little bit tricky how we’re gonna do this. The drop down says it’s showing all items. Select clear, type in 2018, and choose select all.

Pivot Table Date Filter
Pivot Table Date Filter
Pivot Table Just 2018
Pivot Table Just 2018

What we’re doing here is, we’re saying unselect everything, show no dates. Then, if you type in 2018, it’ll show only the 2018 dates that are in the original table. If you click select all, it’s going to select only the 2018 dates. Click OK. You have this filtered by 2018. Click OK, and there you are.

Completed Pivot Table

A really easy to understand pivot table with just the data that you need and if you want to change anything, this is always live. I could just go back here and customize it however you want. So that’s all. Taking a good solid list of data that has columns with consistent data types in it, no blanks, and we’ve created this pivot table that gave you the exact information that you wanted to see.

More training is available on how to prepare data for a Pivot Table as part of a Pivot Table Course designed with beginners in mind.

Finished Pivot Table
Finished Pivot Table
Table of data before being used in a pivot table

Google Sheets – Group Data Inside a Pivot Table

Pivot Table Groups

If you’re using a Pivot Table in Google Sheets, and you want to create groups within that pivot table, you can do it with just a few clicks.

This tutorial starts with a table of sales transactions and walks you through the steps to group the transactions by region.

This walk-through assumes that you have already completed your Pivot Table and you have a basic knowledge of how to use it. See this video if you need some help on Pivot Tables or take a deep-dive in our comprehensive training course which includes full access to all of the spreadsheet files used including source data and Pivot Tables.

If you need a primer on Pivot Tables, this video will walk you through them.

Raw Data

When you look at the table below, we can see we have different regions. We have West, East, North, and, um, just one mile left of North.

Table of data before being used in a pivot table
Table of data before being used in pivot table

Build Your Pivot Table

Let’s make the rows of our Pivot Table the value in the Region column from the table of raw data. Select any cell in the table of data and go to Data and Pivot table. This will start to fill your Pivot Table. Click ADD for the rows and select “Region”.

Selecting Region as the row
Selecting Region as the row

Basics Course Thumbnail

Learn all of the foundational skills necessary to create and use spreadsheets.

  • Create and style your sheet
  • Use cell references
  • Create formulas
  • Sort and filter
  • Share and protect
  • Analyze with pivot tables

10 downloadable videos, LINKED GOOGLE SHEETS for you to copy and use, quizzes, and built-in notes. Lifetime access to the course.

Learn More

To fill in the center of the Pivot Table with data, select ADD for the Values and choose SUM which is the default. This will show the sum of the sales by Region.

Add Amount as a value and SUM it
Add Amount as a value and SUM it

Let’s add another value here to make it look a little bit more informative. We also care about the item, right? Okay, add that as a column. This will give your Pivot Table a broader display of data.

Add Item as a Column
Add Item as a column

Video Description

Grouping the Data

Now let’s group together the compass directions and then group the One Mile Left of North in another group because he’s a little bit different so we want to analyze him differently. What you want to do highlight the three that you want to group separately, right click, and create a Pivot group as shown in the image below.

Add the Pivot Table Groups
Add the Pivot Table Groups

Now the Pivot Table has put the three compass direction territories together and then the one oddball on its own. Now you can expand and collapse these groups in the same way that you can in a spreadsheet without a Pivot Table. That’s the way to group data in a Google Sheets pivot table. That’s basically all.