Lookup
Dynamic Named Range
Create dynamic ranges that automatically adjust their size as you add or remove data. Eliminates the need to manually update range references when your data changes.
Excel Formula
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)Step-by-Step Explanation
1
OFFSET creates a reference offset from a starting cell
2
A1 is the starting cell
3
0, 0 means no row/column offset
4
COUNTA(A:A) dynamically counts non-empty cells for height
5
1 is the width (one column)
6
Use in Name Manager to create a dynamic Named Range
Example
| Data (A) | Notes |
|---|---|
| Apple | Range auto-includes |
| Banana | all non-empty cells |
| Cherry | in the column |
| (new data) | auto-included! |
Result: =OFFSET(A1,0,0,COUNTA(A:A),1) — grows as you add data
Common Variations
Multi-column
=OFFSET(A1,0,0,COUNTA(A:A),5)Dynamic range spanning 5 columns
Skip header
=OFFSET(A2,0,0,COUNTA(A:A)-1,1)Exclude header row
Spill range (365)
=A2#Excel 365: reference entire spill range
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →