Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Dynamic Chart Range in Excel

I have a strong reason for you to use a dynamic chart range.

It happens sometimes that you create a chart and at the time when you update it, you have to change its range manually.

Even when you delete some data, you have to change its range.

Maybe it looks like that changing a chart range is no big deal. But what, when you have to update data frequently?

You do need a dynamic chart range.

Are you sure, I need a Dynamic Chart Range?

Yes, 100%.

Alright, let me show you something.

Below, you have a chart with month wise amount and when you add amount for Jun, chart values are same, there is no change.

how to create a dynamic chart range in excel monthly amount chart

Now the thing is, you have to update chart range manually to include Jun in the chart.

So what do you think, using a dynamic chart range is a time-saver?

Using Data Table for Dynamic Chart Range

If you are using 2007 version of excel or above then using a data table instead of a normal range is the best way.

All you have to do, convert your normal range into a table (use shortcut key  Ctrl + T ) & then use that table to create a chart.

Now, whenever you add data to your table it will automatically update the chart as well.

how to create a dynamic chart range in excel using data table update data

In above chart, when I have added the amount for Jun, chart get updated automatically.

The only one thing that leads you to use next method is when you delete data from a table, your chart will not get updated.

how to create a dynamic chart range in excel data table delete data

Solution to this problem is when you want to remove data from chart just delete that cell by using delete option.

Using Dynamic Named Range

Using a dynamic named range for a chart is bit tricky but it’s a one-time setup.

Once you do that, it’s super easy to manage it.

So, I have split the entire process into two steps.

  1. Creating a dynamic named range.
  2. Changing source data for the chart to dynamic named range.

Creating a dynamic named range for dynamic chart

To create a dynamic named range we can use OFFSET Function.

Quick Intro To Offset: It can return a range’s reference which is a specified number of rows and columns from a cell or range of cells.

We have following data to create a named range.

how to create a dynamic chart range in excel with dynamic named range

In column A we have months and amount in column B.

And, we have to create dynamic named ranges for both of the columns so that when you update data your chart will update automatically.

Download this file to follow along.

Here are the steps.

  • Go to Formulas Tab -> Defined Names -> Name Manager.

how to create a dynamic chart range in excel click name manager

  • Click on “New” to create a named range.

how to create a dynamic chart range in excel click new

  • Now, in the new name window, enter the following formula (I will tell you further how it work).
    • =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1,1)
  • Name your range “amount”.

how to create a dynamic chart range in excel enter formula

  • Click OK.
  • Now, create another named range by using following formula.
    • =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)
  • Name it “month”.
  • Click Ok.

At this point, we have two named ranges, “month” & “amount”.

Now, let me tell you how it work

In above formulas, I have used count function to count the total number of cells with a value. Then I have used that count value as a height in offset to refer to a range.

how to create a dynamic chart range in excel formula used

In month range, we have used A2 as starting point for offset and counting the total number of cells having in column B with counta (-1 to exclude heading) which gives reference to A2:A7.

Changing source data for the chart to dynamic named range

Now, we have to change source data to named ranges we have just created.

Oh, I am sorry I forget to tell you to create a chart, please insert a line chart.

Here are the further steps.

  • Right click on your chart and select “Select Data”.

how to create a dynamic chart range in excel select data

  • Under legend entries, click on edit.

how to create a dynamic chart range in excel data table click edit

  • In series values, change range reference with named range “amount”.

how to create a dynamic chart range in excel add range name

  • Click OK.
  • In horizontal axis, click edit.

how to create a dynamic chart range in excel click edit axis range

  • Enter named range “months” for the axis label.

how to create a dynamic chart range in excel edit range in month

  • Click Ok.

All is done. Congratulations, now your chart has a dynamic range.

how to create a dynamic chart range in excel chart is ready

Important Note: While using named range in your chart source make sure to add worksheet name along with it.

Sample File

how to create a dynamic chart range in excel sample file

Last Words

Using a dynamic chart range is a super time saver & it will save your lot of efforts.

You don’t have to change your data range again & again. Every time when you update your data your chart is instantly updated.

Over To You

Have you ever used a chart with a dynamic range?

Or do you have any other idea to create a dynamic chart range?

Please share with me in the comment box.

More Tips On Dynamic Charting