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.


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



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.



Create a graph with data range from A1 to B13



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.



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

Excel graph dynamic data range 002


Outbound References



Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, 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

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

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