Pivot tables vs formulas for summarising data: which to use?

Both pivot tables and formulas can summarise data — count rows, sum by category, find averages. The right tool depends on how the output will be used and how often the analysis needs to change.

What a pivot table does that formulas cannot

A pivot table is an interactive summary. You drag fields into rows, columns, and values, and it builds the structure for you. Changing the view — grouping by a different field, adding a filter, seeing totals by quarter instead of month — takes seconds. No formulas to rewrite.

Pivot tables also handle things that would require long formula chains:

• Grouping dates by month, quarter, or year automatically
• Showing top 10 values in a field
• Percentage of total, running total, rank within a group — all built-in
• Cross-tabulating two fields (rows by months, columns by region)

For any analysis you expect to adjust, explore, or share with someone who needs to filter it: use a pivot table.

When formulas are the better fit

Formulas are better when the summary needs to live inside another structure — a dashboard, a report template, or a workbook where the output position has to stay fixed.

A SUMIFS or COUNTIFS formula in a cell stays in that cell. A pivot table expands and contracts as it refreshes, which can push adjacent cells around. If a chart, a named range, or another formula depends on a fixed position, formulas are safer.

Formulas are also better when:

• The logic requires a condition a pivot table cannot express (partial text match, a formula-derived criterion)
• The workbook will be shared with people who should not be able to accidentally reshape the summary
• The output is a single number, not a table

The quick test

Can you describe what you want in one sentence — “total sales by region and month”? That is a pivot table. Does the result need to live in a specific cell that other things depend on, or does the condition require a formula? That is SUMIFS.

Both together

Many workbooks use both. A pivot table builds the summary; GETPIVOTDATA formulas pull specific numbers from it into a fixed-layout report. The pivot table handles the heavy lifting, and the formulas pin the output where it needs to be.