How to Create a Dynamic Chart Range in Excel


- Written by Puneet

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.

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

Below, you have a chart with the month-wise amount and when you add the amount for Jun, chart values are the same, there is no change. Now the thing is, you have to update the 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 the 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) and 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 the above chart, when I have added the amount for Jun, the chart gets updated automatically. The only thing that leads you to use the 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

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

Using Dynamic Named Range

Using a dynamic named range for a chart is a 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 the 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 amounts 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.

  1. Go to Formulas Tab -> Defined Names -> Name Manager.
    how to create a dynamic chart range in excel click name manager
  2. Click on “New” to create a named range.
    how to create a dynamic chart range in excel click new
  3. 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)
  4. Name your range “amount”.
    how to create a dynamic chart range in excel enter formula
  5. Click OK.
  6. Now, create another named range by using following formula.
    • =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)
  7. Name it “month”.
  8. Click Ok.

At this point, we have two named ranges, “month” & “amount”. Now, let me tell you how it works. In the above formulas, I have used the 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 the 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 the 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.

  1. Right click on your chart and select “Select Data”.
    how to create a dynamic chart range in excel select data
  2. Under legend entries, click on edit.
    how to create a dynamic chart range in excel data table click edit
  3. In series values, change range reference with named range “amount”.
    how to create a dynamic chart range in excel add range name
  4. Click OK.
  5. In horizontal axis, click edit.
    how to create a dynamic chart range in excel click edit axis range
  6. Enter named range “months” for the axis label.
    how to create a dynamic chart range in excel edit range in month
  7. 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 you a lot of effort. You don’t have to change your data range again and again. Every time when you update your data your chart is instantly updated.

More Charting Tips and Tutorials

Last Updated: June 22, 2023

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

  1. When I paste the name range to the series name and the axis range it changes it to a simple range and it will be no longer dinamically.
    sheet1!chart –> sheet1!$A$1:$B$4
    What should I do?

  2. So I have a problem where the worksheet including a chart needs to be duplicated within the same workbook.
    My dynamic ranges are scoped at the worksheet level, so that when duplicating the worksheet, they too duplicate.
    However, the duplicated chart does not bring that functionality across and instead hard codes the range on the new sheet.
    Any suggestions to get around this without the need to rebuild the plot references via macro?

  3. I can get the “dynamic named range” method to work for columns with inputted values, but not for corresponding columns with formulas (They’re set to be blank if nothing is entered in the corresponding row). Will this only work for columns with manually inputted cells? Thank you.

  4. You TOTALLY helped me solve a huge problem with variable data sets!! WooHoo! You are awesome!!! The Offset did the trick!! THANK YOU!!!!

  5. You said use a Data Table, which you then said to create by pressing Ctrl+T but that does not give me a data table at all. Confused

    • Make sure you have clicked “into” the data set. If you are clicked outside of the set, it will not create a Table. Yes, Ctrl+T is the trick. Or old-school, Insert > Table.

  6. Puneet,

    I am trying to do this by the named ranges method. When I try to do this step –

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

    the system revolts, saying “A formula in this worksheet contains one or more invalid references” or “The formula you typed contains an error”. I am using a 64-bit Windows 7 machine.

    I have tried typing in the range as Sheet1!x (x is the name of my range of x values, and Sheet1 the sheet name) and also by Formulas-> Use in Formulas -> Paste Name, with and without the Sheet1! prefix. Neither method works.

    Would you know what is the problem?

    Perspective on my problem: I have a long data list of which I sometimes want to plot only 50 entries, sometimes 100 entries, etc. I actually want to put that parameter (the number of entries I want to plot) into a cell and have Excel plot accordingly. All the data is already there, I am not typing in new data I just want to control how much of it gets plotted.

  7. Good day.
    I have read your tutorial and it is well appreciated. My graph is a bit different, so I will explain it.
    I have a data sheet which contains SPEED, say, column C. I am trying to draw a graph where I can pick two random points for my Y-Axis. I enter the start point and endpoint on another sheet say, INPUT (Cell E2 and Cell E3) which I can alter to create different start and end points. This means the start point will change as well as the end point. Though I have successfully drawn a graph with only one set points, whenever I change even the start point, the graph collapses. What can I do please. Thank you. Jacob

  8. Hi, I am unable to edit under legend entries and horizontal.
    Please try to give easy way to make me understand because I am on biggner level.

  9. The Name definition works for me as long as you add data, but if data is dynamicaly removed from the data range the graph then still shows the bubbles of the removed values. Can’t find a way to get rid of those

  10. I am stuck building dashboards with changing requirements. I taught myself how to build dynamic ranges using the OFFSET formulas and all was good. For 3 minutes when my client said he wanted the ability to choose specific data points. My example is a rolling 5 period report. Month Month Month Quarter Month. I built it so the next month will go Month Month Quarter Month Month then Month Quarter Month Month Month next one is built to go QMMMQ. Here’s the rub…. next one should go MMMQM but he wants it to go QQM so that once a Quarter is completed by the three previous months they collapse into the Quarter and stack next to the previous Quarter. My offset only reads the previous 5 periods using a -5. Can and how to I amend my OFFSETS to accomplish the client request. Haven’t slept in days so any response appreciated. Thanks.


Leave a Comment