PivotTables for Sales and Expense Analysis in Excel

Building the first PivotTable

Click inside a Table, Insert tab → PivotTable → New Worksheet → OK. This gives a blank PivotTable and a field list. Drag a category field (e.g. Region) into Rows and a numeric field (e.g. Amount) into Values — instantly, a total per category. Drag a second field like SalesRep into Rows below Region, and the result nests: region, then rep within region.

Reshaping by dragging, not rewriting

Drag Region out of Rows and into Columns instead, and the whole shape flips — regions become column headers. This is the real power of Pivots over formulas: reshaping a summary is a drag, not a rebuild.

Changing the summary statistic

By default, a numeric field dropped into Values sums it. Click the field in the Values area, Value Field Settings, and switch to Average, Count, Max, or Min — whichever statistic actually answers the question being asked.

Grouping dates automatically

Drag a date field into Rows, then right-click any date in the Pivot and choose Group — Excel offers to group by Months, Quarters, or Years automatically. This turns hundreds of individual dates into a clean month-by-month summary instantly, no helper column required.

Calculated fields for computed metrics

For a metric that isn’t a straight sum — Average Order Value as Amount ÷ Count of Orders, for example — PivotTable Analyze tab → Fields, Items & Sets → Calculated Field builds it directly inside the Pivot, rather than as a separate formula living outside it.

A worked example

A monthly review needing “sales by region by month” used to mean rebuilding a SUMIFS-based summary table by hand every month. A PivotTable with the date field grouped by Month and Region in Rows reproduces the same summary in under a minute — refreshable with one click as new data arrives.

Mistakes that undermine a PivotTable

• —Building from a plain range instead of a Table. A range-based PivotTable doesn’t auto-expand as new rows get added — a proper Table does.
• —Forgetting to Refresh. Unlike formulas, PivotTables don’t auto-update when source data changes — right-click → Refresh is a required manual step every time the underlying data changes.
• —A numeric field silently summarizing as Count instead of Sum. This happens automatically if the field has any blank cells — worth checking whenever a total looks suspiciously small.