How to Run a Pivot Table in Excel

If you have rows and rows of data in an Excel spreadsheet, a Pivot Table is one of the fastest ways to summarize it. Instead of writing complex formulas to count items or add up totals, you can drag, drop, and click your way to an insightful report in minutes. This tutorial will walk you through exactly how to prepare your data, create your first Pivot Table, and use its most powerful features to answer business questions quickly.

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

First Things First: What is a Pivot Table?

A Pivot Table is an interactive tool in Excel that allows you to summarize and reorganize data from a larger table without changing the original data itself. Think of it as a reporting engine. You give it a table of raw data, and it allows you to "pivot" or rearrange that data to highlight different pieces of information.

For example, imagine you have a spreadsheet with a year's worth of sales transactions. It might include columns for Order Date, Product Category, Sales Region, Sales Rep, and Sale Amount. You could use a Pivot Table to answer questions like:

• What are the total sales for each region?
• Which sales rep had the highest sales last quarter?
• Which product category is most popular in the East region?

Trying to answer these questions with standard formulas like SUMIF or COUNTIF would be slow and complicated. A Pivot Table does the heavy lifting for you, turning thousands of rows into a clean, easy-to-read summary.

The Golden Rule: Preparing Your Data

Before you create a Pivot Table, your data needs to be structured properly. This is the most common reason people run into issues. Your data must be in a clean, tabular format. If you follow these simple rules, you'll avoid 99% of common Pivot Table problems.

1. Use a Tabular (Columnar) Format

Your data must be organized in simple columns and rows. Each column represents a specific attribute (like "Region" or "Sale Amount"), and each row represents a single record (like one sale transaction).

• No blank rows or columns. Remove any completely empty rows or columns from your dataset.
• No merged cells. Merged cells confuse Excel's ability to read your data range. Unmerge any cells within your data area.
• No subtotals. If your data already has summary rows (like subtotals or grand totals), get rid of them. The Pivot Table's job is to create new totals for you.

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

2. Every Column Needs a Unique Header

Each column must have a distinct header in the very first row. These headers tell the Pivot Table what each piece of data is (e.g., 'Product,' 'Price,' 'Units Sold'). Don't leave any headers blank.

3. Keep Data Types Consistent

Make sure the data within each column is consistent. Dates should be formatted as dates, numbers as numbers, and text as text. Avoid mixing them. For example, a "Sale Amount" column should only contain numerical values, not text like "N/A" or "pending." Clean up inconsistencies like "CA" and "California" so they are treated as the same item.

Creating Your First Pivot Table: Step-by-Step

Once your data is properly formatted, creating the Pivot Table itself is surprisingly fast. We'll use a sample sales data set with columns for 'Date', 'Region', 'Product', and 'Sales Amount'.

Step 1: Select Your Data

Click any single cell inside your prepared data set. You don't need to highlight the entire table, Excel is smart enough to detect the full range of your data automatically as long as there are no blank rows or columns breaking it up.

Step 2: Insert the Pivot Table

Navigate to the Insert tab in the Excel ribbon and click the PivotTable button. A dialog box will appear.

Excel will have already guessed your data range. The default option is to place the Pivot Table in a New Worksheet, which is the best practice for keeping your workbook organized. Just click OK.

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 3: Meet the PivotTable Fields Pane

A new, blank worksheet will appear, along with the PivotTable Fields pane on the right side of your screen. This pane is your command center. It has two main sections:

1. A list of all the fields (your column headers) at the top.
2. Four "areas" at the bottom: Filters, Columns, Rows, and Values.

Your job is to drag and drop the fields from the list into these four areas to build your report.

What Do the Four Areas Do?

• Rows: Fields you put here will appear as row labels down the left side of your Pivot Table. For example, if you drag 'Region' here, you'll get a unique row for "East," "West," "North," and "South."
• Columns: Fields here will become column headers across the top. This is great for comparing data side-by-side, like sales by quarter.
• Values: This is where you put the data you want to calculate. It's almost always a numerical field, like 'Sales Amount' or 'Units Sold'. Excel will then sum, count, or average these values.
• Filters: Fields here let you apply a high-level filter to your entire report. You could drag 'Year' here to easily switch between viewing data for 2022 vs. 2023.

Building a Simple Sales Report: An Example

Let's use our sample data to build a practical report. Our goal is to see the total sales for each region.

From the PivotTable Fields list, do the following:

1. Drag the 'Region' field and drop it into the Rows area.
2. Drag the 'Sales Amount' field and drop it into the Values area.

That's it! Instantly, your Pivot Table comes to life, showing a clean summary of total sales broken down by each region. Notice how Excel automatically called it "Sum of Sales Amount."

Adding More Detail

Now, let's break it down further. We want to see which products sold in each region.

• Drag the 'Product' field into the Rows area, right below the 'Region' field.

The Pivot Table immediately updates to a nested report, showing each region with an expandable list of the products sold within that region and their corresponding sales totals.

Advanced (but Easy!) Pivot Table Tricks

Once you've built a basic table, here are a few other powerful features you can use to refine your analysis.

Change the Calculation (Sum to Count or Average)

By default, Excel sums numerical data in the Values area. But what if you wanted to know the number of sales, not the total dollar amount? In the Values area, click on "Sum of Sales Amount," then select Value Field Settings... In the new window, you can change the calculation type from Sum to Count, Average, Max, Min, and more. Choose 'Count' to see the total number of transactions per region.

Sort Your Data

Want to see which region had the highest sales? Right-click on any of the sales amount numbers in your Pivot Table, go to Sort, and select Sort Largest to Smallest. The table will reorder to show your top-performing regions first.

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

Filtering with Slicers

While the Filters area works, Slicers are a much more visual and user-friendly way to filter your data.

1. Click anywhere inside your Pivot Table.
2. Go to the PivotTable Analyze tab in the ribbon.
3. Click Insert Slicer.
4. Check the box for the field you want to filter by - let's use 'Product'.

A new Slicer box with clickable buttons will appear on your worksheet. Now, you can simply click "Laptop" or "Monitor" to instantly filter your entire report to show data for just that product.

Group by Dates

Dealing with dates is a common task. If you place a date field in your Rows or Columns, Excel will often group it into Years, Quarters, and Months for you. If it doesn't, you can do it manually. Right-click on any date in the row labels, select Group, and choose how you want to aggregate the dates (e.g., by Months, Quarters, or Years).

Always Refresh Your Data

This is extremely important: Pivot Tables do not update automatically when you change your source data. If you add new sales data or correct a mistake in your source table, you must tell the Pivot Table to update. To do this, simply right-click anywhere on the Pivot Table and hit Refresh.

Final Thoughts

Pivot Tables are one of Excel's most powerful features, and mastering them separates casual users from proficient ones. They transform the tedious task of manual data summarization into a quick, flexible, and error-free process, giving you more time to analyze your findings and make decisions.

We know that even with great tools like Excel, the weekly ritual of exporting CSVs from different platforms, cleaning them up, and building reports can consume hours. We built Graphed because we believe getting insights shouldn't require manual drudgery. You can connect sources like Google Analytics, Shopify, and your CRM in seconds, then create real-time dashboards just by asking questions in plain English - no exporting or pivot tables required.