FormulaSmith
Free tools
Free tools

Pivot Tables

Click any cell in the source data, then Insert > PivotTable > OK and drag field names into Rows, Columns and Values. Rows and Columns become the labels down the side and across the top, Values is the number being aggregated — Sum by default, but Count the moment the column contains any text or blank — and the source data itself is never altered. The catch that costs people an afternoon is that a PivotTable is a snapshot rather than a live formula: it keeps showing yesterday's numbers until you refresh it with Alt+F5, and rows appended below the original range are ignored altogether unless the source was an Excel Table or you repoint Change Data Source.

Back to DataAll guides

A worked example

A1:D2000 holds one row per order — Date, Region, Rep, Amount — with headers in row 1 and no blank rows.

Click A2 > Insert > PivotTable > OK > drag Region to Rows, Rep to Columns, Amount to Values

A grid of regions down the side and reps across the top, each cell the sum of Amount for that pair, with grand totals on both edges. Paste 200 new orders into rows 2001-2200 and the pivot does not change even after a refresh — the range was captured as A1:D2000. Converting the source to a Table with Ctrl+T before building is what makes future rows come along automatically.

Which one do I need?

If you want to…Use
You want a complete practice dataset with expected totalsCalculate revenue from 200 sales orders, build region/category and monthly PivotTables, then compare the reference answer tables
The source changed but the pivot still shows old totalsPivotTable Analyze > Refresh, or Alt+F5; PivotTable Options > Data > Refresh data when opening the file makes it automatic
You added rows and the pivot ignores themThe captured range is fixed — convert the source to a Table with Ctrl+T, or use PivotTable Analyze > Change Data Source
Values shows Count where you expected SumOne text entry or stray blank in that column forces Count; clean the column, then Value Field Settings > Sum
You need how many different customers, not how many rowsRebuild the pivot with "Add this data to the Data Model" ticked, then Value Field Settings > Distinct Count
You need a column the source does not have, such as marginPivotTable Analyze > Fields, Items & Sets > Calculated Field — remembering it computes on the totals, not row by row
Dates should collapse into months or quartersRight-click any date inside the pivot > Group > Months; every cell in that column has to be a real date first
You want clickable filter buttons instead of the filter drop-downsInsert a slicer and connect it to the pivot
The numbers should stay but the pivot should goCopy the pivot, Paste Special > Values elsewhere, then select the whole pivot and press Delete
The source is spread over several sheets or filesAppend them into one table first; a pivot can only read one source

Frequently asked questions

Why does my pivot table not update when the data changes?

A PivotTable reads a cached copy of the source, so edits to the sheet do not reach it until you refresh — Alt+F5, or PivotTable Analyze > Refresh. Note that refreshing re-reads the same range: if you added rows below it, refreshing alone will not pick them up, and you also need Change Data Source or a Table as the source.

Why does Excel say "Cannot group that selection"?

Grouping by month or quarter requires every cell in the date column to be a real date. One blank, one text entry, or a column of dates that are actually text will block it. Fix the column at source, refresh the pivot, then group — grouping cannot be forced past bad values.

How do I count unique values in a pivot table?

Tick "Add this data to the Data Model" in the Create PivotTable dialog, then Value Field Settings offers Distinct Count alongside Sum and Count. Without the Data Model that option does not exist; the fallback is a helper column of =IF(COUNTIF($A$2:A2,A2)=1,1,0) in the source, summed in the pivot.

Why is my calculated field giving the wrong average?

A calculated field is evaluated against each cell's aggregated totals, not row by row, so a field defined as Revenue/Units returns total revenue divided by total units. That is correct for a blended rate and wrong if you wanted the mean of the per-row values — for that, compute the ratio as a column in the source data and average that column in the pivot.

Excel Pivot a Pivot TableCalculated Fields in Pivot Table from Pivot Fields ExcelHow to Refresh Pivot Table in ExcelHow to Update a Pivot Table in ExcelHow to Make a Pivot Table ExcelHow to Delete a Pivot Table in ExcelExcel Pivot Table Format All RowsExcel Pivot Table Show Total at BottomExcel Pivot Table Calculated FieldExcel Pivot Table Change ColumnExcel Pivot Table Print Each Row on Separate PageMs Excel Pivot Table Add Calculated FieldMs Excel Pivot Table FILTER Values by Not FalseExcel Group Date Pivot FILTERMs Excel Pivot Table Move Grand Total to TopExcel Pivot Table TrainingExcel Pivot Calculated FieldExcel Pivot Table MEDIANExcel Formula PivotWhat Is the Use of Pivot Table in ExcelWhere Is Pivot Table in ExcelHow to Add Power Pivot to ExcelHow to Add Powerpivot to ExcelExcel Pivot Table Group by MonthExcel Pivot Table Multiple SheetsRefreshing a Pivot Table in ExcelExcel Pivot Table Tutorial PDFSample Pivot Table in ExcelPivot Table Excel YoutubeExcel Auto Refresh Pivot TableConditional Formatting Excel Pivot TableExcel Pivot Tables for DummiesExcel Pivot Table FILTERExcel Pivot Table GroupGrouping in Excel Pivot TableExcel Change Pivot Table Data RangeExcel Pivot Table Change Data SourceExcel Pivot Table Data Source Reference Is Not ValidHow to Find Pivot Table in ExcelHow to Use Power Pivot in ExcelExcel Pivot Table SlicerExcel Pivot Table Top 10Advanced Pivot Table ExcelExcel Pivot Table Collapse AllWeighted AVERAGE Excel Pivot TableExcel Pivot Table Cannot Group That SelectionExcel Copy Pivot TableExcel Pivot Table COUNTHow to Open a Pivot Table in ExcelExcel Pivot Table PracticeExcel Cumulative SUM Pivot TableExcel Pivot Table Running Total

Other sub-topics

Drop-down Lists · 121Cells · 494Remove Duplicates · 62Find Duplicates · 32Remove Blank Rows · 19Recover Unsaved File · 52Highlight Duplicates · 9Remove Data Validation · 20Sort Data · 30COUNT Duplicates · 15Fn VLOOKUP · 23Remove Spaces · 19