How to Make a Pivot Table in Excel
Pivot tables let you summarize thousands of rows into a clean report in seconds. Here's how to build one from scratch, customize it, and avoid the most common mistakes.
A pivot table takes a flat list of data — transactions, sales records, survey responses — and summarizes it however you need: totals by region, counts by category, averages by month. What would take dozens of formulas takes about 30 seconds with a pivot table.
Before you start
Your data needs to be in a proper table format:
- One row of headers at the top (no blank headers)
- No blank rows or columns in the middle
- Each column represents one type of data
If your data is messy, clean it first. Blank rows in the middle will silently cut off your pivot table.
Step-by-step: creating a pivot table
Step 1. Click anywhere inside your data range.
Step 2. Go to Insert → PivotTable. Excel will detect your data range automatically.
Step 3. Choose where to put the pivot table — a new sheet is usually cleaner. Click OK.
Step 4. In the PivotTable Fields panel on the right, drag fields into the four areas:
- Rows — What you want to group by (e.g., Region, Category, Month)
- Values — What you want to calculate (e.g., Revenue, Quantity)
- Columns — Optional second grouping dimension
- Filters — Optional top-level filter (e.g., Year)
Step 5. By default, Values shows "Sum of..." For counts or averages, click the field in the Values area → Value Field Settings → change the summary function.
✦ TIP
Right-click any cell in the pivot table and select Refresh after your source data changes. Pivot tables don't update automatically.
Grouping dates
If your data has a date column, you can group it by month, quarter, or year automatically:
- Add the date field to Rows
- Right-click any date in the pivot table → Group
- Select Month, Quarter, Year (or multiple)
This is much faster than adding helper columns to your raw data.
Sorting and filtering
Click the dropdown arrow next to a Row or Column label to filter by specific values, or sort A→Z / largest to smallest. For more control, add a Slicer: Insert → Slicer. Slicers are clickable buttons that filter the pivot table — useful if you're sharing the file with someone who isn't comfortable with Excel filters.
Common pivot table mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Blank rows in source data | Pivot table cuts off mid-dataset | Remove all blank rows before inserting |
| Headers with merged cells | Fields don't appear correctly | Un-merge all header cells |
| Forgetting to refresh | Pivot shows old data after updates | Right-click → Refresh, or enable auto-refresh |
| Text in a number column | Sum shows 0 or less than expected | Convert text-numbers to actual numbers |
The Griddy way
Pivot tables have a steep learning curve — mostly because the field layout isn't obvious at first, and the drag interface doesn't explain what it's doing. Instead of figuring out the row/column/values structure yourself, just tell Griddy what summary you need:
"Show me total revenue by region and month for Q1"
Griddy builds the pivot table, formats it, and if the source data isn't structured correctly, it fixes that too.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.