FormulaSmith
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
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 Table2900Calculated Fields in Pivot Table from Pivot Fields Excel1000Excel Pivot Table COUNT Distinct880Excel Pivot Table COUNT UNIQUE880How to Refresh Pivot Table in Excel880How to Update a Pivot Table in Excel720How to Make a Pivot Table Excel590How to Delete a Pivot Table in Excel590How to Edit Pivot Table in Excel590How to Remove Pivot Table in Excel590How to Modify a Pivot Table in Excel480Excel Pivot Table Format All Rows390Excel Pivot Table Show Total at Bottom390Excel Pivot Table Calculated Field390Excel Pivot Table Change Column390Excel Pivot Table Print Each Row on Separate Page390How to Insert Pivot Table in Excel390Ms Excel Pivot Table Add Calculated Field320Ms Excel Pivot Table FILTER Values by NOT False320Excel Group Date Pivot FILTER320Ms Excel Pivot Table Move Grand Total to Top320Excel Pivot Table Training320Excel Pivot Calculated Field260Excel Pivot Table MEDIAN260Excel Formula Pivot260Excel Sort Pivot Table260Updating Pivot Tables in Excel260How to Delete Pivot Table from Excel210What Is the Use of Pivot Table in Excel210Where Is Pivot Table in Excel210Excel Pivot Table Sort Values210How to Add Power Pivot to Excel170How to Add Powerpivot to Excel170Excel Pivot Table Group by Month170Excel Pivot Table Multiple Sheets170Refreshing a Pivot Table in Excel170How to Remove Pivot Table from Excel140Excel Pivot Table Tutorial PDF140Sample Pivot Table in Excel140Pivot Table Excel Youtube140Excel Auto Refresh Pivot Table110Excel Pivot Table Sort by Value110Conditional Formatting Excel Pivot Table110What Do Pivot Tables Do in Excel110Excel Pivot Tables for Dummies110Excel Pivot Table FILTER110Excel Pivot Table Group110Grouping in Excel Pivot Table110Excel Group by Pivot Table90Excel Change Pivot Table Data Range90Excel Pivot Table Change Data Source90Excel Pivot Table Data Source Reference Is NOT Valid90What Does a Pivot Table in Excel Do90How to Find Pivot Table in Excel90How to Use Power Pivot in Excel90Excel Pivot Table Slicer90Excel Pivot Table Top 1070Advanced Pivot Table Excel70Excel Pivot Table Collapse All70Weighted AVERAGE Excel Pivot Table70Excel Pivot Table Cannot Group That Selection70Excel Copy Pivot Table70Excel Pivot Table COUNT70Excel Pivot Table Formatting70How to Open a Pivot Table in Excel70Excel Pivot Table Practice70Excel Cumulative SUM Pivot Table50Excel Pivot Table Running Total50

Other sub-topics

Drop-down Lists · 160Cells · 497Remove Duplicates · 75Find Duplicates · 22Remove Blank Rows · 19Recover Unsaved File · 52Fn VLOOKUP · 37Sort Data · 30Fn UNIQUE · 21Highlight Duplicates · 6Remove Spaces · 19PDF to Excel · 20