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?
- Select N2:N13, the empty cells where the mini charts will sit.
- On the Insert tab, in the Sparklines group, choose Line (or Column).
- In the dialog, set Data Range to B2:M13 and click OK.
- 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.
| Setting | Each sparkline on its own scale | Same for all |
|---|---|---|
| Best for | Seeing the shape of each category | Comparing sizes between categories |
| Small category | Looks as volatile as a big one | Looks flat next to rent |
| Large category | Fills the cell | Fills the cell |
| Risk | Overstating small changes | Hiding 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
- Compare each sparkline's high point with the largest figure in its row.
- Add a total column and a comment cell for unusual months.
- Refresh data by pasting new months into the table, not over the formulas.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

