Excel graph dynamic data range

This Excel tutorial explains how to set Excel graph dynamic data range.

You may also want to read:

Excel dynamic Data Validation list

Excel Dynamic Print Area

Excel dynamic data range

Excel graph dynamic data range

The purpose of Excel graph 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 draw a graph from January to October easily by selecting A1 to B11, but if you do that, you need to select the data range every time for a new month.

When you create a chart using the below data set

Excel graph dynamic data range 05

The line will stop in November.

excel_dynamic_chart_2

But for some chart types, Null data is defaulted to 0, resulting in a sudden drop.

Excel graph dynamic data range 06

The best solution is to create a dynamic data range that allows the data to expand automatically based on column B.

Create Excel graph dynamic data range

Excel graph dynamic data range 05

To dynamically create a data range,  create a Name Range for each column A and B respectively, where last row of A depend on B.

To create a dynamic range for column A,

Click on Formulas tab > Name Manager > New

Under Refers to, type the below formula and name the Range as A

=OFFSET(Sheet1!$A$2,0,0,SUMPRODUCT(MAX((Sheet1!$B:$B<>"")*ROW(Sheet1!$B:$B)))-1,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])

 

Afterwards, create another Range for column B as below, and name the range as B.

=OFFSET(Sheet1!$B$2,0,0,SUMPRODUCT(MAX((Sheet1!$B:$B<>"")*ROW(Sheet1!$B:$B)))-1,1)

 

Create a graph with data range from A1 to B13

excel_dynamic_chart_2

 

Right click on the graph > Select Data

Edit the horizontal and vertical data range as below.

Excel graph dynamic data range 001

 

Now you get the below graph with Nov and Dec hidden.

excel_dynamic_chart_1

 

Whenever you type a value in column B, the chart is updated automatically.

Excel graph dynamic data range 002

 

Outbound References

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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

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 *