How to summarize data in Excel
When a spreadsheet has thousands of rows, you need the summary, not the rows: totals by category, counts, averages. Excel gives you several ways to do it — here are the five that matter, when to use each, and the one-step way to summarise an entire file automatically.
Five ways to summarise, and when to use each
Match the method to the question.
The workhorse is the PivotTable: drag a category into rows, a number into values, and you get totals by group with no formulas — the fastest way to summarise by category. Use it whenever you want to slice a big table by one or two dimensions.
For a filtered list, SUBTOTAL is better than SUM because it ignores hidden rows and other subtotals — the total updates as you filter. For specific answers, the IFS family (SUMIFS, COUNTIFS, AVERAGEIFS) sums or counts by condition, which is ideal when you want one number, not a whole table.
Group & Outline collapses rows into subtotalled sections you can expand and collapse — good for a printable summary. And Analyze Data gives a quick automatic look at patterns. Which you pick depends on whether you want a table (PivotTable), a single number (IFS), or a collapsible view (Group).
Summarising methods compared
Pick by what you want out.
| Method | Best for | Output |
|---|---|---|
| PivotTable | Totals by category, fast | A summary table |
| SUBTOTAL | Filtered lists | A total that respects filters |
| SUMIFS / COUNTIFS | One conditional number | A single value |
| Group & Outline | Printable subtotals | Collapsible sections |
| Analyze Data | A quick look | Suggested summaries |
The gotcha: your total can be silently wrong
Summaries inherit the data’s problems.
Every summary method assumes clean data. If a numeric column is stored as text, SUM and PivotTables quietly skip it and your total is understated with no warning. If categories are inconsistent (‘NY’ vs ‘New York’), a PivotTable splits them into separate rows. And SUM over a filtered list includes hidden rows — use SUBTOTAL instead.
So the reliable habit is to check the data type and category consistency before you trust a summary. A wrong total that looks right is the most expensive kind of spreadsheet error.
Summarise the whole file in one step
Get every breakdown automatically.
If you want the summaries without choosing a method each time, DataHub Pro does it in one step. Upload the spreadsheet and it produces the standard breakdowns automatically — totals by each category, trends over time, top contributors, averages and counts — and shows them as a dashboard, with every number auditable to its source rows.
You can also ask for a specific summary in plain language (‘total sales by region and month’) and get it instantly. For anyone who summarises spreadsheets regularly, it’s the PivotTable result across every dimension at once, in about two minutes. Try it on a file.
Frequently asked questions
How do I summarize data in Excel?
The fastest way is a PivotTable: drag a category into Rows and a number into Values for instant totals by group. For a single conditional number use SUMIFS or COUNTIFS; for filtered lists use SUBTOTAL; for collapsible subtotals use Group & Outline. To summarise a whole file at once, upload it to DataHub Pro.
What is the fastest way to summarize a large spreadsheet?
A PivotTable summarises by category in seconds with no formulas. For a whole-file summary across every dimension at once, a spreadsheet-native tool like DataHub Pro auto-generates the breakdowns, trends and top contributors from an uploaded file in about two minutes.
What is the difference between SUM and SUBTOTAL?
SUM adds every cell in the range, including rows hidden by a filter. SUBTOTAL (with the right function number) ignores filtered-out rows and other subtotals, so the total updates as you filter. Use SUBTOTAL when summarising a filtered list.
Why is my Excel summary total wrong?
Usually because a numeric column is stored as text (SUM and PivotTables skip it), category labels are inconsistent and split into separate groups, or you used SUM over a filtered list (which includes hidden rows). Check data types and use SUBTOTAL for filtered data.
How do I summarize data by category in Excel?
Use a PivotTable (category in Rows, number in Values) for a table, or SUMIFS/AVERAGEIFS for a single category’s total. DataHub Pro produces all category breakdowns automatically from an uploaded file.
Can I summarize an entire Excel file automatically?
Yes. Upload it to DataHub Pro and it generates the standard summaries — totals by category, trends, top contributors, averages — automatically as a dashboard, with each figure auditable to its source rows.
Explore related guides
More Excel how-to guides.
Every summary from your file, automatically
Upload a spreadsheet and DataHub Pro builds the category totals, trends and top contributors in one step — auditable, no PivotTables. Free tier, then $14.99/mo.
Try it free on your file →