How to Create a Pivot Table

Wrangling large spreadsheets to find meaningful answers can feel like a huge chore, often involving complicated formulas and manual sorting. A Pivot table is one of the most powerful - yet often intimidating - features in Excel and Google Sheets that cuts through this complexity. This guide will walk you through exactly how to create and use pivot tables to summarize your data in just a few clicks.

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

So, What Exactly Is a Pivot Table?

Think of a pivot table as an interactive summary report for a large dataset. It lets you quickly reorganize and aggregate rows and columns of data to see it from different angles, all without touching your original spreadsheet. You can "pivot" your data to answer questions like, "What were our total sales per region last quarter?" or "Which marketing channel brought in the most new customers each month?"

Imagine you have a massive list of sales transactions with columns for Date, Region, Product Category, Sales Rep, and Sale Amount. This raw list might have thousands of rows, making it nearly impossible to spot trends. A pivot table can instantly transform that list into a clean table showing you the total sales for each region, broken down by product category.

It essentially automates the work of manually filtering, calculating, and organizing your data. Instead of writing dozens of SUMIF or COUNTIF formulas, you just drag and drop!

Step 1: Get Your Data Ready

Before you build your pivot table, your source data needs to be clean and organized. Following a few simple rules will prevent most common pivot table headaches down the line. Your data should be in a single, continuous block with no empty rows or columns cutting through it.

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

Your Pre-Pivot Table Checklist:

• Use a Unique Header for Every Column: Each column needs a clear, unique title in the very first row (e.g., 'Date', 'Salesperson', 'Revenue'). Pivot tables use these headers to categorize your data.
• No Blank Rows or Columns: Make sure there are no completely empty rows or columns within your dataset. This can cause the pivot table to miss some of your data.
• Format Data as a Table (Excel Pro-Tip): This is a game-changer. Select any cell in your data range and press Ctrl+T (or Cmd+T on Mac) to turn it into an official Excel Table. This makes your data dynamic, and when you add new rows of data later, the pivot table will automatically include them when you hit refresh.
• Keep Data Types Consistent: Ensure columns have consistent data. For example, a column for sales amounts should only contain numbers, and a date column should only contain valid dates. Inconsistent entries (like 'NY' and 'New York' in the same 'State' column) will be treated as separate items.

Here’s an example of a good, clean dataset ready for a pivot table: Note how every column has a name, there are no gaps, and the data is consistent.

Step 2: Create the Pivot Table in Excel

Once your data is prepped, creating the pivot table itself only takes a few seconds. For this example, let’s use our sales data to find out the total revenue generated by each Sales Rep in each Region.

1. Click anywhere inside your data range.
2. Navigate to the Insert tab on the Excel ribbon and click PivotTable.
3. A dialog box will pop up. Since you formatted your data as a Table, Excel should have automatically selected the correct data range (e.g., 'Table1'). Leave the option to place the PivotTable in a New Worksheet selected and click OK. Using a new sheet keeps your work tidy.
4. You now have a blank pivot table on a new sheet and the PivotTable Fields pane on the right side of your screen. This pane is your control center.

The PivotTable Fields pane lists all your column headers at the top. Below that are four boxes that represent the different parts of your report:

• Filters: Lets you apply a high-level filter to your entire report (e.g., filtering for a specific year).
• Columns: The fields you place here will become the column headers in your pivot table report.
• Rows: The fields you place here will become the row labels on the left side of your report.
• Values: This is where you put the numeric data you want to aggregate. By default, it will calculate the SUM, but you can change it to COUNT, AVERAGE, etc.

Building Your Report by Dragging and Dropping

Now, let's build the report to answer our question: "What is the total revenue for each Sales Rep in each Region?"

1. Drag the Region field from the list and drop it into the Rows box. You'll see the regions appear as row labels in your pivot table.
2. Drag the Sales Repr field and drop it into the Rows box, right underneath Region. This nests the reps under their respective regions.
3. Drag the Revenue field and drop it into the Values box.

Just like that, your pivot table is created! It should instantly show you a summarized report with total revenue by region, broken down by each salesperson.

Step 3: Creating a Pivot Table in Google Sheets

The process in Google Sheets is almost identical, though the interface looks slightly different.

1. Select your data range.
2. Go to the menu and click Data > Pivot table.
3. Choose to insert it into a new sheet and click Create.
4. A blank pivot table and the Pivot table editor sidebar will appear.

The editor in Google Sheets uses "Suggested" pivot tables, or you can manually add fields to the Rows, Columns, and Values sections just like in Excel. Drag and drop the same fields from our example above into their corresponding sections to build the same report.

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

Step 4: Customizing and Refining Your Pivot Table

Your initial pivot table is a great start, but the real power comes from refining it to find deeper insights. Here are a few common customizations.

Change the Calculation Type

What if you want to see the number of sales instead of the total revenue? Easy.

In the Values area of the PivotTable Fields pane, click the dropdown on 'Sum of Revenue' and select Value Field Settings. In the dialog box, you can change the calculation from Sum to Count, Average, Max, or Min. Changing it to 'Count' will show you the total number of transactions per rep and region.

Format Your Numbers

Raw numbers can be hard to read. To format your 'Sum of Revenue' as currency:

1. Go to Value Field Settings again.
2. Click the Number Format button at the bottom-left.
3. Choose Currency from the category list and click OK.

Clean formatting instantly makes your report more professional and easier to understand.

Sort and Filter Your Data

You can sort your data to quickly see top performers. Simply right-click on any revenue total and choose Sort > Sort Largest to Smallest. You can also use the dropdown arrows next to the 'Row Labels' or 'Column Labels' headers in the pivot table to filter out specific items you don't want to see.

Add a Slicer for Interactive Filtering

Slicers are basically stylish filter buttons that make it easy for anyone (even non-Excel users) to filter your pivot table data.

1. Click anywhere inside your pivot table.
2. Go to the PivotTable Analyze tab (or 'Analyze' on older versions).
3. Click Insert Slicer.
4. Check the box for the field you want to filter by — let's say, 'Product Category' — and click OK.

A clickable menu will appear on your sheet. Now you can click on any product category in the slicer to see your pivot table instantly update to show sales figures for just that category. It’s a great way to build interactive dashboards.

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

Troubleshooting: "My Pivot Table Isn't Updating!"

This is the most common hiccup people face. Remember, pivot tables are not live, they are a snapshot of your data at the time of creation. If you change or add data to your source table, you need to refresh the pivot table to see the changes.

Simply right-click anywhere on the pivot table and select Refresh.

If you didn't format your original source data as an official Excel Table (using Ctrl+T), you may also need to manually update the data source range. You can do this by going to the PivotTable Analyze tab and clicking Change Data Source.

Final Thoughts

Mastering pivot tables is a fundamental step in becoming more comfortable with data analysis in tools like Excel or Google Sheets. It replaces manual calculation and guesswork with a fast, dynamic, and repeatable process for summarizing your data and uncovering the patterns hidden within it.

Of course, even with pivot tables, the process of exporting data from all your different platforms — analytics, advertising, sales — and cleaning it in a spreadsheet still takes a ton of time. At Graphed we felt this pain ourselves, which is why we built a tool to get you straight to the insights. By directly connecting your data sources, we let you create real-time dashboards and reports simply by asking questions in a conversational way — no more exporting CSVs or building pivot tables from scratch to get the metrics you need.