Auto-Expanding Chart Source

All versionsTable / OFFSET

Add a new row of data and have the chart grow with it — automatically. The easy way is an Excel Table; the formula way is a dynamic named range built with OFFSET and COUNTA.

Quick formula: define a name (Formulas → Name Manager) that sizes itself:

=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1)

COUNTA counts the filled cells; OFFSET returns exactly that many rows. Point the chart series at the name.

Functions used (tap for the full reference guide):

OFFSET COUNTA

The example

A named range that grows as rows are added.

	A
1	Defined name: SalesData
2	=OFFSET($B$2,0,0,COUNTA($B$2:$B$1000),1)

The formula

The self-sizing named range:

SalesData = OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1) // grows with the data

How it works

Two ways — Table (simplest) or OFFSET name:

Easiest: select your data and press Ctrl+T to make a Table. Charts built on a Table extend automatically when you add rows.
Formula way: in Name Manager, define a name as =OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1).
COUNTA counts the non-empty cells; OFFSET returns a range of exactly that height.
In the chart, edit the series values to use the name (e.g. =Sheet1!SalesData). New rows now appear automatically.

Prefer Tables. Unless you need OFFSET for an unusual layout, a Table is simpler, non-volatile, and also gives you structured references, filters, and banding for free. Reserve the OFFSET name for when a Table won’t fit the layout.

Try it: interactive demo

Live demo

Add values; the “range” auto-sizes.

Range height:

Variations

Excel Table (best)

Select data → Ctrl+T; charts extend automatically.

Two-column range

Widen the OFFSET:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),2)

Ignore trailing blanks

COUNTA stops at the gap if data is contiguous.

Pitfalls & errors

OFFSET is volatile. It recalculates often; on huge models a Table is leaner. COUNTA also miscounts if there are blank cells within the data.

Reference the name in the series. The chart series must use the workbook-qualified name (=Sheet1!SalesData), not the raw range, for auto-expansion to work.

Blanks break COUNTA. Gaps in the column undercount the height; keep the data contiguous.

Practice workbook

📊

Download the free Auto-Expanding Chart Source practice workbook

A chart fed by an OFFSET/COUNTA dynamic name and a Table example, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make an Excel chart expand automatically with new data?

Easiest: turn the data into a Table (Ctrl+T) and build the chart on it. Formula way: define a dynamic name =OFFSET($B$2,0,0,COUNTA($B$2:$B$1000),1) and point the chart series at the name.

Should I use a Table or an OFFSET name?

Prefer a Table — it's simpler, non-volatile, and adds filters and structured references. Use the OFFSET name only when a Table doesn't fit your layout.

Why doesn't my dynamic range update the chart?

The chart series must reference the workbook-qualified name (=Sheet1!Name), and the data must be contiguous so COUNTA counts the right number of rows.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Dynamic named range · OFFSET dynamic range · Dynamic chart title

Function references: OFFSET · COUNTA