Spend analysis in Excel: method, limits, and when to stop

Published 25 August 2026 · 6 min read

Most spend analysis starts in Excel and a good deal of it should. The useful question is not whether Excel is a real tool — it is — but which part of the job it does well and which part quietly consumes weeks.

Is Excel good enough for spend analysis?

For a one-off analysis of a few thousand rows, yes. For recurring analysis of a large or long-tailed dataset, it depends entirely on which half of the job you mean — and the two halves have very different answers.

What Excel is genuinely good at

Once your data is classified, Excel is excellent. A pivot table over 20,000 categorised rows answers “what did we spend on hand protection last year, and with whom” in about fifteen seconds. Nothing you buy will beat that for speed or familiarity.

It is also fine for the whole job on small files. Under a few thousand lines, with a manageable number of suppliers, you can classify by hand in a day or two and be finished.

Where it breaks

The bottleneck is never the pivot. It is getting a category onto every row in the first place.

The usual approach is a mapping table — supplier name in one column, category in the next, joined with VLOOKUP or XLOOKUP. That works until it meets reality:

Suppliers sell across categories. Amazon appears under IT, stationery, facilities and catering. A supplier-level map cannot split them, so it puts the whole vendor in one bucket and the analysis inherits the error.
Names are not consistent. “RS Components”, “RS Components Ltd” and “R.S. COMPONENTS LTD.” are three lookup misses. You end up maintaining a synonym list by hand.
The long tail dominates the effort. The top 100 suppliers are quick. The remaining 3,000, each with two or three transactions, are where the fortnight goes — and they are the rows most likely to contain a saving.
It goes stale immediately. Next quarter brings new suppliers, and someone has to sit down and extend the map again.

If you are doing it in Excel, do it well

Normalise supplier names first. Upper-case, strip punctuation and the legal suffix, then dedupe. Half the lookup misses disappear.
Classify by description, not just supplier, wherever a description exists. It is the only way to split multi-category vendors.
Sort by value and stop early. Classify down to the point where the remaining unclassified spend is under 5%, then label the rest “tail” and move on. Perfect coverage is rarely worth the last week.
Keep the mapping table in its own sheet, versioned, never edited inside the transaction data.
Reconcile the total against finance before anyone sees a chart.

When to stop

The honest signals that Excel has stopped paying its way:

The file is over about 50,000 rows and calculation is slow enough to interrupt thinking.
You are re-doing the classification every quarter rather than extending it.
More than one person edits the mapping table and you cannot tell who changed what.
The unclassified bucket is over 15% and static, because the remaining rows are all long tail.

None of that means abandoning Excel. It means moving the classification step somewhere else and continuing to analyse the result in the tool you already know.