ChartsIntermediate7 min read

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

  1. Select your data range, then Insert → Table (or Ctrl+T)
  2. Build your chart from the Table as normal (Insert → Chart)
  3. Add new rows directly below the Table's last row — Excel extends the Table automatically, and any chart built from it extends too
This is the right approach for almost everyone. It requires no formulas and works the moment you start typing in the row right under the table.

Try it yourself

Edit the numbers — the chart updates itself

Jan
Feb
Mar
Apr
May
Jun

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.

Tables handle the common case cleanly; reach for OFFSET/INDEX named ranges only when you need a moving window or a non-contiguous source a Table can't express.

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