This Excel tutorial explains how to set Excel graph dynamic data range.
You may also want to read:
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.
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
The line will stop in November.
But for some chart types, Null data is defaulted to 0, resulting in a sudden drop.
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
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.
Now you get the below graph with Nov and Dec hidden.
Whenever you type a value in column B, the chart is updated automatically.