How to Create a PivotTable in Excel

Tired of manually sifting through endless rows of data just to find a simple answer? Excel's PivotTable is the tool you need to turn overwhelming spreadsheets into clear, insightful reports. This guide will walk you through exactly how to create and use them, step by step, even if you're a complete beginner.

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 is a PivotTable and Why Should You Use One?

A PivotTable is an interactive tool in Excel that allows you to quickly summarize, rearrange, and analyze large amounts of data without writing a single formula. Imagine you have a spreadsheet with thousands of sales records. Instead of manually filtering and sorting to find out which product sold best in which region, a PivotTable can give you that answer in a few clicks.

They are essential for anyone who works with data because they help you:

• Summarize Data Instantly: Condense thousands of rows into a compact and meaningful summary.
• Find Patterns and Trends: Easily spot top performers, identify outliers, and see relationships in your data that would otherwise be hidden.
• "Slice and Dice" Your Data: Dynamically filter, sort, and group information to look at it from different angles. For example, you can switch from viewing sales by month to sales by quarter with one click.
• Create Dynamic Reports: Build flexible reports and dashboards that can be updated in seconds when new data is added.

Before You Begin: Preparing Your Data

A PivotTable is only as good as the data it’s built on. Before you start, your data needs to be organized properly in a tabular format. Think of it like a simple database. Following these rules will prevent a vast majority of common PivotTable errors.

Make sure your data follows these simple guidelines:

• No Empty Rows or Columns: Your data should be a single, contiguous block. Don't have any completely blank rows or columns cutting through the middle of it.
• Unique Column Headers: Each column must have a distinct header in the very first row (e.g., 'Date', 'Region', 'Sales Total'). Your headers should be single-row and not merged cells.
• One Record Per Row: Each row should represent a single transaction or record.
• Consistent Data Types: Ensure all data within a column is of the same type. Dates should be formatted as dates, numbers as numbers, and text as text. Avoid mixing them in the same column.

How to Create a PivotTable: A Step-by-Step Guide

Once your data is clean and structured, creating the PivotTable is straightforward. We'll use our sample sales data for this example.

Step 1: Select Your Data

First, click on any single cell inside your data table. You don't need to highlight the entire table, Excel is smart enough to detect the full range of your data set as long as it's formatted correctly.

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

Step 2: Insert the PivotTable

With a cell selected, go to the Insert tab on the Ribbon and click the PivotTable button. This will be the first option on the far left.

A "Create PivotTable" dialog box will appear. Excel will have automatically selected the data range for you. The default setting is to place the new PivotTable in a New Worksheet, which is usually the best choice to keep your work organized. Click OK.

You’ll now see a new worksheet with an empty PivotTable placeholder on the left and the PivotTable Fields pane on the right. This pane is your control panel.

Step 3: Build Your Report Using the PivotTable Fields Pane

The PivotTable Fields pane is where the magic happens. It's divided into two main sections: a list of your column headers (or "fields") at the top, and four empty areas at the bottom. Your job is to drag the fields from the top list into these four areas to build your report.

Here’s what each area does:

• Rows: Fields placed here will appear as rows on the left side of your PivotTable. This is typically used for categorical data you want to analyze, like 'Product' or 'Region'.
• Columns: Fields placed here create columns across the top of your PivotTable. This is great for comparing data side-by-side, such as different months or regions.
• Values: This area is for the numbers you want to calculate (e.g., 'summarize', 'count', 'average'). It almost always contains a numerical field like 'Sales Total' or 'Units Sold'. Excel will automatically sum numbers and count text fields.
• Filters: Put a field here to create a top-level filter for your entire report. For example, drag 'Region' here to quickly filter the entire table to show data for only one region at a time.

Step 4: Use the PivotTable to Analyze Data

Let’s use our new PivotTable to answer a classic business question: "What were our total sales for each product, broken down by region?"

Here's how to build it:

1. Find the Product field in your Fields List and drag it into the Rows area. You’ll immediately see a list of your unique products appear on your worksheet.
2. Next, find the Region field and drag it into the Columns area. Your report will now have columns for each sales region.
3. Finally, find the Sales Total field and drag it into the Values area.

That's it! In three simple drags, you've created a summarized report. Excel automatically calculated the total sales for each product within each region, including Grand Totals for both rows and columns.

Customizing and Refining Your PivotTable

The basic report is great, but the real power of PivotTables comes from customization. Let's explore a few ways to refine our analysis.

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

Change the Calculation Type

By default, Excel will sum your values. But what if you wanted to see the average sale, or just count how many sales there were?

In the Values area, click the field ('Sum of Sales Total'). Select Value Field Settings. From here, you can choose from various functions like Count, Average, Max, Min, and more. Choose Average and click OK. The entire table will now show the average sale amount instead of the total.

Sort Your Data to Find Top Performers

To quickly find the best-selling product, you can sort by the Grand Total. Right-click on any number in the Grand Total column, go to Sort, and select Sort Largest to Smallest. The table will instantly reorder itself to bring your top products to the top.

Filter with Slicers for Interactive Reports

Manually dragging fields into the Filter box works, but Slicers provide a much more visual and user-friendly way to filter your data.

Click anywhere inside your PivotTable. Go to the PivotTable Analyze tab and click Insert Slicer. A dialog box will appear listing all your fields. Check the box for 'Category' and 'Region', then click OK. You'll now have interactive buttons that you can use to filter your report with a single click. Click "Electronics" to see only electronics sales, then hold Ctrl and click "Appliances" to see both at once.

Visualize Your Data with a PivotChart

A PivotChart is a chart that is linked directly to your PivotTable. Any changes you make to the PivotTable (like filtering with a Slicer) will automatically update the chart.

Go to the PivotTable Analyze tab and select PivotChart. Choose a chart type that suits your data, like a Clustered Column chart, and click OK. Now you have a dynamic visual that complements your report.

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

Advanced Tips to Take Your Skills Further

Grouping Data by Dates or Numbers

This is an incredibly useful feature. If you have a date field in your Rows or Columns, you can group daily data into months, quarters, and years. Right-click on any date cell inside the PivotTable, select Group, and choose your desired time intervals. You can do the same for numbers, grouping them into numerical ranges (e.g., grouping products by price brackets).

Refreshing Your Data Source

Remember, a PivotTable does not automatically update when you add, remove, or change data in your source table. If your source data changes, you must refresh the PivotTable. Simply right-click anywhere in the PivotTable and hit Refresh (or press Alt+F5).

Final Thoughts

Learning to use PivotTables is one of the single most impactful skills for anyone who works with data in Excel. They automate the tedious work of summarizing information, allowing you to move straight to analysis and decision-making without complex formulas and manual calculations.

While PivotTables are powerful for analyzing a single spreadsheet, the challenge often begins before you even open Excel. Many teams spend hours manually exporting data from platforms like Google Analytics, Shopify, Salesforce, and ad networks, then stitching it all together just to create that source file. At Graphed, we eliminate that entire process. You can connect all your sources in seconds, then use plain English to build real-time reports and dashboards. Instead of spending your Monday wrestling with CSVs, you could just ask, "Show me a comparison of Facebook ad spend vs. Shopify revenue by campaign for last month," and get an answer instantly.