Create a Dynamic Chart Range in Excel (2 Methods)

When you need to update your chart range quite frequently, there’s no alternative to the dynamic chart range. It saves your time and effort as you add more data to your chart, the chart range automatically gets updated each time. So, if you are thinking about creating your own dynamic chart range then follow along with the whole article. Because you will learn 2 easy methods to create a dynamic chart range in Excel step-by-step.


Download the Practice Workbook

You can download the Excel file from the link below and practice along with it.


What is the Dynamic Chart Range?

The dynamic chart range is a chart range that updates itself automatically when you add new data to the source data.

This dynamic chart range is very responsive to the data changes. It gives the most benefit when you need to update or insert your source data quite frequently.


2 Ways to Create a Dynamic Chart Range in Excel

1. Use Excel Table to Create a Dynamic Chart Range in Excel

We can convert a set of data in an Excel spreadsheet into an Excel Table. This Excel table can help create a dynamic chart range. To do that follow the steps below:

❶ Convert your data table into an Excel table by selecting the whole data table first.

❷ After that press CTRL + T keys. This will instantly create an Excel table out of a random data table.

After pressing the CTRL + T keys, a dialog box named Create Table will appear. In the dialog box, the table range is already there. You will find a check box out there that says My table has headers. Make sure that it is ticked.

❸ After that hit the OK command.

Dynamic Chart Range in Excel: Create a Table

Now you have an Excel table. After that,

❹ Go to the INSERT menu from the main ribbon.

❺ Under the Charts group, you will find Insert Column Chart. Just click on it.

❻ Then select your preferable 2-D Column chart.

Insert column chart to create Dynamic Chart Range in Excel

Now you will see Excel has created a column chart based on your Excel table data like this:

Dynamic range chart in Excel

So, you have already created your own dynamic range chart in Excel. Now let’s test whether it works or not.

To do that, we have inserted a new record. We inserted Bruce in the Name column and 42 in the Age column. As we can see in the picture below, these newly added records in the source data have already been added to the column chart.

Example of Dynamic Chart Range in Excel

Related Content: Excel Dynamic Range Based on Cell Value


2. Create Dynamic Chart Range in Excel Using OFFSET & COUNTIF Function

A. Creating Dynamic Named Range

The easiest way to create a dynamic chart range is to use Excel Table. But for some reason, if you can’t use the previous method, then you can use the OFFSET and COUNTIF function to create a dynamic chart range in Excel.

To do this, follow the steps below:

❶ First go to the FORMULAS menu from the main ribbon. Then select Name Manager.

Creating Dynamic Named Range for dynamic chart range in Excel

After that, the Name Manager dialog box will open up.

❷ Click on New of the Name Manager dialog box.

Creating Dynamic Named Range: Name Manager

❸ A new dialog box called New Name will open. Now insert Names in a Name bar. And enter the following formula in the Refers to box.

=OFFSET(NamedRange!$B$2,0,0,COUNTA(NamedRange!$B:$B)-1,1)

Then hit the OK command.

Offset formula to create a Dynamic Named Range

❹ Again hit the New command in the Name Manager dialog box. This time insert Age in the Name box and the following formula in the Refers to box.

=OFFSET(NamedRange!$A$2,0,0,COUNTA(NamedRange!$A:$A)-1,1)

After that hit the OK command.

After all these states the Name Manager dialog box will look like this:

Read More: Excel OFFSET Dynamic Range Multiple Columns in Effective Way

B. Creating Chart Using Dynamic Named Range

Now you have to insert a column chart for the visualization of the data. To do that,

❺ Go to the INSERT menu. Under this menu from the Charts group select Insert Column Chart. Now you can select any column chart as you like.

Creating Chart Using Dynamic Named Range

❻ Now go to the DESIGN tab and click on Select Data.

❼ Then a dialog box called Select Data Source will open. There you will find an Add option under the Legend Entries (Series). Hit it.

❽  Enter the following formula in the Series values box in the Edit Series dialog box. And hit the OK command.

=NamedRange!Ages

❾ Then go back to the Select Data Source dialog box. In this dialog box, you will see Horizontal (Category) Axis Labels. Hit the Edit command under this section.

❿ After that, another dialog box called Axis Labels will appear. Just insert the following formula in the Axis label range box.

=NamedRange!Names

Finally hit the OK command

After all of these steps, you have successfully created a dynamic range chart in Excel. Now whenever you update your source data, this will automatically update the chart range instantly.

Related Content : Excel Dynamic Named Range [4 Ways]


Things to Remember

📌 The most convenient way to create a dynamic chart range is to use an Excel table.

📌 You can use the Named Range method if you have issues with creating an Excel table.


Conclusion

To sum up, we have discussed 2 methods to create a dynamic chart range in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo