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.

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.

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

Read More: How to Create a Range of Numbers in Excel (3 Easy Methods)


Similar Readings


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 in 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 Dynamic Named Range [4 Ways]

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.

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

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. 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

    • Reply
      Naimul Hasan Arif Oct 3, 2022 at 5:12 PM

      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.

Leave a reply

ExcelDemy
Logo