When you create a chart in Excel and the source data changes, you need to update the chart’s data source to make sure it reflects the new data.

In case you work with charts that are frequently updated, it’s better to create a dynamic chart range.

## What is a Dynamic Chart Range?

A dynamic chart range is a data range that updates automatically when you change the data source.

This dynamic range is then used as the source data in a chart. As the data changes, the dynamic range updates instantly which leads to an update in the chart.

Below is an example of a chart that uses a dynamic chart range.

Note that the chart updates with the new data points for May and June as soon as the data in entered.

## How to Create a Dynamic Chart Range in Excel?

There are two ways to create a dynamic chart range in Excel:

- Using Excel Table
- Using Formulas

In most of the cases, using Excel Table is the best way to create dynamic ranges in Excel.

Let’s see how each of these methods work.

**Click here to download the example file**.

### Using Excel Table

Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it.

Excel Table feature was introduced in Excel 2007 version of Windows and if you’re versions prior to it, you won’t be able to use it (see the next section on creating dynamic chart range using formulas).

**Pro Tip:**To convert a range of cells to an Excel Table, select the cells and use the keyboard shortcut – Control + T (hold the Control key and press the T key).

In the example below, you can see that as soon as I add new data, the Excel Table expands to include this data as a part of the table (note that the border and formatting expand to include it in the table).

Now, we need to use this Excel table while creating the charts.

Here are the exact steps to create a dynamic line chart using the Excel table:

- Select the entire Excel table.
- Go to the Insert tab.
- In the Charts Group, select ‘Line with Markers’ chart.

That’s it!

The above steps would insert a line chart which would automatically update when you add more data to the Excel table.

Note that while adding new data automatically updates the chart, deleting data would not completely remove the data points. For example, if you remove 2 data points, the chart will show some empty space on the right. To correct this, drag the blue mark at the bottom right of the Excel table to remove the deleted data points from the table (as shown below).

While I have taken the example of a line chart, you can also create other chart types such as column/bar charts using this technique.

### Using Excel Formulas

As I mentioned, using Excel table is the best way to create dynamic chart ranges.

However, if you can’t use Excel table for some reason (possibly if you are using Excel 2003), there is another (slightly complicated) way to create dynamic chart ranges using Excel formulas and named ranges.

Suppose you have the data set as shown below:

To create a dynamic chart range from this data, we need to:

- Create two dynamic named ranges using the OFFSET formula (one each for ‘Values’ and ‘Months’ column). Adding/deleting a data point would automatically update these named ranges.
- Insert a chart that uses the named ranges as a data source.

Let me explain each step in detail now.

#### Step 1 – Creating Dynamic Named Ranges

Below are the steps to create dynamic named ranges:

- Go to the ‘Formulas’ Tab.
- Click on ‘Name Manager’.
- In the Name Manager dialog box, specify the name as
**ChartValues**and enter the following formula in Refers to part: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”)) - Click OK.
- In the Name Manager dialog box, click on New.
- In the Name Manager dialog box, specify the name as
**ChartMonths**and enter the following formula in Refers to part: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”)) - Click Ok.
- Click Close.

The above steps have created two named ranges in the Workbook – ChartValue and ChartMonth (these refer to the values and months range in the data set respectively).

If you go and update the value column by adding one more data point, the ChartValue named range would now automatically update to show the additional data point in it.

The magic is done by the OFFSET function here.

In the ‘ChartValue’ named range formula, we have specified B2 as the reference point. OFFSET formula starts there and extends to cover all the filled cells in the column.

The Same logic works in the ChartMonth named range formula as well.

#### Step 2 – Create a Chart Using these Named Ranges

Now all you need to do is insert a chart that will use the named ranges as the data source.

Here are the steps to insert a chart and use dynamic chart ranges:

- Go to the Insert tab.
- Click on ‘Insert Line or Area Chart’ and insert the ‘Line with markers’ chart. This will insert the chart in the worksheet.
- With the chart selected, go to the Design tab.
- Click on Select Data.
- In the ‘Select Data Source’ dialog box, click on the Add button in ‘Legend Entries (Series)’.
- In the Series value field, enter =Formula!ChartValues (note that you need to specify the worksheet name before the named range for this to work).
- Click OK.
- Click on the Edit button in the ‘Horizontal (Category) Axis Labels’.
- In the ‘Axis Labels’ dialog box, enter =Formula!ChartMonths
- Click Ok.

That’s it! Now your chart is using a dynamic range and will update when you add/delete data points in the chart.

A few important things to know when using named ranges with charts:

- There should not be any blank cells in the chart data. If there is a blank, named range would not refer to the correct dataset (as the total count would lead to it referring to less number of cells).
- You need to follow the naming convention when using the sheet name in chart source. For example, if the sheet name is a single word, such as Formula, then you can use =Formula!ChartValue. But if there is more than one word, such as Formula Chart, then you need to use =’Formula Chart’!ChartValue.

**You May Also Like the Following Excel Tutorials:**

## 15 thoughts on “How to Create a Dynamic Chart Range in Excel”

Thanks for a great tip!

I haven’t tried this yet but superbly explained. I am keeping my fingers crossed it will work out as I hope it should. Keep up your good work and comprehensive explanations and “how-to” details.

Are you aware that the latest version of Excel throws an error message?

“Excel found a problem with one or more formula references in this worksheet”

This is amazing! It is exactly the solution I needed to deal with this aggravating pivot table disappearing custom chart formatting issue. Thank you!

When I have to do ‘something’ myself anyway to get rid of excess chart space like in the first example – it is not really dynamic in my mind. My hope was to have a completely self adjusting chart in every sense.

• I want to make a chart which doesn’t include the all the data of the table and its data range include the data from middle to end of the table. I have found that the dynamic feature of charts using table data doesn’t hold in this case. Is there a way to correct this problem?

When you said “In the Series value field, enter =Formula!ChartValues (note that you need to specify the worksheet name before the named range for this to work).” What would the syntax be to specify the worksheet name.

Hi,

Do you know if Excel functionalities changed since you made those “offset” formula.

I have copied exactly what you’ve done and it does not work…

I have no clue why.

Thanks

In Step 1 – Creating Dynamic Named Ranges, shouldn’t your formulas used in the ‘Refers to’ field of the dynamic range names, viz;

=OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,””))

include some value after the “”?

For example, to count the number values in column B to determine the number of rows in the range, shouldn’t the formula be:

=OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”” & 0))

Hello,

I have a table with two columns, One Date(For Example Jan 2015…….. Dec 2017),

The Second (For Example Monthly Temporature for each month)

I created a 2 dropdown menu lists (Start Date and End Date).

and I put Error Message Box for Start Date should be Prior to End Date.

I want to create ( a Dynamic Graph (Chart) link to two dropdown menu which

when you change Start Date and End Date of dropdown Menu’s, the chart Automatically Update the graph based on

new dropdown menu.

I would like to know, Is it possible with VBA or not? or It’s asking too much from VBA 😉

Thanks,

Amir

Dear sumit sir from last 2 days i didnt get any mail. I hope and pray you are good and fine.

Thank you very much for this awesome tutorial, I am impressed that this can be achieved in Excel, great!! What’s the meaning of ”” in COUNTIF formula?

Thanks for commenting Juan.. is an operator that meas ‘not equal to’. In the COUNTIF function, it is used in double quotes with a blank (“”). This means that COUNTIF will count all the cells that are not empty.

Sumit: i don´t see the blank in four formula: is it there?

Thank you very much for the great explanation Sumit

Comments are closed.