How to Create a Pivot Table in Google Spreadsheet

A pivot table can turn a messy spreadsheet of raw data into a clean, insightful report that answers your most important business questions. Learning to use them is like gaining a superpower for data analysis, and this guide will show you exactly how to do it in Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is a Pivot Table?

Think of a pivot table as a data summary tool. It takes a large, flat list of data - like a record of every sale your company has made - and allows you to dynamically reorganize and summarize it without changing the original data. You're not writing any complex formulas, you're just dragging, dropping, and "pivoting" the data to see it from different angles.

A pivot table helps you answer questions like:

• Which sales representative generated the most revenue last quarter?
• What were our top 5 best-selling products in the East region?
• How does our website traffic from organic search compare to traffic from paid ads month-over-month?
• Which marketing campaign had the lowest cost per lead?

Instead of manually filtering, sorting, and using SUMIF formulas for hours, you can get these answers in seconds.

Step 1: Get Your Data Ready

Your pivot table is only as good as the data you feed it. Before you start, your data needs to be organized in a clean, tabular format. This is the single most important step, and getting it right will save you a lot of headaches.

Follow this checklist:

• No Empty Columns or Rows: Your data should be a single, contiguous block. Don't have any completely blank rows or columns in the middle of your dataset.
• Every Column Needs a Header: Each column must have a unique, descriptive title in the very first row (e.g., "Date," "Sales Rep," "Region," "Revenue").
• One Type of Data Per Column: Ensure columns are consistent. A "Revenue" column should only contain numbers, and a "Date" column should only contain dates. Mixing text and numbers in the same column will cause issues.
• Format Consistently: Make sure spellings and capitalizations are consistent. "North" and "north" will be treated as two different regions by a pivot table.

Here’s an example of a well-formatted dataset, perfect for a pivot table analysis:

Step 2: Create a Pivot Table in Google Sheets

Once your data is clean and organized, creating the pivot table itself is incredibly simple. You can be up and running in just a few clicks.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

1. Select Your Data

Click on any single cell inside your dataset. You don't need to highlight the entire range. Google Sheets is smart enough to detect the whole table as long as there are no blank rows or columns breaking it up.

2. Insert the Pivot Table

Go to the main menu at the top of the screen and click on Insert > Pivot table.

3. Choose Where to Place It

A pop-up will appear. It confirms the data range it automatically selected and asks if you want to place the pivot table in a new sheet or on the existing sheet.

Pro-tip: Always choose New sheet. This keeps your raw data separate from your analysis, making your spreadsheet much cleaner and easier to manage.

Click "Create." Google Sheets will now open a new sheet with a blank pivot table and the Pivot table editor pane on the right side.

Step 3: Build Your Report Using the Pivot Table Editor

The Pivot table editor is where the magic happens. This sidebar is your control panel for summarizing your data. It consists of four main areas, which act like building blocks for your report:

• Rows: The data fields you add here will appear as row labels on the left side of your table. This is what you want to group your data by. (Example: Sales Rep, Product Category, Month)
• Columns: Fields added here will be displayed as column headers across the top of your table. This is another way to group or segment your data. (Example: Region, Quarter)
• Values: This is a crucial section. It’s where you put the metric you want to calculate. It defaults to summarizing numerical data with SUM and counting text data with COUNTA. (Example: SUM of Revenue, COUNT of Orders)
• Filters: Use this to narrow down your analysis. Add a field here to show data only for specific criteria. (Example: Filter to show only data for the "North" region or for "Q1 2024")

Example: Answering, "What is our total revenue per region?"

Let's use our sample data to answer this straightforward question. We want to see a list of regions and the total revenue for each.

1. Add Regions to Rows: In the Pivot table editor, find the "Rows" section and click "Add." Select "Region" from the list of available fields. You'll see your regions (East, North, West, South) instantly appear as rows in your table.
2. Add Revenue to Values: Next, find the "Values" section and click "Add." Select "Revenue." The pivot table will automatically calculate the SUM of revenue for each region.

Just like that, you’ve built your first report. It cleanly shows the total revenue across all four regions.

Step 4: Dig Deeper with Advanced Techniques

A simple summary is great, but the true power of pivot tables is in the ability to slice, dice, and view data from multiple perspectives. Let's build on our last example.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Break Down Data with Columns

Now, let's answer a more complex question: "Which products are selling best in each region?"

We already have revenue by region. To add the product dimension, simply go back to the Pivot table editor, click "Add" in the Columns section, and select "Product."

Instantly, your table expands. Now you have a detailed matrix showing the revenue for each specific product broken down by each sales region. With two drags-and-drops, you've created a report that would have taken ages to build manually.

Filtering for Focus

What if you only care about your top salesperson, Ana, and you only want to see her sales in the North and South regions? This is where filters are essential.

1. Click "Add" in the Filters section of the editor.
2. Select "Sales Rep".
3. In the filter box that appears, uncheck everything except "Ana" and click "OK." Your entire table now only reflects Ana's sales.
4. Repeat the process for the "Region" field, selecting only "North" and "South." Now you have a highly-focused report specific to your exact query.

Working with Calculated Fields and Custom Formulas

Sometimes you need to calculate a metric that doesn't exist in your source data. For example, maybe you want to calculate a 15% commission for each sales rep based on their total revenue.

You can create a Calculated field to do this right inside the pivot table.

1. In the Values section, click "Add," then select "Calculated field."
2. A new window will pop up. In the formula box, you can create your own custom formula referencing your other fields. To calculate a 15% commission on revenue, you'd type:

='Revenue' * 0.15

1. Give your new field a name, like "Sales Commission."
2. Click "Add," and a new "Sales Commission" value is added to your pivot table, calculated automatically.

Common Pivot Table Problems (And How to Fix Them)

"My pivot table isn't updating with new data!"

A pivot table does not automatically refresh when you add new rows to your source data sheet. The most common cause is that the data range is fixed. When you created the table, you might have selected A1:E100. If you add data in row 101, it's outside of that range and won't be included.

The Fix: Instead of a fixed range, define your data range with an open-ended reference. For example, change Sheet1!A1:E100 to Sheet1!A:E. This tells the pivot table to include all rows in columns A through E, so new entries will be included when refreshed.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

"My numbers aren't summing correctly – it's counting them instead."

If your "Revenue" column is being counted instead of summed, it usually means Google Sheets sees some of that data as text, not numbers. This can happen if there are accidental spaces, special characters (like a comma typed by mistake), or mixed data formats.

The Fix: Go back to your source data sheet. Select the entire column that's causing trouble. Go to Format > Number and apply a numeric format like "Currency" or "Number." Check carefully for any entries that look out of place or have leading spaces.

Final Thoughts

Pivot tables are a foundational skill for anyone working with data. They provide a powerful, flexible way to transform raw numbers into actionable reports, allowing you to quickly summarize trends, compare performance, and uncover insights buried in your spreadsheets without needing to be a formula wizard.

Manually exporting CSVs from different platforms to build these reports can quickly eat up your entire week. That's why we built Graphed. We wanted to eliminate the manual drudgery of consolidating data into spreadsheets in the first place. You simply connect your data sources like Google Analytics, Shopify, and Facebook Ads once, and then you can create live, auto-updating dashboards just by describing what you want to see in plain English. No more pivot tables - just real-time answers.