Spreadsheets

Pareto chart: which few categories make up most of your spending

Build a Pareto chart of your spending in Excel: sort categories, add a cumulative percentage line and see which few categories make up most of it.

Editorial illustration for Pareto chart: which few categories make up most of your spending

A Pareto chart shows which few categories account for most of your spending. In Excel, total each category, sort largest to smallest, add a running total and a cumulative percentage, then make a combo chart: columns for the amounts and a line on the secondary axis for the cumulative percentage. Where the line passes 80 per cent shows how many categories carry most of the money.

Total and sort your categories

Get one row per category for the period, from a pivot table or from a SUMIFS list. Sort by amount, largest first, using Data, Sort, Largest to Smallest. Excel 365 users can write =SORTBY(A2:A11, B2:B11, -1) to keep a sorted copy that updates.

Add the cumulative columns

  • Column C, running total: =SUM($B$2:B2), copied down
  • Column D, cumulative percentage: =C2/SUM($B$2:$B$11), formatted as a percentage
  • Column E, an optional target line: 0.8 typed in each row

The dollar signs lock the start of the range so the sum grows downward, and lock the total so every row divides by the same figure.

A ten-category worked example

Say one month's spending is ₹60,000 across ten categories. These numbers are made up.

CategoryAmount (₹)Running total (₹)Cumulative share
Rent18,00018,00030.0%
Groceries10,00028,00046.7%
EMI8,00036,00060.0%
Transport6,00042,00070.0%
Dining out5,00047,00078.3%
Electricity3,00050,00083.3%
Gifts3,00053,00088.3%
Mobile and internet2,50055,50092.5%
Medical2,50058,00096.7%
Subscriptions2,00060,000100.0%

The line crosses 80 per cent at the sixth category. Five categories make 47,000, which is 78.3 per cent, and six make 83.3 per cent. So in this example, six of ten categories carry about four-fifths of the money.

Draw the combo chart

  1. Select Category, Amount and Cumulative share. Hold Ctrl to pick the non-adjacent column.
  2. Choose Insert, then Combo Chart, then Create Custom Combo Chart.
  3. Set Amount to Clustered Column, and Cumulative share to Line with Markers on the Secondary Axis.
  4. Format the secondary axis with a minimum of 0 and a maximum of 1, so it ends at 100 per cent.
  5. Set the column Gap Width to around 10 per cent so the bars nearly touch, as a Pareto chart usually does.

Newer Excel versions also include a built-in Pareto chart. The manual build works in every version and puts the cumulative figures in cells you can check.

Is it really an 80/20 rule?

No. Eighty per cent and twenty per cent is a rule of thumb, not a law, and your data may split 70/30 or 90/10. The chart's value is in showing your own pattern. In the example above, rent and EMI are fixed commitments, so the top of the chart mostly tells you what you already know.

The same chart works for merchants. List your merchants by total spend, sort, add the cumulative share, and see how many shops and sellers carry most of the money. It often shows that a handful of regular places, such as the grocer, the fuel pump and the pharmacy, account for most of a month.

What to do with it

  • Open the top flexible categories and read the transactions behind them.
  • Check categories like Other. If it ranks high, your categorisation needs work.
  • Repeat for a few months to see whether the order is stable.
  • Do not cut small categories to feel productive. They rarely move the total.

Pivot totals and sorting are where this chart usually breaks, since each month needs resorting. SpendixAI has reports grouped by question that already rank spending by category, which saves the rebuild. Whichever route you use, compare at least three months before deciding anything.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports