Spreadsheets

Sparklines for each spending category in Excel

Add a line sparkline per spending category across twelve months in Excel, with high and low markers and a shared axis so small and large categories compare fairly.

Editorial illustration for Sparklines for each spending category in Excel

Put categories down column A and twelve months across B to M, select the empty cells in column N, choose Insert, Sparklines, Line, and give B2:M13 as the data range. Each row gets a tiny line chart. Turn on High Point and Low Point markers, and set every sparkline to the same vertical axis if you want them compared fairly.

How do I lay out the table?

Use the financial year, April to March, across the top. Put the first day of each month in row 1 (01-Apr-2025, 01-May-2025 and so on) with the format mmm. Fill the cells below with SUMIFS from your transaction sheet:

  • =SUMIFS(Txn!$C$2:$C$2000, Txn!$B$2:$B$2000, $A2, Txn!$A$2:$A$2000, ">="&B$1, Txn!$A$2:$A$2000, "<"&EDATE(B$1,1))
  • Here Txn column A holds dates, column B the category and column C the amount.
  • The $ signs keep the category column and the date row fixed while you fill right and down.

How do I add the sparklines?

  1. Select N2:N13, the empty cells where the mini charts will sit.
  2. On the Insert tab, in the Sparklines group, choose Line (or Column).
  3. In the dialog, set Data Range to B2:M13 and click OK.
  4. Make the row height larger and the column wider, so each mini chart is easy to see.

Because you selected all the location cells together, Excel treats them as a group, and formatting one changes them all.

How do I mark the highest and lowest month?

With a sparkline cell selected, open the Sparkline tab that appears and tick High Point and Low Point under Show. Use Marker Color to give each its own colour, for instance red for the high and green for the low. A marker answers the first question people ask, which is which month was the peak, without any numbers.

Why should all sparklines share the same axis?

By default each sparkline scales to its own range. A category that moves between ₹800 and ₹1,200 then looks as dramatic as one that moves between ₹8,000 and ₹22,000. To compare fairly, open Axis in the Sparkline tab, and for both Vertical Axis Minimum Value Options and Maximum Value Options choose Same for All Sparklines.

SettingEach sparkline on its own scaleSame for all
Best forSeeing the shape of each categoryComparing sizes between categories
Small categoryLooks as volatile as a big oneLooks flat next to rent
Large categoryFills the cellFills the cell
RiskOverstating small changesHiding the pattern of small categories

Neither is wrong. A fair approach is to use the individual scale for the shape and add a column with the yearly total so readers also see the size.

What about blank months and zeros?

  • Use Hidden and Empty Cells, Show empty cells as, to choose gaps or zero.
  • A category with no spending in a month is better shown as zero than as a gap if the money really was zero.
  • Make sure formulas return 0, not text, for months that have not happened yet. Leave future months blank rather than zero.

Is there an equivalent in Google Sheets?

Sheets has a SPARKLINE function. For a row of numbers in B2:M2, =SPARKLINE(B2:M2) draws a line chart in one cell, and an options array lets you change chart type and colours. It is a formula, so you copy it down yourself.

A final check

  1. Compare each sparkline's high point with the largest figure in its row.
  2. Add a total column and a comment cell for unusual months.
  3. Refresh data by pasting new months into the table, not over the formulas.
SPENDIXAI JOURNAL

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

Explore your reports