Mrinmoy Roy
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 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 data changes. It gives the most benefit when you need to update or insert your source data quite frequently.
Read More: How to Dynamically Change Excel Chart Data (3 Effective Methods)
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.
Read More: How to Create a Range of Numbers in Excel (3 Easy Methods)
Similar Readings
- How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)
- Data Validation Drop Down List with Excel Table Dynamic Range
- How to Create Chart with Dynamic Date Range in Excel (2 Easy Ways)
- Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)
- How to Make Dynamic Charts in Excel (3 Useful Methods)
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 in 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:
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.
Read More: How to Use Dynamic Named Range in an Excel Chart (A Complete Guide)
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
- Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)
- OFFSET Function to Create & Use Dynamic Range in Excel
- How to Create Dynamic Excel Charts with Drop-Down List (3 Ways)
- Excel Charts with Dynamic Title and Legend Labels
- How to Create Dynamic Charts in Excel Using Data Filters (3 Easy Ways)
- Excel Dynamic Named Range [4 Ways]
- How to Create Dynamic Range Using Excel INDEX Function
Hi Mrinmoy – Hope you are doing well !!
I wanted to check if we can create the dynamic range when we have multiple rows and multiple column For Example:
Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22
x1 9 9 1 1 5 1 3
x2 5 3 2 8 8 7 9
x3 3 8 9 7 3 4 7
above is the data and we will be getting new month column each month but for the graphs we only need to display the last 3 months
According to your requirements, you wanted to display the last 3 months’ data by a graph. You can follow the following procedure where I have tried to give you a simple solution that will help you display the last 3months’ data by graphical representation..
Create a new column, input the following formula in the 1st cell of that column and AutoFill till the cell you need. This additional column will define with numerical value the last 3 rows containing data.
=IF(AND(D4>0,ISBLANK(D5)),1,IF(B5=1,2,IF(B5=2,3,””)))
Next, create a Pivot Table with Months as Filters, Last 3 Months as Axis, and Sum of Product 1, Sum of Product 2, and Sum of Product 3 as Values.
Now, choose your preferred graphical representation format to display the last 3 months’ data.
Note: Don’t forget to refresh the Pivot Table after inserting the new month’s data. Otherwise, the graph won’t get updated. Alternatively, you can use Auto Update Pivot Table to lessen your hustle.