Excel Pivot Tables: Complete Guide
Pivot tables summarise thousands of rows into a readable report in under a minute — without formulas. Here is how to create them, configure them, and fix every common problem.
Quick Start
Click anywhere in your data → Insert → PivotTable → OK → drag a category to Rows and a number to Values. You have a pivot table. Everything below makes it more powerful and reliable.
How to Create a Pivot Table: Step by Step
Prepare your source data
One header row, no merged cells, no blank rows or columns in the middle. Every column must have a unique header. Format the range as an Excel Table (Insert → Table) so the pivot table automatically expands when you add new rows.
Insert the pivot table
Click anywhere in your data → Insert → PivotTable. Choose "New Worksheet" — keeping the pivot table on its own sheet avoids layout conflicts. Click OK.
Add fields to Rows
Drag a field to the Rows area. This becomes the left-hand column of your report — typically a category like Region, Product, or Month.
Add a Values field
Drag a numeric field to Values. Excel defaults to SUM for numbers. Right-click the value in the pivot table → Summarise Values By to switch to Count, Average, Min, Max, or others.
Add Columns or Filters
Drag a second categorical field to Columns for a cross-tab report (e.g. sales by region AND by month). Drag a field to Filters to add a page-level filter above the table.
Refresh after data changes
Right-click the pivot table → Refresh. Or use PivotTable Analyze → Refresh All to refresh every pivot table in the workbook at once.
Grouping Dates by Month, Quarter, or Year
Right-click any date value in the pivot table → Group. Select the groupings you want — Years, Quarters, Months, Days — and Excel creates a hierarchy. Selecting multiple groupings (e.g. Years + Months) lets you drill down from year to month in the same pivot table.
If Group is greyed out, at least one cell in the date column is stored as text. Fix the dates first, then try grouping again. The date format guide covers every conversion method.
Calculated Fields: Add Formulas to a Pivot Table
A calculated field creates a new value column using a formula based on other fields. Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field.
Profit margin
= Profit / RevenuePivot table fields use = not formulas. Reference the field name exactly as it appears in your data.
Cost per unit
= Cost / UnitsCalculated fields apply to every row — you cannot make them conditional on specific rows.
Year-over-year growth
= (Sales2026 - Sales2025) / Sales2025Works only if both year columns exist as separate fields in your source data.
Slicers: Visual Filters for Pivot Tables
Slicers are clickable filter buttons that sit next to your pivot table. Go to PivotTable Analyze → Insert Slicer, choose the fields to filter by, and Excel creates a panel of buttons. Click a button to filter — click multiple with Ctrl held to select several. One slicer can control multiple pivot tables on the same sheet.
Show Values As: Percentages, Running Totals, Rank
Right-click any value in the pivot → Show Values As. Options include % of Grand Total, % of Row Total, % of Column Total, Running Total, Rank (largest to smallest), and Difference From (useful for year-over-year comparisons).
6 Common Pivot Table Problems — Fixed
New rows not appearing in pivot table
The pivot table's data range does not include the new rows. Right-click → Change Data Source to expand it, or format your source as an Excel Table before creating the pivot.
Dates grouping as individual days instead of months
Right-click any date in the pivot → Group → choose Months (or Years + Months for a hierarchy). If grouping is greyed out, some dates in the column are stored as text — convert them to real dates first.
Values showing Count instead of Sum
The column contains blank cells or text — Excel defaults to Count when it cannot confirm all values are numeric. Clean the source data, then right-click the value → Summarise Values By → Sum.
(blank) appearing as a row or column value
Source data has blank cells in that column. Filter them out in the pivot (uncheck (blank) in the field dropdown) or fill them in the source data using the Fill Blank Cells tool.
Pivot table not refreshing
Pivot tables never auto-refresh. Right-click → Refresh, or enable "Refresh when opening" in PivotTable Options → Data.
Cannot group dates — "Cannot group that selection"
At least one cell in the date column contains text or an error. Find and fix them with Go To Special → Constants → Text, or use the Spreadsheet Auditor to locate the problem cells.
Pivot table returning wrong totals?
Numbers stored as text are the most common cause of wrong pivot table sums. The free Spreadsheet Auditor finds all cells that look like numbers but are stored as text.
Frequently Asked Questions
How do I create a pivot table in Excel?
Click anywhere in your data → Insert → PivotTable → OK. Drag fields to Rows, Columns, Values, and Filters in the PivotTable Fields panel. Format your source data as an Excel Table first so new rows are included automatically.
Why is my pivot table not refreshing?
Pivot tables do not auto-refresh. Right-click → Refresh. For auto-refresh on open: PivotTable Analyze → Options → Data tab → check "Refresh data when opening the file". If new rows are missing, expand the data source range.
What is the difference between a pivot table and SUMIFS?
SUMIFS is a formula that recalculates automatically and outputs one value. A pivot table is an interactive report that summarises multiple dimensions at once but needs manual refresh. Use SUMIFS for single conditional totals; use pivot tables for multi-dimensional exploration and reporting.
Related Guides
SUMIFS and COUNTIFS
When a formula beats a pivot table — conditional sums that auto-update.
Read →
Fix Date Format Problems
Dates stored as text break pivot table grouping — how to fix them.
Read →
Data Cleaning Hub
Clean your source data before building the pivot — blank rows, duplicates, text numbers.
Read →
Spreadsheet Auditor
Find formula errors and text-formatted numbers that break pivot totals.
Read →