Google Sheets Pivot Table: A Complete How-To Guide

Raw data looks manageable until you try to answer a simple question: 

• Total sales by region
• Average order value by product
• Count by month

Suddenly, the sheet is doing that thing where it has all the information and none of the answers.

That’s where pivot tables help. 

In Google Sheets, they give you a quick way to group, calculate, and reorganize data without writing formulas first. 

In this guide, I’ll walk you through how to create a pivot table in Google Sheets, use the editor, and adjust it based on what you need.

How to Create a Pivot Table in Google Sheets

To create a pivot table in Google Sheets, follow these steps:

Step 1: Select your data

Select the full dataset you want to summarize. Click any cell inside your table, then drag to highlight the complete range.

Before selecting, check the structure of your data:

• Each column has a header in the first row
• No blank rows in the middle
• No empty columns inside the dataset
• Each row represents one record
• Each column contains one type of value

If your dataset includes totals, exclude them because pivot tables should work from raw data.

Select your data. Image by Author.

Step 2: Insert Pivot Table

Once your data is selected, go to the ribbon and click Insert > Pivot table. A Create Pivot Table dialog box will appear, asking where you want the pivot table to appear.

Step 3: Choose a location

You will see two options:

• New sheet: creates the pivot table in a separate tab
• Existing sheet: places it in the current sheet

Select one and click Create. After this, Google Sheets opens a blank pivot table and the editor on the right.

Create a pivot table in Google Sheets to a new sheet. Image by Author.

Step 4: Use the Pivot Table editor

Once you click create, the pivot table editor will appear on the right side of the screen in the new sheet. It has four sections:

• Rows: adds categories vertically
• Columns: adds categories across the top
• Values: calculates totals, counts, or averages
• Filters: limits the data shown

Each section controls how your data is grouped and summarized.

Pivot table editor panel. Image by Author.

Understanding the Pivot Table Editor

The pivot table editor controls how your data is grouped and calculated. You don’t need to memorize the editor. You just need to know what each part controls.

Rows

Rows group your data vertically. When you add a field, the table creates one row for each unique value in that column.

For example, if you add Region to Rows, the pivot table will create one row for each region, such as East, West, and South.

If you want more detail, you can add another field under it.

• Add Product below Region

Now each region expands into products. You will see something like:

• East → Notebook, Pencil
• West → Pen

Columns

Columns group data across the top. They work the same way as Rows, but horizontally.

For example, if you add Product to Columns, each product becomes a column header.

This helps when you want to compare categories side by side.

If rows already have Region, adding Product to columns shows how each product performs within each region.

Tip: Keep this simple. Too many columns make the table harder to read.

Values

Values define what gets calculated. When you add a numeric field, Google Sheets applies a calculation. It usually defaults to SUM(). One could also do COUNT() or AVERAGE().

For example, if you add Revenue to Values, you will get a SUM() of Revenue.

If your pivot table is grouped by Region, then adding Revenue to Values will show total revenue for each region.

Tip: If your numbers look wrong, check the source data first. A column stored as text will not sum correctly.

Filters

Filters control which data is included in the pivot table. They do not change your original dataset; they only change what is visible in the summary.

For example, if you add Region to Filters, you can choose to show only East or West data without changing the source table itself.

What to check if the pivot table looks wrong

If the result does not look right, the problem is often in the source data, not in the pivot table itself.

Check these:

• Numbers stored as text
• Missing or unclear headers
• Blank rows inside the dataset
• Wrong data range selected
• Field placed in the wrong section

That last one happens a lot. Putting a field in Rows instead of Values changes the whole layout. To fix this, remove it and add it to the correct section.

How to Summarize Data in a Pivot Table

To summarize data in a pivot table, add a numeric field to Values and choose how you want it calculated. 

You will mainly use these three:

• SUM() for totals

• COUNT() for number of entries

• AVERAGE() for typical values

Each one gives a different view of the same dataset.

Here’s an example to understand this better.

Let's say I want to calculate how much sales each region generates. For this, set up the pivot table like this:

• Add Region to Rows

• Add Total Sales to Values

In Summarize by field, Google Sheets will apply the SUM() of Total Sales, so you’ll see total sales for each region.

Summarize the data using a Pivot table. Image by Author.

You can change the calculation from the Summarize by dropdown:

• Switch to AVERAGE() to see average sales per transaction

• Switch to COUNT() to see how many sales entries each region has

If your numbers look wrong

If the numbers look off, check a few things:

• Total Sales is stored as numbers
• The correct field is in Values
• The calculation is set correctly

Most issues come from one of these.

How to Group Data in Google Sheets Pivot Tables

Grouping reduces detail, so the table is easier to read. You can group data in three ways: 

• by category
• by date
• by number ranges

Grouping by categories

Use category fields like Region, State, or Product Category when you want to combine similar items into one group. 

For example, if you want to see how much each product category contributes to total sales:

• Add Product Category to Rows

• Add Total Sales to Values

The pivot table will show one total per category, such as Electronics, Furniture, and Office Supplies.

If you need more detail, you can layer another field under it.

• Add Product Name below Product Category

Now each category expands into individual products, so you can see what drives the totals within each group.

Group data by category using a Pivot table. Image by Author.

Grouping by date

Dates often create long, hard-to-read lists. Grouping turns them into time periods.

For example, to analyze sales over time:

• Add Date to Rows

• Right-click any date in the pivot table

• Select Create pivot data group and choose Month or Year

Instead of seeing every single date, you’ll see grouped values like Jan 2025 or 2026.

If grouping doesn’t work, check the data type because dates stored as text won’t group.

Group data by dates using a pivot table. Image by Author.

Grouping by numeric ranges

Use numeric grouping when you don’t need exact values and want to see the distribution instead. 

For example, to analyze how orders are spread by quantity:

• Add Units Sold to Rows

• Right-click a value

• Select Create pivot group rule

Set your range, for example:

• Minimum: 0
• Maximum: 30
• Interval: 10

The pivot table will group values into ranges like 1–10, 11–20, and 21–30.

Group data by numeric range using a pivot table. Image by Author.

When grouping helps

Use grouping when:

• The table has too many rows
• Values are too detailed to scan
• You’re trying to spot patterns

How to Filter Pivot Table Data

If your pivot table shows too much data, use filters to narrow it down. With filters, you can focus on one region, one product, or any segment without changing the original dataset.

Assume your pivot table is set up like this:

• Product Category in Rows

• Total Sales in Values

Now you want to see category-wise sales for a specific region. To add this filter:

• Click anywhere in the pivot table

• Open the Pivot table editor (right side panel)

• In Filters, click Add

• Select Region

Once added, a filter dropdown appears:

• Open the Region dropdown

• Uncheck all

• Select West

The pivot table updates to show only West. All totals and calculations adjust based on that selection.

You can switch the selection anytime:

• Select East to see East sales
• Select South to view South

No need to rebuild the table. Just change the filter.

Filter the data using a pivot table. Image by Author.

When to use filters

Use filters when:

• You want to focus on one segment
• You’re comparing values one group at a time
• The table includes more data than you need to see at once

How to Customize Pivot Tables

Once the table works, clean it up so people can actually read it. Let’s see how to do it:

Sort values

Sorting helps you see the top or bottom performers quickly. For example, to find which category or region has the highest sales:

• Click anywhere in the pivot table

• Open the Pivot table editor

• Under Rows (such as Product Category or Region)

• Use Sort by and select SUM of Total Sales

• Set order to Descending

The highest value moves to the top. Switch to ascending if you want the lowest first.

Sort the data in the pivot table. Image by Author.

Change aggregation type

You can change how values are calculated at any time. For example, to switch from total sales to average sales:

• Go to Values

• Click Total Sales

• In Summarize by, select AVERAGE()

The table now shows average values instead of totals. Other options like COUNT() are useful when you want frequency instead of totals.

Use other aggregation types to calculate in the pivot table. Image by Author.

Format numbers

Formatting makes the table easier to read. For example, to display sales as currency:

• Select any value in the pivot table
• Go to Format > Number > Currency

Values now appear in a readable format.

You can also use percentages or add decimals depending on what you’re showing. This only changes how values appear, not the data itself.

Format the numbers in the Pivot table. Image by Author.

Rename fields

Default labels can get awkward. For example, “SUM of Total Sales” is correct but not something you want to present.

To rename it:

• Double-click the header in the pivot table
• Enter a clearer name, such as Total Revenue

This is a minor change, but it makes the table easier to read.

Change the name of the fields in the pivot table. Image by Author.

Pivot Table Calculated Fields in Google Sheets

If your dataset does not already have the value you need, create it using a calculated field. This way, you can run your own calculation inside the pivot table without changing the original data.

A calculated field creates a new value based on existing columns. For example, instead of using Total Sales directly, create a new metric like profit.

The pivot table will calculate it for you.

Here’s how to create one:

1. Go to the Pivot table editor
2. Click Add under Values 
3. Select Calculated field
4. Enter your formula

You will see a formula box where you can use your column names.

For example, let’s say my dataset has:

• Total Sales
• Unit Price
• Units Sold

Now, instead of relying on Total Sales, I can calculate it inside the pivot table. In the calculated field, I enter the following formula: 

='Units Sold' * 'Unit Price'

Press enter and rename it to Revenue in the pivot table. The pivot table will now calculate revenue automatically for each group.

Calculated fields in a pivot table. Image by Author.

If your pivot table is grouped by Region, the calculated field will show profit for each region. If grouped by Product Category, it will show profit per category.

You do not need to rebuild anything. The calculation adjusts based on how your pivot table is structured.

A few things to remember: 

• You must use exact column names in the formula
• Calculated fields work on aggregated data
• Results depend on how your pivot table is grouped 

Google Sheets Pivot Table vs Excel Pivot Table

Pivot table in Excel and Google Sheets offer the same core functionality. They help you summarize and analyze data. But here are some key differences: 

Quick way to remember: 

• Use Google Sheets for quick analysis and team collaboration
• Use Excel for advanced analysis and larger datasets

Common Issues with Pivot Tables in Google Sheets

Sometimes you run into issues and do not know what went wrong. Most of the time, the problem is small and easy to fix once you know where to look.

Here are some common issues you may come across and their solutions: 

Data not updating

If your pivot table does not reflect new data, it may be because the pivot table is linked to a fixed range.

To fix it:

1. Go to the Pivot table editor
2. Check the Data range
3. Update it to include new rows

Tip: Use a full column range like A:I if your data keeps growing.

Incorrect data range

Some rows or columns may be missing in the pivot table because the wrong range was selected while creating the pivot table.

To fix it:

1. Open the Pivot table editor
2. Update the Data range
3. Make sure all columns are included

Even missing one column can change your results.

Missing or incorrect headers

Sometimes the dataset may not have proper headers, which is why fields do not appear correctly in the pivot table.

To fix it:

• Make sure the first row contains column names
• Remove blank header cells
• Avoid duplicate header names

Wrong aggregation type

Your numbers may look incorrect because the pivot table is using the wrong calculation, like COUNT() instead of SUM().

To fix it:

1. Go to Values in the Pivot table editor

2. Click the field (for example, Total Sales)

3. Change Summarize by to the correct option

Numbers stored as text

Totals or calculations may not work because your numeric values could be stored as text.

To fix it:

1. Select the column in your dataset
2. Go to Format > Number
3. Choose Number or Currency
4. Refresh the pivot table

Best Practices for Using Pivot Tables

Here are some small habits that make a big difference in how easy Pivot tables are to use:

• Clean your data first because if the data is messy, the pivot table will be messy too.

• Use clear, consistent column names like Region, Total Sales, or Units Sold. 

• Avoid empty headers or duplicates.

• Begin with one field in Rows and one in Values, then build from there. 

• Don’t overload the structure because too many rows, columns, or nested fields make the table harder to read. 

Final Thoughts

At this point, you know how to build, adjust, and read a pivot table. Now it’s time to use it on real data.

Start with a dataset you already have. It could be anything, like sales data, survey responses, or any other data with rows and columns.

Build a simple table first:

• Add one field to Rows
• Add one numeric field to Values

Then change one thing at a time:

• Switch the calculation
• Add a filter
• Try grouping

If you’re following along, don’t try every feature at once. Pick one question and use a pivot table to answer it. Then move to the next.