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 like this.

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.

 Live examples in Sheets

Go to this spreadsheet for examples of pivot table groupings that you can study and use anywhere you would like.

One thought on “Google Sheets – Group Data Inside a Pivot Table

  • Hello, I am trying to sift through large amounts of data where the description of the items could have one to multiples of 30 with different amounts from credit card charges for example. Is there an automated approach to group together all items with a similar name other then highlighting > rt click > create pivot group? I have thousands of items at a time and creating groups within the groups could be prone to error and take a long time. Hope this makes sense and thanks for the lessons.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>