Spreadsheets

A chart that always shows the last 12 months

Feed an Excel chart from a rolling 12-row summary block so the axis rolls forward by itself, with no series editing each month. Setup and fixes included.

Editorial illustration for A chart that always shows the last 12 months

To make a chart that always shows the last 12 months, do not edit the chart each month; make the data behind it roll instead. Build a 12-row summary block whose first month is calculated from TODAY, point a normal column or line chart at that block once, and the axis moves forward by itself when the month changes.

How do I build the summary block?

  1. In a sheet called Chart, type Month in A1 and Spent in B1.
  2. In A2 enter =EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-12) for the first month, which is the first day of the month twelve months back.
  3. In A3 enter =EDATE(A2,1) and fill down to A13. These are the twelve full months ending last month.
  4. In B2 enter =SUMIFS(Data!$C$2:$C$800,Data!$A$2:$A$800,">="&$A2,Data!$A$2:$A$800,"<"&EDATE($A2,1)) and fill down to B13. Data holds your dates in column A and amounts in column C.
  5. Format A2:A13 as mmm yy and B2:B13 as numbers with no decimals.

How do I point the chart at it?

Select A1:B13, then Insert, and choose a clustered column chart or a line chart. Because the range is a fixed twelve rows and the values inside it change, the chart never needs its series edited. On 1 December the first row quietly becomes December 2025 and the chart redraws.

Excel may decide the month column is a date axis and space the points by days. Right-click the horizontal axis, choose Format Axis, and set the axis type to Text axis if the columns look uneven or leave gaps.

What does the block look like with real numbers?

Say today is 12 November 2026 and the made-up totals are as below. The chart would show these twelve columns, and the budget line is a second series drawn from column C, where every cell equals one cell holding your monthly budget.

MonthSpent (₹)Budget line (₹)
Nov 2536,50042,000
Feb 2633,80042,000
May 2640,20042,000
Aug 2643,90042,000
Oct 2651,70042,000

The table shows five of the twelve rows to keep it short. In October the column rises about ₹9,700 above the budget line, which is the kind of gap a rolling chart makes visible without you hunting for it.

How can I show amounts in lakhs on the axis?

If your Windows region is set to English (India), Excel may already group digits the Indian way. If not, right-click the vertical axis, choose Format Axis, then Number, then Custom, and paste this format: [>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Do I need a dynamic named range instead?

You may see advice to build OFFSET-based named ranges that grow or slide. Those exist for data that keeps adding rows. Here the summary block is always exactly twelve rows, so the simpler setup is also the easier one to debug.

What if the chart looks wrong?

  • All columns the same height: calculation may be set to manual. Check Formulas, Calculation Options and set it to Automatic.
  • Gaps or zeros: the dates in Data are probably text. =ISNUMBER(Data!A2) should show TRUE.
  • The current month is missing: that is intended, because the block ends at last month. Move the first month to -11 in A2 if you prefer to include the running month, and expect that last bar to be partly filled.
  • A category chart: make a second block with one SUMIFS per category across the same twelve months and chart the categories you care about.

If you later want to compare two chart windows, say the last twelve months with the twelve before them, copy the block, change the first month to -24 in the EDATE, and chart both blocks together as two series.

Once the block works, bring in a new month's spending by pasting rows into Data and the chart takes care of itself. If you would rather skip the pasting, SpendixAI can import statements and keep the categorised transactions in its own reports.

SPENDIXAI JOURNAL

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

Explore your reports