Dynamic Chart Range in Excel: Charts That Update Automatically

Part of the free Module 8: Basic Charts · Lesson 12 of 12 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. Spill-range names need Excel 365 or 2021.

A dynamic chart range in Excel is a chart source that grows or shrinks automatically when you add or remove rows, so the chart always plots the latest data without anyone editing it. There are two reliable ways to build one: convert the source to an Excel Table, or define named ranges with OFFSET or INDEX and point each chart series at the name. This lesson covers both, plus a rolling last-N-months chart.

Why a normal chart range goes stale

When you insert a chart from A1:B13, Excel writes fixed cell references into the series formula, for example =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1). Type a new month in row 14 and nothing happens: the chart still stops at row 13. On a monthly report that means editing every chart every month, and on a dashboard it means silent, wrong charts. A dynamic range replaces those fixed references with something that expands on its own.

Method How it expands Best for Limits
Excel Table (Ctrl + T) New rows added directly under the Table join it automatically Most reports and dashboards; zero formulas Cannot show only the last N rows; whole column is plotted
OFFSET named range A COUNTA or COUNT formula sets the height of the range Rolling windows (last 12 months), classic dashboards Volatile, recalculates on every change; slower in huge models
INDEX named range INDEX finds the last cell; the range is A2:INDEX(…) Same as OFFSET but non-volatile Slightly harder to read
Spill range name (365) Name refers to a dynamic array result with # Charts fed by FILTER, SORT or UNIQUE Excel 365 or 2021 only

Method 1: base the chart on an Excel Table

This is the method to teach first because it needs no formulas. A Table is a structured range that knows its own boundaries, and a chart built on a Table follows those boundaries.

  1. Click any cell in the data and press Ctrl + T. Confirm My table has headers and click OK.
  2. With a Table cell selected, choose Insert > Charts > Insert Line or Area Chart > Line with Markers (or any chart type).
  3. Type the next month and its value in the row immediately below the Table. The Table extends and the chart plots the new point straight away.
  4. Optional: rename the Table on Table Design > Table Name, for example tblSales, so the series formula reads clearly.

Deleting a Table row removes the point too. If the chart does not extend, check that the new row is directly under the Table with no blank row between, and that the Table has not been converted back to a range.

Method 2: OFFSET named ranges

Use this when you need a rolling window or when the source cannot be a Table. Two names are needed: one for the categories and one for the values. Sample data in A1:B13, months in column A and sales in column B, headers in row 1.

  1. Go to Formulas > Defined Names > Define Name.
  2. Name: rngMonths. Refers to: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). Click OK.
  3. Define a second name rngSales with =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). Both names use column A to count rows so they always have the same height.
  4. Insert a chart from A1:B13 as normal, then right-click it and choose Select Data.
  5. Under Legend Entries (Series) click Edit. In Series values type =Sheet1!rngSales (or =Book1.xlsx!rngSales if the name is workbook-scoped). Click OK.
  6. Under Horizontal (Category) Axis Labels click Edit and enter =Sheet1!rngMonths. Click OK twice.

The name must be prefixed with the sheet or workbook name inside the Select Data dialog; a bare name is rejected. After you click OK, Excel rewrites the series formula to =SERIES(Sheet1!$B$1,Book1.xlsx!rngMonths,Book1.xlsx!rngSales,1), which you can confirm by clicking the series and reading the formula bar.

The OFFSET arguments are: starting cell, rows down, columns across, height, width. COUNTA($A:$A)-1 counts the non-empty cells in column A and subtracts the header. Keep column A free of notes below the data, or the count will be wrong.

Method 3: INDEX for a non-volatile range

OFFSET is volatile: it recalculates whenever anything in the workbook changes. In a large model that slows everything. INDEX gives the same result without the volatility:

rngMonths:  =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
rngSales:   =Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))

INDEX returns a reference to the last filled cell, and the colon builds a range from A2 to that cell. Assign the names to the series exactly as in Method 2.

Rolling window: chart the last 12 months

A dashboard usually shows the most recent period, not the whole history. Put the window length in a cell, say D1 = 12, and offset the start of the range by the number of rows to skip:

rngMonths: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-Sheet1!$D$1,0,Sheet1!$D$1,1)
rngSales:  =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A:$A)-Sheet1!$D$1,0,Sheet1!$D$1,1)

With 30 rows of data and D1 = 12, the range starts 18 rows below the header and is 12 rows tall. Change D1 to 6 and the chart shows six months. Add a data-validation drop-down to D1 with 3, 6, 12 and 24 and you have an interactive chart with no VBA. Wrap the height in MIN(D1, COUNTA(A:A)-1) if users might request more months than exist.

Excel 365: chart a spill range

Charts cannot reference a spilled array with the # operator directly in the Select Data dialog, but a defined name can. If =FILTER(A2:B200,A2:A200>=D1) spills from F2, define rngFiltered as =Sheet1!$G$2# for the values and =Sheet1!$F$2# for the categories, then assign the names to the series. The chart follows the spill as it grows and shrinks.

Worked example: a self-extending sales chart

Month Sales
Jan-26 48,200
Feb-26 51,400
Mar-26 47,900
Apr-26 55,300
May-26 58,100
Jun-26 60,700
  1. Enter the data in A1:B7 and define rngMonths and rngSales with the INDEX formulas above.
  2. Select A1:B7 and insert a Clustered Column chart.
  3. Open Select Data, edit the series values to =Sheet1!rngSales and the axis labels to =Sheet1!rngMonths.
  4. Type Jul-26 in A8 and 63,400 in B8.

Result: a seventh column appears at once. Delete row 8 and the chart returns to six columns. Link the chart title to a cell containing ="Sales to "&TEXT(INDEX(A:A,COUNTA(A:A)),"mmm yyyy") and the title moves with the data as well.

Tips and common mistakes

  • Prefer a Table unless you need a rolling window. It is simpler, non-volatile and self-documenting.
  • Count on the category column, not the value column. Blank values in the middle of the data would shorten an OFFSET range and misalign the series.
  • Prefix names with the sheet or workbook in Select Data. =Sheet1!rngSales works; =rngSales does not.
  • Do not leave stray text below the data in the counted column. Totals or notes inflate COUNTA.
  • Test the name first. Type =SUM(rngSales) in a spare cell and check it changes when you add a row, before wiring it into the chart.
  • Use INDEX on big models. OFFSET is volatile and can make a large workbook feel sluggish.
  • One name per series. A chart with five series needs five value names plus one category name.

Errors and how to fix them

Problem Cause Fix
“That function isn’t valid” or “Reference isn’t valid” in Select Data Name not prefixed, or the name formula has an error Type =Sheet1!name; check the name in Name Manager evaluates to a range
Chart shows one extra blank point COUNTA includes the header or a stray cell Subtract 1, or point COUNTA at a clean column
Table chart does not extend New row typed with a gap, or Table converted to range Enter data directly under the last Table row; press Ctrl + T again if needed
Name works in a cell but chart stays fixed Excel replaced the name with cell references Re-enter the name in Select Data; make sure the name refers to a formula, not a static range
Rolling window returns #REF! Window length larger than the data Use MIN(D1, COUNTA(A:A)-1) for the height

Practice exercise

  1. Convert the six-month table to an Excel Table, build a line chart and add two more months to prove it extends.
  2. Define rngMonths and rngSales with OFFSET and rebuild the same chart on the names.
  3. Replace the OFFSET formulas with the INDEX versions and confirm the chart still updates.
  4. Add a cell with a window length and change the names so the chart shows only the last N months.
  5. Link the chart title to a cell that reports the last month in the range.

Key takeaways

  • A chart built on an Excel Table extends automatically with no formulas.
  • OFFSET or INDEX named ranges give full control, including rolling last-N-month windows.
  • In Select Data, always write the name as =Sheet1!name.
  • Count rows on the category column and keep it free of notes and totals.
  • INDEX is non-volatile; use it instead of OFFSET in large workbooks.
  • In Excel 365 a name that refers to a spill range lets charts follow FILTER and SORT results.

Related lessons

Frequently asked questions

How do I make an Excel chart update automatically when I add data?

Convert the source range to an Excel Table with Ctrl + T and build the chart from the Table. Any row typed directly beneath the Table becomes part of it, and the chart plots the new point immediately. If you need only the most recent rows, use OFFSET or INDEX named ranges instead.

What is the OFFSET formula for a dynamic chart range?

A typical formula is =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). It starts at B2, moves zero rows and columns, and sets the height to the number of filled cells in column A minus the header. Define it as a name and enter =Sheet1!name as the series values in Select Data.

Why does Excel say the reference is not valid when I use a named range in a chart?

In the Select Data dialog the name must be qualified with the sheet or workbook name, for example =Sheet1!rngSales. A bare name is rejected. Also open Name Manager and confirm the name evaluates to a real range; a formula that returns an error or a value instead of a reference will fail.

Can I chart only the last 12 months automatically?

Yes. Put 12 in a cell and define the range as =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A:$A)-12,0,12,1), replacing 12 with the cell reference. The range starts twelve rows before the last entry and is twelve rows tall, so it slides forward each month as new data is added.

Is OFFSET or INDEX better for dynamic ranges?

Both produce the same range. OFFSET is easier to read but volatile, so it recalculates on every change and can slow large workbooks. INDEX, written as $A$2:INDEX($A:$A,COUNTA($A:$A)), is non-volatile and is the better choice in big models. For most small reports the difference is not noticeable.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousScatter Chart in Excel: Plot Two Variables and Add a TrendlineAll lessonsBasic Charts