Mrinmoy
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.
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.
Now you will see Excel has created a column chart based on your Excel table data like this:
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.
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.
After that, the Name Manager dialog box will open up.
âť· Click on New of the Name Manager dialog box.
❸ 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.
âťą 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.
âť» 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!NamesFinally 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.
