PivotTables for Expense Categories: The Six Things That Go Wrong
Dates that will not group, a value field that counts instead of sums, and a calculated field that computes on the total rather than each row. Here is an expense pivot built properly, and the fix for every failure that stops people using them.
The Right Tool, With Six Sharp Edges
Summarising a year of transactions by category and month is exactly what a PivotTable is for. Ten seconds of dragging replaces a page of SUMIFS. The reason people stop using them is a short list of failures that look like the feature is broken.
None of them is a bug. Each is Excel responding correctly to source data that is not quite right. This article builds the pivot and works through all six.
Try the overhead rate calculatorTurn the category totals a pivot produces into an overhead rate you can apply to jobs.The source data, which decides everything
Swipe sideways to compare columns.
| Column | Type | Note |
|---|---|---|
| Date | Real date | Not text. This is failure number one. |
| Vendor | Text | Consistent spelling matters more than you expect |
| Category | Text | A short controlled list, not free text |
| Subcategory | Text | Optional, but it saves rebuilding later |
| Amount | Number | One sign convention throughout |
| Account | Text | Which card or bank account |
| Business or personal | Text | Lets one dataset serve both |
Building it
- Click inside the table, then Insert, PivotTable, New Worksheet.
- Drag Category to Rows.
- Drag Date to Columns. Excel usually offers a Months grouping automatically; if not, right-click any date in the pivot, choose Group, and select Months and Years.
- Drag Amount to Values. Check that it says Sum of Amount, not Count of Amount.
- Add a Slicer on Account and a Timeline on Date, both from the PivotTable Analyze tab.
Swipe sideways to compare columns.
| Category | Total | % of total |
|---|---|---|
| Payroll | $142,600 | 56.9% |
| Marketing | $31,500 | 12.6% |
| Rent | $28,800 | 11.5% |
| Travel | $12,880 | 5.1% |
| Software | $9,240 | 3.7% |
| Professional fees | $8,100 | 3.2% |
| Insurance | $6,400 | 2.6% |
| Utilities | $4,760 | 1.9% |
| Supplies | $3,320 | 1.3% |
| Meals | $2,940 | 1.2% |
| Total | $250,540 | 100.0% |
The percentage column is not a formula. Drag Amount into Values a second time, then right-click it, Show Values As, % of Column Total. Two fields from one source column, one showing currency and one showing share.
The six failures
1. Cannot group that selection
The date column contains something that is not a date. Usually text dates imported from a bank export, occasionally a blank row, occasionally a stray header. Select the column and check the alignment: numbers and dates right-align by default, text left-aligns.
Fix with Data, Text to Columns, Finish, which forces re-evaluation of the whole column. For stubborn formats use =DATEVALUE(A2) in a helper column, or Power Query, which lets you set the source locale explicitly.
2. Count of Amount instead of Sum of Amount
Excel defaults a value field to Count when the column contains any blank or text cell. It is telling you the data is not clean. Find the offender with =COUNT(range) against =COUNTA(range): if they differ, some cells are not numbers.
Change it via Value Field Settings, but fix the source too. A single text amount is a transaction that will be wrong in every report built on this data, not just this one.
3. A calculated field that gives the wrong answer
This is the subtle one. A calculated field operates on the aggregated values, not row by row. A field defined as Amount × TaxRate applies the rate to the sum of Amount for the group, which is fine. But one involving division or a conditional gives an answer that is not the sum of the row-level results.
4. GETPIVOTDATA appearing when you click a pivot cell
Clicking a pivot cell while writing a formula inserts a GETPIVOTDATA reference rather than a cell address, and it does not fill down. The function is genuinely useful because it survives the pivot layout changing, but it is not what you want mid-formula.
Turn it off at PivotTable Analyze, the dropdown next to Options, Generate GetPivotData. Or type the cell reference by hand rather than clicking.
5. The pivot does not include new rows
The source is a fixed range rather than a table. Either convert the source with Ctrl+T and repoint the pivot, or change the source range at PivotTable Analyze, Change Data Source. The table route is the one that does not need doing again.
6. Stale numbers
PivotTables do not recalculate with the workbook. They refresh on demand, with Alt+F5 or the Refresh button. Set PivotTable Options, Data, Refresh data when opening the file, so a report handed to someone else is not showing last month.
Show Values As, which is where the analysis is
Swipe sideways to compare columns.
| Setting | Answers |
|---|---|
| % of Column Total | What share of this month went to each category |
| % of Row Total | How one category is spread across the months |
| % of Grand Total | What share of the year each cell represents |
| Difference From, previous month | What changed, in currency |
| % Difference From, previous month | What changed, proportionally |
| Running Total In, Date | Year to date by category |
| Rank Largest to Smallest | Which categories to look at first |
Difference From with the previous month is the most useful and the least used. Drag Amount into Values three times and set the three to Sum, Difference From previous month, and % Difference From previous month. That is a variance report built entirely by dragging.
Three settings to change once
- PivotTable Options, Layout, For empty cells show: 0. Blanks read as missing data; zeros read as no spend.
- PivotTable Options, Layout, Autofit column widths on update: off. Otherwise every refresh undoes your formatting.
- Design, Report Layout, Show in Tabular Form, plus Repeat All Item Labels. This produces a layout you can copy into another sheet and use as data, which the default compact form cannot.
What the pivot does not do
- Categorise transactions. That is a judgement per row, and the pivot is only as good as the category column. Inconsistent vendor names and free-text categories are the main reason expense analysis fails.
- Handle split transactions. A single payment covering two categories needs two rows in the source, not clever handling in the pivot.
- Deal with refunds and reversals automatically. Decide a sign convention and apply it in the source, or a refund will inflate a category rather than reduce it.
- Distinguish cash from accrual. A pivot on payment dates is a cash view; matching expenses to the period they relate to needs a separate period column.
- Tell you what is deductible. Category totals are an input to that question, never the answer.
- Detect anomalies. A vendor charged twice looks like a larger category total and nothing else. Add a duplicate check on the source data.
Why does Excel say "Cannot group that selection"?
The date column contains at least one value that is not a date, usually text imported from a bank export, or a blank. Select the column, use Data, Text to Columns, and click Finish, which forces Excel to re-evaluate every cell.
Why is my PivotTable counting instead of summing?
Because the amount column contains a blank or a text value somewhere. Excel defaults to Count in that case. You can change it in Value Field Settings, but find and fix the bad cell too, since it is wrong for every other calculation as well.
Why does my calculated field give the wrong total?
Calculated fields operate on the aggregated sums, not on individual rows. Anything other than a simple multiplication by a constant should be a helper column in the source table instead. Ratios and conditional logic in particular will not sum the way you expect.
How do I stop GETPIVOTDATA appearing in my formulas?
PivotTable Analyze, the small dropdown beside Options, and untick Generate GetPivotData. It is a global setting rather than a per-pivot one, so you only need to do it once per installation.
How do I show percentages without a helper column?
Drag the amount field into Values a second time, right-click the new field, Show Values As, and pick % of Column Total or % of Grand Total. The same source column can appear in Values as many times as you like with different settings.
Should I use a PivotTable or SUMIFS?
PivotTable for exploring, where you do not yet know which cut matters and want to drag things around. SUMIFS for a fixed report that must sit in a specific layout, feed other formulas, and update without a refresh. Many good workbooks use a pivot to explore and SUMIFS to publish.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team