This Excel tutorial explains how to create Excel dynamic data range using Name Range.
You may also want to read:
Excel dynamic Data Validation list
Excel graph dynamic data range
Excel dynamic data range
The purpose of Excel dynamic data range is to select data range based on how many data you have input instead of using a fixed range.
Assume that you have the data set below.
You can select A1 to B11 as the data source, but if you do that, you need to select the data range every time for a new month.
Excel dynamic data range – data range of one column
To dynamically create a data range, create a Name Range.
Assume that we want to create a dynamic range for column A.
Click on Formulas tab > Name Manager > New
Under Refers to, type
=OFFSET(Sheet1!$A$1,0,0,SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A))),1)
If you don’t understand the OFFSET function, click here to view my previous post.
But simply speaking, the syntax of OFFSET function is as below
OFFSET( reference, rows, columns, [height], [width])
Excel dynamic data range – two columns of same row
Assume that we want to include both column A and B in the dynamic data range
In Refers to, type
=OFFSET(Sheet1!$A$1,0,0,SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A))),2)
To confirm success, input the data for November and December, and then check the data range of the Name Range we just created, the data range is expanded dynamically.
Excel dynamic data range – two columns of different row
You may want to type the month up to December although you don’t have the actual value yet, but this time you want the data range to include up to column B last row.
In “Refers to”, type
=OFFSET(Sheet1!$B$1,0,-1,SUMPRODUCT(MAX((Sheet1!$B:$B<>"")*ROW(Sheet1!$B:$B))),2)
To confirm success, check the data range of the Name Range we just created, the data range is expanded according to column B.
