# 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. 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

`=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 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. ## Outbound References

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