Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Include Google Analytics

Cody Schneider••8 min read

If you're staring at a massive spreadsheet filled with rows of raw data, pivot tables are the fastest way to make sense of it all. They turn overwhelming lists of information into clear, organized summaries, helping you spot trends and uncover insights without writing a single formula. This article will guide you through exactly how to create and use pivot tables in Google Sheets to analyze your data.

GraphedGraphed

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.

Watch Graphed demo video

What Exactly Is a Pivot Table?

Imagine you have a detailed list of every sale your company made last year. It includes the date, sales representative, product category, region, and sale amount. Right now, it's just a long, chronological list. Answering a simple question like "What were our total sales per region?" would require manual sorting, filtering, and summing.

A pivot table does that work for you in seconds. It’s an interactive tool that lets you summarize, group, count, and rearrange data from a larger table. You "pivot" your data by dragging and dropping different fields (like 'Region' or 'Product Category') to see the relationships between them in a new, condensed table. It’s like having a set of building blocks, you can rearrange them in different ways to build different structures and see how they fit together.

The Key Components of a Pivot Table

When you create a pivot table in Google Sheets, you’ll work with the Pivot Table editor, which has four main areas. Understanding these is the key to mastering pivot tables:

  • Rows: This is for data you want to group and display vertically down the side of your table. For example, you might place 'Sales Rep' or 'Product Category' here to see a list of them.
  • Columns: This groups your data horizontally across the top. You could add a 'Month' field here to see performance broken down month-by-month.
  • Values: This is where the numbers go. It’s the data you want to calculate or summarize. For instance, you would put 'Sales Amount' here and choose to see the SUM or the AVERAGE of those values for each row and column.
  • Filters: This lets you narrow down your data set to focus on specific information. You could add a filter for a particular 'Region' or a specific range of dates.

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.

How to Create a Pivot Table in Google Sheets: A Step-by-Step Guide

Let's walk through the exact process of creating a pivot table using a sample sales data set. We’ll follow a practical example to make the concepts clear.

Step 1: Get Your Data Ready

Pivot tables work best with clean, well-structured data. Before you start, make sure your spreadsheet follows these rules:

  • No Blank Rows or Columns: Your data should be a continuous block. Blank rows or columns in the middle can confuse the pivot table when it tries to automatically detect your data range.
  • Unique Column Headers: Every column must have a distinct name in the very first row (e.g., 'Date', 'Region', 'Sales Amount'). This is how you'll identify your data in the pivot table editor.
  • Consistent Formatting: Ensure your data is consistent. Dates should be formatted as dates, and numbers should be formatted as numbers, not text. Inconsistent data (like having "CA" and "California" in the same 'Region' column) will be treated as two separate items. Clean it up first!

Here’s a small sample of the data we'll use:

Step 2: Select Your Data and Insert the Pivot Table

First, click any single cell within your data set. You don’t need to highlight everything just yet.

  1. Navigate to the top menu and click Data.
  2. From the dropdown menu, select Pivot table.

A dialog box will appear. Google Sheets usually does a good job of automatically detecting your full range of data (e.g., 'Sheet1'!A1:E100). Next, you'll choose where to put the new pivot table. It's almost always best to choose New sheet. This keeps your raw data separate from your summary, which makes everything easier to manage.

Click Create. A new sheet will open with an empty pivot table layout and the Pivot table editor sidebar.

GraphedGraphed

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.

Watch Graphed demo video

Step 3: Configure Your Pivot Table

Now comes the fun part: telling the pivot table what you want to see. We'll use the editor on the right to build our report. Let’s try to answer a basic business question: "What are the total sales for each Sales Rep, broken down by Region?"

Add Rows

We want to see a list of sales reps, so we’ll add that field to the Rows section. In the sidebar, under Rows, click Add and select Sales Rep. Instantly, you'll see a unique, alphabetized list of your sales reps appear in the first column of your new table.

Add Columns

Next, we want to split the sales by region. Under Columns, click Add and select Region. Now you’ll see the different regions ('North', 'West', 'East') listed as column headers across the top of your pivot table.

Add Values

So far, we have the structure but no numbers. We need to tell the pivot table which data to calculate. Under Values, click Add and select Sales Amount. By default, Google Sheets will summarize this data using SUM. Now your table fills up with the total sales for each rep in each region, including grand totals for both rows and columns.

In just three clicks, you've transformed hundreds of rows of raw data into a clear summary report!

Step 4: Use a Filter to Hone In

What if you only want to see sales for 'Widget A' and 'Widget B' and exclude everything else? This is where filters come in handy.

  1. In the editor sidebar, find the Filters section and click Add.
  2. Select the field you want to filter by, in this case, Product.
  3. A new box appears. It will say "Showing all items". Click on it.
  4. Uncheck the products you want to exclude (like 'Widget C'), or click Clear and then select only the products you want to see (Widget A, Widget B).
  5. Click OK.

Your pivot table will instantly update to show the sales figures reflecting only the products you selected.

Advanced Tips for Pivot Tables

Once you've got the basics down, you can start using some more advanced features to gain deeper insights.

Grouping By Date

Pivot tables make analyzing data over time incredibly easy. If you have a Date column, you can group your data by month, quarter, or year.

  1. Add your Date field to either the Rows or Columns area.
  2. You'll see a list of every single date, which isn't very useful.
  3. Right-click on any of the date values within the pivot table itself.
  4. Select Create pivot date group.
  5. Choose an option like Year-Month or Quarter.

Your dates will automatically be grouped, giving you a clean, high-level view of performance over time.

Using Calculated Fields

Sometimes you need to create a new metric that doesn't exist in your source data. For example, maybe you want to calculate a 5% commission on sales.

  1. In the Values section of the editor, click Add.
  2. Select Calculated Field.
  3. In the formula box, you can create a formula using your existing column headers. Type: 'Sales Amount' * 0.05
  4. Give your new field a name, like Commission.
  5. Click enter, and a new column calculating the commission will appear in your pivot table.

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.

Changing the Value Calculation

By default, the Values field will SUM your numbers. But what if you need to find the average sale size or count the number of transactions? It's simple to change.

In the Values section, look for the field you added (e.g., SUM of Sales Amount). Click the dropdown box to change from SUM to other options like AVERAGE, COUNT, COUNTA, MAX, or MIN. This lets you quickly switch between different types of analysis.

Slicers for Interactive Filtering

While the Filters section works well, Slicers are a more user-friendly way to filter your data, especially if you're building a dashboard for others to use.

  1. Make sure your cursor is inside your pivot table.
  2. Go to the main menu and select Data > Add a slicer.
  3. A floating filter box will appear. In the sidebar on the right, choose which column it should control (e.g., Region).
  4. Now, you have a distinct button-based filter you can click to update your pivot table without going into the editor. You can add multiple slicers for different fields, like Region and Sales Reps, to quickly mix and match filters.

Final Thoughts

Pivot tables are a fundamental skill for anyone working in Google Sheets. They transform the tedious task of sorting, filtering, and writing complex formulas into an intuitive drag-and-drop process, allowing you to quickly summarize trends, compare figures, and get clear answers from your data.

Manually building reports, even with powerful tools like pivot tables, can still take up valuable time, especially when your data lives across different platforms like Google Analytics, Shopify, and Salesforce. At Graphed, we automate this process entirely. You can connect your marketing and sales data sources and then simply ask in plain English for the reports and dashboards you need—it's like having pivot tables that build themselves, instantly giving you the insights without any of the manual setup.

Related Articles