Excel guide · 4 min read
How to do a spend analysis in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier (as entered) | Category | Amount | Supplier | Name as entered | Use | Supplier | Spend | Share | ||
| 2 | INV-881 | Acme Ltd | Packaging | 18400 | Acme Ltd | Acme Ltd. | Acme Ltd | Acme Ltd | 35300 | 0.49536907101 | ||
| 3 | INV-882 | Brightline Logistics | Freight | 12750 | Brightline Logistics | Brightline Logistics Inc | Brightline Logistics | Brightline Logistics | 23650 | 0.33188324446 | ||
| 4 | INV-883 | Acme Ltd | Packaging | 9600 | Acme Ltd | Cobalt IT | 8400 | 0.11787819253 | ||||
| 5 | INV-884 | Cobalt IT | Software | 4200 | Cobalt IT | Evergreen Cleaning | 1450 | 0.02034802133 | ||||
| 6 | INV-885 | Acme Ltd. | Packaging | 7300 | Acme Ltd | Gala Catering | 980 | 0.013752455796 | ||||
| 7 | INV-886 | Delta Office | Office | 860 | Delta Office | Delta Office | 860 | 0.012068481617 | ||||
| 8 | INV-887 | Brightline Logistics Inc | Freight | 10900 | Brightline Logistics | Fenwick Print | 620 | 0.0087005332585 | ||||
| 9 | INV-888 | Evergreen Cleaning | Facilities | 1450 | Evergreen Cleaning | |||||||
| 10 | INV-889 | Fenwick Print | Marketing | 620 | Fenwick Print | |||||||
| 11 | INV-890 | Gala Catering | Events | 980 | Gala Catering | |||||||
| 12 | INV-891 | Cobalt IT | Software | 4200 | Cobalt IT |
Download this sheet as an Excel practice file (.xlsx)
Clean supplier names before you total anything
The same supplier rarely appears the same way twice. In the sample, Acme is entered as "Acme Ltd", "Acme Ltd" with two trailing spaces, and "Acme Ltd.", and Brightline appears with and without "Inc". SUMIFS matches exact text (ignoring upper and lower case only), so each variant becomes its own supplier and the biggest suppliers look smaller than they are.
Two steps fix it. TRIM removes leading, trailing and doubled spaces. A small mapping table (G:H) translates the variants TRIM can't fix to one clean name. The clean name in E2: =XLOOKUP(TRIM(B2),$G$2:$G$3,$H$2:$H$3,TRIM(B2)). If the trimmed name is in the map, use the mapped name; otherwise keep the trimmed name. The fourth argument of XLOOKUP needs Excel 365 or 2021; in older versions wrap a VLOOKUP in IFERROR.
TRIM won't remove non-breaking spaces from web or PDF exports (CHAR(160)); if a name still won't match, see remove spaces in Excel.
Result: 18400
18400: Acme's spend if you total the raw names. After cleaning, Acme's real spend is 35,300.
Total spend per supplier and per category
With clean names in E, spend per supplier is =SUMIFS($D$2:$D$12,$E$2:$E$12,J2), where J holds each supplier once. In Excel 365, =SORTBY(UNIQUE(E2:E12),SUMIFS(D2:D12,E2:E12,UNIQUE(E2:E12)),-1) builds that supplier list already sorted by spend. The same formula on column C ranks categories: Packaging 35,300, Freight 23,650 and Software 8,400 lead.
A PivotTable does this without formulas: select the data, Insert > PivotTable, drag Supplier (the clean column) to Rows and Amount to Values, then right-click a value and choose Sort > Sort Largest to Smallest.
Result: spills 7 rows × 1 column
| Acme Ltd |
| Brightline Logistics |
| Cobalt IT |
| Evergreen Cleaning |
| Gala Catering |
| Delta Office |
| Fenwick Print |
Seven suppliers, largest first: Acme Ltd, Brightline Logistics, Cobalt IT, Evergreen Cleaning, Gala Catering, Delta Office, Fenwick Print. The raw names had 10 variants.
Share of spend and cumulative %
With the summary sorted largest first (J:K), each supplier's share in L2 is =K2/SUM($K$2:$K$8). The cumulative % in M2 is =SUM($K$2:K2)/SUM($K$2:$K$8): the first reference is locked and the second moves as you fill down, so each row adds up everything above it.
The cumulative column only means something if the list is sorted by spend. Re-sort after every refresh, or let SORTBY build the list so it can't go stale. In a PivotTable, Show Values As > % of Grand Total gives the share and % Running Total In gives the cumulative %.
Result: 0.82725231546 shows as 82.7% with Percent format
0.8273: the top 2 suppliers, Acme (49.5%) and Brightline (33.2%), make up 82.7% of all spend.
Find the 80% and the tail
The 80/20 rule of thumb says a few suppliers carry most of the spend. Count how many it takes to pass 80%: =COUNTIFS(M2:M8,"<=0.8")+1 returns 2 here. Those are the suppliers worth negotiating with, holding contracts with and watching for risk.
The other end is tail spend: many suppliers, little money each, but a lot of invoices and admin. Pick a threshold in an input cell (1,000 here) and count them with =COUNTIFS(K2:K8,"<1000"): 3 suppliers, 2,460 between them, about 3.5% of spend. Tail suppliers are candidates for a preferred supplier, a purchasing card or a catalogue, not for a negotiation. For the suppliers you keep, a consistent purchase order template makes next year's spend data cleaner from the start.
Result: 3
3: Gala Catering, Delta Office and Fenwick Print each have under 1,000 of spend, the tail.
Keep the analysis honest
Use one source and one period, such as a full year of AP invoices, and say which: PO lines show commitments, invoices show what you were billed. Include credit notes so refunds reduce spend, and decide whether tax is in or out before you compare suppliers.
Check the total against the ledger before sharing: =SUM(D2:D12) should match the AP total for the period (71,260 here). If the cleaned summary totals less, a name is missing from J; if more, a supplier appears twice under two clean names.
Result: 71260
71260: total spend across all 11 invoices, which the supplier summary in K2:K8 must also add up to.
Common mistakes
Totalling raw supplier names
TRIM the names and map variants to one clean name before SUMIFS or a PivotTable.
Cumulative % on an unsorted list
Sort by spend, largest first, before adding the running SUM, or build the list with SORTBY.
Mixing PO commitments and invoices
Pick one source for the period and say which you used.
Not reconciling to the ledger
Check that the summary total equals the AP total for the same period.
FAQ
How do I do a spend analysis in Excel?
Export invoice lines, clean supplier names, total spend per supplier and category with SUMIFS or a PivotTable, then sort by spend and add share and cumulative % columns to see concentration and tail spend.
How do I calculate cumulative percentage of spend in Excel?
Sort suppliers by spend, largest first, then use =SUM($K$2:K2)/SUM($K$2:$K$8) and fill down. The locked first reference makes the range grow one row at a time.
What is tail spend?
The many suppliers that each receive a small share of total spend. Define it with a threshold, such as under 1,000 a year or the suppliers beyond the top 80% of spend, and count them with COUNTIFS.
How do I combine duplicate supplier names in Excel?
Create a clean name column with TRIM and a mapping table looked up with XLOOKUP, then total by the clean column instead of the raw one.
Functions used
SUMIFSAdds with several conditionsTRIMRemoves extra spacesXLOOKUPModern lookup in any directionSORTBYSorts by other columns, spillsUNIQUERemoves duplicates, spillsSUMAdds numbersCOUNTIFSCounts with several conditions
Related guides
- How to make a Pareto chart in Excel
- ABC inventory analysis in Excel
- How to calculate percentage of total in Excel
- How to calculate purchase price variance (PPV) in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- Excel assessment questions for employers (with answer key)
Updated