Make a two-column monthly summary (month start date, total spend), select it, and choose Insert, Line with Markers. Then set the horizontal axis to a date axis with months as the base unit so gaps and spacing are right. A line chart shows direction over time, and the markers help you read each month.
How do I build the monthly summary?
Put the first day of each month in column A, for example 01-Apr-2025 down to 01-Mar-2026 for an April to March year, and use the transaction table for the totals. If transactions sit on a sheet named Txn with dates in column A and amounts in column C, B2 is:
- =SUMIFS(Txn!$C$2:$C$2000, Txn!$A$2:$A$2000, ">="&A2, Txn!$A$2:$A$2000, "<"&EDATE(A2,1))
- Format column A with mmm-yy so it reads Apr-25.
- The first condition takes dates on or after the month start; the second takes dates before the next month start. EDATE adds one month.
How do I make the chart?
- Select A1:B13 (headers plus twelve months).
- Insert, Charts, Line, Line with Markers.
- Click the horizontal axis, open Format Axis, and under Axis Type pick Date axis, with Base unit set to Months.
- Add a clear chart title such as Monthly spend, ₹ (April to March).
- Delete the legend for a single series and keep light gridlines.
Why does the date axis matter?
If Excel treats your months as text, it spaces them evenly, which is fine. If it treats them as dates but chooses days as the base unit, a month with missing data can create strange spacing or a flat line. Setting the base unit to Months makes each point a month, whatever the number of days.
What does a trend actually tell me?
| Month | Spend (₹) |
|---|---|
| Apr | 48,000 |
| May | 52,000 |
| Jun | 47,500 |
| Jul | 51,000 |
| Aug | 49,500 |
| Sep | 63,000 |
These are hypothetical numbers. The first five months average (48,000 + 52,000 + 47,500 + 51,000 + 49,500) / 5 = ₹49,600. September is ₹13,400 higher. If a ₹12,000 one-off bill landed that month, most of the jump is explained and the line does not mean your daily spending rose. A trend is the pattern you see once one-off spikes are understood, not just the last point.
Can I compare this year with last year on the same chart?
Yes. Set up twelve rows from April to March with the month name in column A (type Apr, May and so on, or use =TEXT(date,"mmm")), this year's totals in column B and last year's in column C. Select all three columns and insert a Line with Markers chart. Both lines share the same twelve positions, so a gap between them shows the change for that month. Because the months are text labels, the date axis setting is not needed for this layout.
What should I watch out for?
- A part month at the end of the data makes the line fall; either leave it out or label it.
- Festival months, annual premiums and school fees create regular peaks. Compare the same month in two years.
- Different month lengths matter little for monthly totals, but February is shorter, so a small dip is normal.
- A month with no data yet plots as zero and the line plunges. Leave future cells empty, not zero, and set Hidden and Empty Cells to show gaps.
- A chart that starts at 40,000 on the vertical axis makes small changes look huge. Start at zero unless you state otherwise.
A short routine
- Update the transaction table after each statement.
- Check the new month's total against the statement.
- Extend the summary by one row, and check that the chart range grew with it. Converting the summary to a table (Ctrl+T) does this for you.
- Note any one-off in a cell beside the chart, so next year's you remembers what happened.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

