Create a Dynamic Named Range

Excel Formulas › Lookup

All versionsOFFSETCOUNTA

A dynamic named range automatically grows and shrinks as you add or remove data — so charts, drop-downs, and SUMs never need their ranges updated. The classic build uses OFFSET + COUNTA in the Name Manager.

Quick formula: in Name Manager, define a name (e.g. SalesData) that refers to:
=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1)
COUNTA measures how many values exist; OFFSET sizes the range to exactly that many rows, starting at B2.

Functions used (tap for the full reference guide):

OFFSETCOUNTAINDEX

How it works

The named range covers only the filled cells — add a row and it expands.

AB
1MonthSales (named: SalesData)
2Jan$100
3Feb$140
4Mar$120
5(add Apr → range auto-grows)

The formula

The Name Manager definition:

=OFFSET($B$2, 0, 0, COUNTA($B$2:$B$1000), 1) // sizes to the count of values

How it works

COUNTA does the measuring, OFFSET does the sizing:

  1. COUNTA($B$2:$B$1000) counts how many non-blank cells are in the column — the current data length.
  2. OFFSET($B$2, 0, 0, thatCount, 1) returns a range starting at B2 that is exactly that many rows tall and 1 column wide.
  3. Save it as a name (e.g. SalesData). Now =SUM(SalesData), a chart, or a drop-down using it always covers just the filled cells.
  4. Add a row and COUNTA grows, so the named range grows with it — no manual range edits ever again.

Two cleaner modern alternatives: an Excel Table (Ctrl+T) auto-expands its structured references with zero formulas; and in Excel 365 a spill formula referenced with # resizes itself. Use OFFSET when you need a classic named range without a Table.

Try it: interactive demo

Live demo

Add or remove values; the named range (and its SUM) resizes.

SalesData covers rows · SUM =

Variations

Non-volatile with INDEX

Avoid OFFSET’s volatility:

=$B$2:INDEX($B:$B, COUNTA($B:$B))

Excel Table (no formula)

Ctrl+T, then reference the column — it auto-expands.

365 spill range

A dynamic-array result referenced with =Sheet!$B$2#.

Pitfalls & errors

Blanks in the middle undercount. COUNTA counts filled cells, so a gap makes the range too short. Keep the column contiguous, or count a key column with no gaps.

OFFSET is volatile. Many dynamic ranges can slow a workbook. The INDEX form or a Table is faster.

Headers. Start the range below the header and count from there, or COUNTA includes the header in the size.

Practice workbook

📊
Download the free Create a Dynamic Named Range practice workbook
The OFFSET/COUNTA dynamic-range setup with a live SUM over it, the INDEX and Table alternatives, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I create a dynamic named range in Excel?
In Name Manager, define a name referring to =OFFSET($B$2,0,0,COUNTA($B$2:$B$1000),1). COUNTA measures the data length and OFFSET sizes the range, so it grows as you add rows.
Is there a non-volatile dynamic range?
Yes, use INDEX: =$B$2:INDEX($B:$B, COUNTA($B:$B)). It resizes like OFFSET but isn't volatile, so it's faster in large workbooks.
What's the easiest way to get an auto-expanding range?
Format the data as an Excel Table (Ctrl+T). Its structured references expand automatically with no formulas, and charts and drop-downs based on it follow.

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: Sum the last N rows (OFFSET) · Reference a spill range with # · Create a drop-down list

Function references: OFFSET · COUNTA · INDEX