Excel dynamic data range

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 Dynamic Print Area

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.

Excel graph dynamic data range 02

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)

Excel graph dynamic data range 03

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 graph dynamic data range 04

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.

Excel graph dynamic data range 05

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.

excel dynamic data range 01

 

Outbound References

https://support.microsoft.com/en-us/kb/324991

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *