Spend analysis in Excel means organizing raw transaction records into a structured table, cleaning supplier names so totals group correctly, summarizing spend with pivot tables by supplier, category and period, and adding slicers and charts to make the result explorable. Done properly it takes a competent analyst two to three weeks the first time.
The part most guides leave out is what happens the second time. That is what this guide covers as well as the method itself.
If you are deciding whether to fund this rather than build it: the method below takes 2–3 weeks of analyst time, reaches under 80% classification accuracy, and repeats substantially in full every cycle. Skip to The honest diagnostic for the four questions that settle it.
The data you need before you start
Check your row count first. Excel's hard limit is 1,048,576 rows per worksheet. An enterprise above roughly $1B of spend commonly exceeds that in 12 months of transaction lines. If you are over, see the row limit before going further — the workaround has consequences.
Then count your blank PO numbers. That figure is your non-PO spend: purchases that never passed through a purchase order, and therefore never through procurement's normal controls. It is usually the most useful single number the entire exercise produces, because most tail spend — the roughly 80% of suppliers that account for about 20% of spend — has no PO behind it. More on tail spend →
The challenge of normalizing currencies
If you operate in multiple currencies, convert before you analyse, and document the method — this is the first thing a CFO will question.
Build an FX_Rates tab with a key of currency plus year-month, then convert:
excel
=[@Amount] * XLOOKUP([@Currency]&"-"&TEXT([@[Transaction Date]],"YYYY-MM"), tblFX[Key], tblFX[Rate])
Which rate to use? Period-average rates by month are commonly used for spend analysis: they smooth daily volatility, so year-on-year category comparisons reflect real spend behaviour rather than currency movement. Transaction-date spot rates are more precise for a single negotiation but make trend analysis unreadable. Pick one, state it on the README tab, and use the same basis every cycle — switching mid-stream invalidates comparisons.
Step 1: Structure your data as an Excel table
Put every transaction on one flat sheet, one row per transaction, with a single header row and no merged cells, no blank rows and no subtotals mixed into the data.
Select any cell in the range and press Ctrl + T. Confirm "My table has headers".
This matters more than it appears. A formatted table gives you structured references, auto-expanding ranges so pivots pick up new rows, and consistent formatting when you append next month's data. Analyses built on plain ranges break the moment the data grows.
Name the table something meaningful via Table Design > Table Name — tblSpend rather than Table1. You will thank yourself in three weeks.
Step 2: Clean supplier names (this is where the time goes)
This step is 70% of the work, and skipping it invalidates everything downstream. "Acme Inc", "ACME INCORPORATED", "Acme Inc." and "Acme, Inc" are four rows in your pivot table and one supplier in reality.
The practical sequence
Normalise the text first. In a helper column:
excel
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER([@[Supplier Name]]),".",""),",","")," LIMITED"," LTD"))
Extend the nested SUBSTITUTE calls to strip the suffixes that appear in your data — INC, INCORPORATED, LLC, LTD, GMBH, PLC, CORP, CO.
Build a mapping table, not a find-and-replace. Create a second sheet with two columns: Raw Name and Clean Name. Pull it into your main table with:
excel
=XLOOKUP([@[Supplier Clean Key]], tblMap[Raw Name], tblMap[Clean Name], [@[Supplier Name]])
The mapping table is the single most valuable artefact you will produce, because it is the only part of this work that survives into the next cycle. Do not do the cleansing with direct edits — you will repeat all of it next quarter.
Find near-duplicates. Sort the cleaned column alphabetically and read it. There is no elegant way to do this in Excel; fuzzy matching requires Power Query's Merge with a similarity threshold, and even that needs human review. Budget real time for it.
Check for parent-subsidiary relationships. "Acme Logistics" and "Acme Freight" may be one commercial relationship. Excel cannot know this. Your AP team often does.
Suplari has written a fuller guide to this problem: how to clean up messy procurement system information.
Step 3: Classify spend into categories
Add a Category column. You have three options in Excel, in ascending order of effort and accuracy:
GL account mapping — fastest, least accurate. Map each GL account to a category with a lookup. Good enough to see shape, too coarse for sourcing decisions.
Supplier-level mapping — assign each cleaned supplier a primary category. Works well until a supplier sells you three different things, which the large ones always do.
Keyword rules on the description field — most accurate available in Excel:
excel
=IFS(
ISNUMBER(SEARCH("license",[@Description])),"Software",
ISNUMBER(SEARCH("consult",[@Description])),"Professional Services",
ISNUMBER(SEARCH("freight",[@Description])),"Logistics",
TRUE,"Unclassified")
Track your Unclassified percentage. If it exceeds 20% of spend, your analysis will not survive scrutiny in a category review.
Manual and rules-based classification reaches under 80% accuracy in practice. That figure is not a criticism of anyone's effort — it is what rule-writing achieves against real-world description fields.
Step 4: Build the pivot tables
With clean data, this part is fast. Insert > PivotTable from your table.
That last one is the most useful and the most often skipped. Use Distinct Count (available when you tick "Add this data to the Data Model" when creating the pivot). A category with 40 suppliers and $2M of spend is a consolidation opportunity; a category with 3 suppliers and $2M is a risk conversation.
Right-click any date field and choose Group > Months, Years to get period analysis without adding helper columns.
Step 5: Add slicers and charts
PivotTable Analyze > Insert Slicer for Category, Cost Centre and Year. Connect one slicer to several pivots via Report Connections so the whole view filters together — this is what turns a set of pivots into something a stakeholder can explore without asking you.
For charts, keep to three that answer questions:
- Pareto of supplier spend — shows concentration and where the tail starts
- Spend by month, by category — shows trend and anomalies
- Supplier count vs. spend by category — a scatter that identifies consolidation candidates instantly
Resist adding more. A dashboard's value is how quickly it produces a decision, not how much it displays.
The four analyses that actually produce savings
Most Excel spend analysis stops at "here are our top 20 suppliers", which is descriptive and rarely actionable. These four are where the money is. Each has a threshold worth applying and a next step.
1. Supplier concentration by category
Method. Use the supplier-count-by-category pivot. Plot distinct supplier count against total category spend.
Threshold. Flag any category with more than 10 suppliers and over $500K of spend. Also flag the inverse: a category above $1M with fewer than three suppliers is a supply-risk conversation, not a savings one.
Worked example. Marketing print and fulfilment: 34 suppliers, $2.1M annual spend, top supplier holding 11%. That fragmentation pattern — no supplier above 15% — usually means no one has ever run a competitive event. Consolidating to three preferred suppliers at tiered volume pricing typically recovers 6–12% on this profile.
Before acting, check contract coverage and parent-company relationships. Consolidating onto a supplier with worse terms destroys value, and two "different" suppliers may share a parent.
2. Price variance for the same item
Method. Where the description field is granular enough to identify a repeated item, pivot that item across business units and compare unit price.
Threshold. Any variance above 10% on the same item within the same 12 months is worth a conversation. Above 20%, it is a finding you can take to the business unit directly.
Worked example. Same laptop model, purchased through two entities: $1,180 in one, $1,395 in the other, 620 units combined at the higher price. The gap is $215 per unit, so aligning both to the lower price recovers roughly $133,000 — and requires no negotiation at all, only a purchasing-channel change.
This is the fastest and most defensible saving available, because nobody argues with two different prices for the same thing.
3. Non-PO and off-contract spend
Method. Filter for blank PO Number, then pivot by category and cost centre.
Threshold. Non-PO spend above 15% of total addressable spend indicates a control gap rather than a set of exceptions. Look at which cost centres concentrate it — the pattern is usually a handful of business units, not an even spread.
This is your maverick spend — purchasing made outside agreed process or contract — combined with your tail. The two overlap heavily but need different responses: tail spend needs visibility and routing, maverick spend needs policy enforcement. How to control maverick spend →
4. Payment terms inconsistency
Method. If your data carries payment terms, pivot supplier by terms and look for the same supplier appearing on different terms in different entities.
Threshold. Any supplier appearing on two or more term sets. Value it as the spend on the shorter terms multiplied by the day difference, divided by 365, multiplied by your cost of capital.
Worked example. $8M with one supplier on 30-day terms in one entity and 60-day in another. Moving the $8M to 60 days releases roughly $658,000 of working capital; at a 6% cost of capital that is about $39,000 a year, recurring, for a contract amendment.
One Suplari customer identified $6M in annual savings through payment terms optimization across their supplier base. The analysis that surfaces it is not complicated — it requires the data to be in one place.
How to use tabs in your spend analysis spreadsheet
A tab is a single worksheet inside your Excel workbook, shown along the bottom of the window. Spend analysis workbooks fail most often not because a formula is wrong but because everything lives on one sheet — raw data, corrections, calculations and charts mixed together — so nobody can tell what was imported and what was edited, and the whole thing breaks when new data arrives.
The fix is one tab per function, with data flowing in one direction only.
The Opportunities tab is the one that distinguishes an analysis from a report. Without an owner and a date, a finding is an observation.
How the data flows
Raw_Data → Working → Pivots → Dashboard
↑
Supplier_Map, Category_Map, FX_Rates
Working is the only tab that calculates. It references Raw_Data for facts and the mapping tabs for decisions. Corrections always go in a mapping tab, never in Working and never in Raw_Data. That single rule is what makes the workbook auditable — you can always prove which numbers came from the source system and which came from a judgement your team made.
Why Raw_Data must stay untouched
Two reasons, both practical. You can re-run the analysis against the original export and prove the result — which is what audit and finance will ask for. And when the extract is refreshed you can replace this tab wholesale without unpicking edits buried inside it.
How to refresh next cycle
This is the payoff of the structure, and the step most guides omit:
- Paste the new export into
Raw_Data, replacing the previous contents. Keep the columns identical. - Extend the
Workingtable to the new row count. IfWorkingis a formatted Excel table (Ctrl + T), formulas fill down automatically; otherwise drag them down. - Existing mappings apply themselves. Every supplier already in
Supplier_Mapis cleaned and categorised with no further work. - Filter
Workingfor unmapped rows — where the clean name still equals the raw name, orCategoryisUnclassified. This is your entire review queue. - Add only the new entries to
Supplier_MapandCategory_Map. - Refresh all pivots —
Data > Refresh All. Charts and slicers update with them. - Update
READMEwith the new extract date, and carry forward open items inOpportunities.
A first cycle that takes two to three weeks should refresh in one to two days once the mapping tabs are populated — provided nothing changed upstream. This is the strongest argument for building the structure properly, and also where the limitation shows: the refresh is faster, but it is still a manual cycle someone has to run, and it does not get faster again.
The tab that matters most
Opportunities is what distinguishes an analysis from a report. Columns: finding, category, estimated value, owner, target date, status. Without an owner and a date, a finding is an observation.
Where Excel breaks — and it is not where you think
Excel does not fail on accuracy. A careful analyst produces a defensible spend analysis in Excel, and the method above is genuinely worth doing. Excel fails on recurrence.
Excel is the right tool for a question you will ask once. It is the wrong tool for a question you will ask again.
That is the whole distinction, and it is worth being precise about why.
The work does not compound. Next quarter's export arrives with new suppliers, new spellings and new categories. Your mapping table helps, but the cleansing, the reclassification and the pivot rebuild largely repeat. Two weeks becomes two weeks every quarter — permanently. I've observed exactly this at a Fortune 500 company: two weeks of analyst time, a reconciliation against someone else's spreadsheet, a day of arguing about category labels, and thirty minutes of CPO attention. Stop slinging spreadsheets →
The classification ceiling is structural. Rule-writing plateaus under 80% accuracy because real description fields are inconsistent in ways rules cannot anticipate. AI classification reaches 90%+ and improves from corrections, because it reads context rather than matching strings.
One workbook, one source system. The moment your spend spans two ERPs, an AP system and an expense tool, the reconciliation between exports becomes the project. Excel has no opinion about whether the same supplier in two systems is the same supplier.
Nothing closes the loop. Excel identifies savings. It cannot tell you whether they were realized — whether the consolidation happened, whether the renegotiated rate reached the invoice. That gap between identified and realized savings is where procurement most often loses CFO credibility. Proving what your team actually delivered →
And the deeper problem is the cadence, not the file. The quarterly spreadsheet exists to serve a quarterly meeting. Replacing the spreadsheet without changing the cadence produces the same review, more expensively. Suppliers do not change quarterly. Contracts do not auto-renew quarterly. The reporting rhythm is the thing worth questioning.
The honest diagnostic
Four questions to decide whether Excel is still the right tool for your situation.
How long does it take you to answer a new question? Not a recurring report — a new one. If the CFO asks tomorrow what your tariff exposure is across your top three IT services suppliers, and the answer involves anyone opening Excel, you have found your constraint.
How often does the data refresh? Monthly or quarterly means you are looking at a snapshot of a moving target.
How much of the week goes to producing the same artefact for the same audience? Category managers typically spend 60–80% of their time on data work rather than category strategy. That ratio is the real cost.
Where do insights come from? Discovered by a human staring at a chart, or surfaced by a system watching spend continuously? The first model does not scale past the analyst who runs it.
If your answers are comfortable, Excel is serving you and there is no reason to change. If two or more made you wince, the constraint is no longer the analysis — it is the operating tempo around it.
What this does not mean
It does not mean waiting until your data is clean before doing anything else. That instinct is the most expensive one in procurement, and it is precisely backwards: the reason many teams are still in Excel is that they believe they must finish a data project before a platform becomes viable. Modern AI-native pipelines are built for imperfect data from day one — they resolve ambiguity in flight rather than erroring out and waiting for a human to write a rule. You can start with a partial extract and surface real opportunities in weeks. The imperfect data myth →
It also does not mean Excel disappears. It remains the right tool for modelling a specific negotiation, testing a scenario, or building a one-off business case. What changes is that it stops being the system of record for how much you spend and with whom.
From Excel to continuous spend intelligence
The step after Excel is not a bigger dashboard. Charts and graphs are not enough anymore — the bar has moved from "can you build the chart" to "can the system tell me what to do".
A procurement intelligence platform differs from an Excel workbook in four specific ways:
Suplari unifies spend across every source system without replatforming, classifies it on an AI-ready data foundation built for imperfect data, and applies procurement-specific AI agents that surface opportunities continuously — 175+ prebuilt insights generated automatically, with closed-loop tracking from insight to P&L. Value typically within 90 days.
If you are comparing options, it is worth reading our breakdown of the leading spend analysis platforms before taking any vendor meeting — including ours.
