How to Use Dynamic Named Range in Excel Chart (Step by Step)

This article illustrates how to use the dynamic named range feature in an Excel chart. Without the feature, we have to readjust the source data of a chart every time we add or remove data from the dataset to show the changes in the chart. To get rid of this unnecessary painful job, learn to make your chart dynamic with the use of named ranges.

The dynamic named range is a feature in Excel that adjusts the range automatically when we add or remove data to the range.

Step 1: Preparing Dataset for Dynamic Named Range in Excel

In this article, we’re going to use the following dataset that illustrates the growth of the world population from 1950 to 2020. We want to make the dataset dynamic with the use of named ranges. It means when we add a new row of data the range will expand accordingly.

Excel Use Dynamic Named Ranged in Chart

Step 2: Creating Dynamic Named Range to Use in Chart in Excel

To make our dataset a dynamic named range, follow the instruction below.

  • Go to the Formulas tab in the Ribbon.
  • Click the Define Name option.

  • In the New Name window, type Year (same as the header name, for convenience) in the Name input box.
  • In the Scope dropdown list, select the Sheet1 option as our dataset is in the Sheet1 of the workbook.

  • Then in the input box named Refers to, put the following formula
  • Finally hit OK to save the settings.

Excel Use Dynamic Named Ranged in Chart

  • Follow the same procedure, to make the Population in Billions column a dynamic named range. In this case, use the following formula

Excel Use Dynamic Named Ranged in Chart

  • To check the named ranges we created, click on the Name Manager button from the Formulas tab.

Excel Use Dynamic Named Ranged in Chart

  • In the Name Manager window, there are two name rangesYear and Population.

Formula Breakdown
Let’s breakdown the formula-


The syntax of the OFFSET function is-
=OFFSET(reference, rows, cols, [height], [width])
The function returns a reference to a range, where
reference– the starting point, in our formula, Sheet1!$B$2- is the first cell value in the Year column.
rows- a row offset, set it to 0 in our formula.
cols- a column offset, set it to 0 in our formula.
[height]- the height of the returned range. We used the COUNTA function to count all the no empty cells in column B. Here we subtracted 1, as the column has a header. Leave it if the column has no header.
[width]- the width of the returned range. Always set it to 1.

Read More: Excel INDIRECT Function with Named Range

Step 3: Applying the Use of the Dynamic Named Range in Excel Chart 

Now we’re going to use the dynamic named ranges in an Excel chart. Let’s follow the moves below to accomplish this.

  • Select an empty cell in the worksheet at a suitable place.
  • Go to the Insert tab in the Excel Ribbon.
  • Click on the Scatter Chart.

Excel Use Dynamic Named Ranged in Chart

  • From the options choose your preferable one.
  • There is an empty chart appeared on the worksheet.

Excel Use Dynamic Named Ranged in Chart

  • Now right click on the chart.
  • Then choose the Select Data.

Excel Use Dynamic Named Ranged in Chart

  • In the Select Data Source window, click on the Add button.

  • Click on the header of the column “Population in Billions” to set the series name.

Excel Use Dynamic Named Ranged in Chart

  • Then, put “=Sheet1!Year” in the Series X values input box as we want to display the year values in the x ordinate. Here, the dynamic named range Year belongs to Sheet1, as we defined earlier.
  • Similarly, put “=Sheet1!Population”, the other dynamic named range i.e., Population in the Series Y values input box.
  • Finally hit Enter to go.

  • To save all these settings, click on the OK button in the Select Data Source

  • As the output, we’ve got the scatter plot showing the YearPopulation relation.

Excel Use Dynamic Named Ranged in Chart

Read More: Excel Reference Named Range in Another Sheet

Step 4: Checking Use of Dynamic Named Range in Excel Chart

Now to check whether we could implement the dynamic named range in the Excel chart properly, put two more rows to the dataset. Let’s enter the predicted population for the years 2030 and 2040 in the dataset.

Excel Use Dynamic Named Ranged in Chart

The chart adjusted with two new entries automatically.

Things to Remember

  • We could implement the same functionality by converting our dataset into an Excel table, and then using it as the source of the chart. But in many cases, it is not a good choice to turn your dataset into a table.
  • While creating a named range, we should follow the naming conventions and the dataset should not have any blank cells in it.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Now, we know how to use the dynamic named ranges in the Excel chart. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

<< Go Back to Named Range | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

  1. I use both the OFFSET-version above, the new dynamic arrays, like $A$1#, and named ranges as chart series data references to have the charts dynamic. However, it seems that Excel replaces them with fixed addresses when I copy a sheet to a new workbook. (I have worksheet scope on the named ranges). Would you have any idea why and maybe a workaround for this problem?

    • Hello JOHAN,
      First, thanks for your query. Actually, dynamic named ranges created with OFFSET, and COUNTA functions don’t work when copied to another workbook. You can use a workaround instead. Follow the steps below.
      • At first, open the Name Manager.
      • Then, click on any name and tap on the Edit button.

      Instantly, it will open the Edit Name dialog box.
      • Here, change the previous formula in the Refers to box and give this new one.
      • As usual, click OK.

      • Similarly, do the same for the second name also. The formula for this is similar also.

      Now, watch the GIF. It’s working in the new workbook.

      And the chart is still dynamic. It’s changing while you are inputting new values.
      That’s all from me on this topic. Hope you find this helpful. Follow our website ExcelDemy to explore more about Excel. Happy Excelling.
      Excel & VBA Content Developer
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF