Cellwise Start free

Excel guide · 4 min read

How to do a spend analysis in Excel

Quick answerExport invoice or PO lines, clean the supplier names (TRIM plus a mapping table for variants), then total spend per supplier with SUMIFS or a PivotTable. Sort suppliers from largest to smallest, add each one's share of total and a running cumulative %, and you can see which few suppliers make up most of the spend and how many sit in the long tail.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1InvoiceSupplier (as entered)CategoryAmountSupplierName as enteredUseSupplierSpendShare
2INV-881Acme LtdPackaging18400Acme LtdAcme Ltd.Acme LtdAcme Ltd353000.49536907101
3INV-882Brightline LogisticsFreight12750Brightline LogisticsBrightline Logistics IncBrightline LogisticsBrightline Logistics236500.33188324446
4INV-883Acme Ltd Packaging9600Acme LtdCobalt IT84000.11787819253
5INV-884Cobalt ITSoftware4200Cobalt ITEvergreen Cleaning14500.02034802133
6INV-885Acme Ltd.Packaging7300Acme LtdGala Catering9800.013752455796
7INV-886Delta OfficeOffice860Delta OfficeDelta Office8600.012068481617
8INV-887Brightline Logistics IncFreight10900Brightline LogisticsFenwick Print6200.0087005332585
9INV-888Evergreen CleaningFacilities1450Evergreen Cleaning
10INV-889Fenwick PrintMarketing620Fenwick Print
11INV-890Gala CateringEvents980Gala Catering
12INV-891Cobalt ITSoftware4200Cobalt 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.

=SUMIFS(D2:D12,B2:B12,"Acme Ltd")

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.

=SORTBY(UNIQUE(E2:E12),SUMIFS(D2:D12,E2:E12,UNIQUE(E2:E12)),-1)

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 %.

=SUM(K2:K3)/SUM(K2:K8)

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.

=COUNTIFS(K2:K8,"<1000")

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.

=SUM(D2:D12)

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

Related guides

Updated