Charts That Update Themselves When New Data Arrives
Stop manually dragging a chart's source range wider every month. Excel Tables and dynamic named ranges make charts grow with your data automatically.
A chart built on a fixed range like A2:A13 stops including new data the moment you add row 14. The fix isn't to remember to drag the range wider every month — it's to base the chart on a source that grows by itself.
The simple fix: use an Excel Table
- Select your data range, then Insert → Table (or Ctrl+T)
- Build your chart from the Table as normal (Insert → Chart)
- Add new rows directly below the Table's last row — Excel extends the Table automatically, and any chart built from it extends too
Try it yourself
Edit the numbers — the chart updates itself
When you need a dynamic named range instead
Occasionally a chart needs to source from a range that isn't a clean, single contiguous table — for example, pulling only the last 12 months from a longer running log. A dynamic named range built with OFFSET or INDEX solves this:
=OFFSET(Sheet1!$B$2, 0, 0, COUNT(Sheet1!$B:$B), 1)Always spans from B2 to the last numeric value in column B — grows or shrinks with the data.
=INDEX(Sheet1!$B:$B, 2, 1):INDEX(Sheet1!$B:$B, COUNT(Sheet1!$B:$B)+1, 1)An INDEX-based alternative — avoids OFFSET's reputation for slowing down very large, heavily-referenced workbooks.
Define this as a named range (Formulas → Define Name), then set that name as the chart's source series instead of a fixed cell range (Chart Design → Select Data → Edit → type the name into the Series values box).
Rolling last-12-months window
Combine the dynamic range idea with a fixed window size when you specifically want "the most recent 12" rather than "everything":
=OFFSET(Sheet1!$B$2, COUNT(Sheet1!$B:$B)-12, 0, 12, 1)Always the last 12 data points, regardless of how many rows exist in total.
Want the full worked example?
Automate Your Reports: Power Query for Beginners
A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.
Get the eBook — ₹199