Dynamic Chart In Excel

What Is Dynamic Chart In Excel?

The Charts in Excel are the graphical representation of a dataset or cell range that update automatically when the selected data is modified. However, the Dynamic Chart in Excel is an advanced type of chart as it accepts the modification of the dataset with new rows/columns added next to the existing data table.

Therefore, to create a dynamic chart, we need to follow a different methodology than the one we follow for the regular chart.

For instance, we have the following chart created in an Excel spreadsheet.

Dynamic Chart in Excel - 1

The above image shows the data from A1:B7, and a chart is created based on this range. Now, let us add a few more rows of data.

Dynamic Chart in Excel - 2

We have added July and August data, but the chart still shows the data till June. It is because while creating the chart, we have provided the static range of cells from A1:B7. Any data modification after this specified range doesn’t alter the chart. In such scenarios, the Dynamic Chart in Excel is an expected choice.

Key Takeaways
  • A Dynamic Chart in Excel accepts selected data modification to the existing dataset and updates the generated chart automatically.
  • There are two ways to create a dynamic chart, i.e., using Excel Table and Named Ranges.
  • An OFFSET formula works differently if there are any blank cells in the middle of the data range. Ideally, there shouldn’t be any blank cells or rows before the last record.
  • The F3 key lists all the named ranges in the workbook.
  • Excel Tables uses structured references, so any new data added to the list will be expanded automatically.

How To Create Dynamic Chart Using Excel Table?

Excel Tables are the best for dynamic data because they use structured references. Thus, any additional data gets updated and automatically added to the Excel Table range.

If we have the data table with a normal range, we can press the shortcut key “Ctrl + T” to convert it into Excel Table format.

For instance, we have the following monthly sales data in Excel.

How To Create Dynamic Chart using Excel Table - 1

To convert this into Excel Table format, select any cells in the table range from A1:B7 and press the Excel Table shortcut key “Ctrl + T” to bring up the following “Create Table” window.

How To Create Dynamic Chart using Excel Table - 2

Since our data already has headers, check/tick the “My table had headers” checkbox, and click “OK”.

How To Create Dynamic Chart using Excel Table - 3

The Excel Table is created, as shown below.

How To Create Dynamic Chart using Excel Table - 4

Now, if we add anything to the next immediate column or row table format expands.

We have added July and August month data, and the Excel Table automatically expands, i.e., two new rows are added. Now, we can create a Dynamic Chart.

How To Create Dynamic Chart using Excel Table - 5

The steps to create a Dynamic Chart in Excel are as follows:

  • Step 1: Select the entire Excel Table range, A1:B9.
Using Excel Table - Step 1
  • Step 2: Select the “Insert” tab → go to the “Charts” group → click the “Insert Column or Bar Chart” option drop-down → select the “Clustered Column” chart type from the “2-D Column” category, as shown below.
Dynamic Chart in Excel - Using Excel Table - Step 2
  • Step 3: The column chart is generated, as shown below.
Dynamic Chart in Excel - Using Excel Table - Step 3
  • Step 4: To check the flexibility and dynamic nature of the Excel Table, add additional months’ data to the existing table, such as September and October.

Once we modified the data, the chart automatically added 2 new column bars for September & October.

Dynamic Chart in Excel - Using Excel Table - Step 4a

However, there is a drawback in using Excel Table as a data source while creating a dynamic chart. For instance, let us delete the last 2 months’ data that we have added in the above steps.

Dynamic Chart in Excel - Using Excel Table - Step 4b

When the last two lines are deleted, we see an empty space in the chart.

Dynamic Chart in Excel - Using Excel Table - Step 4c

The reason is that when we delete the data in the Excel Table, it deletes only the data, and the Excel Table range remains the same. Therefore, the chart shows the empty spaces.

To fix this issue drag the table range using the excel fill handle.

Using Excel Table - Step 4d

Now the chart will show the updated data without any empty spaces.

Dynamic Chart in Excel - Using Excel Table - Step 4e

This way, we can use the Excel Table structured format to create Dynamic Charts In Excel.

How To Create Dynamic Chart Using Named Range And Offset?

Named Ranges are rarely used to make the reports or visualizations dynamic. However, by creating named ranges, we can create dynamic charts.

Let us consider the same data from the above example. We have the monthly sales data, shown below.

How To Create Dynamic Chart Using Named Range and Offset - 1

The above one is not an Excel Table reference, but rather, it is just a normal cell reference in excel. Instead of creating the chart by using this static range, first, we need to create a named range for the fields that we are going to use.

Let us create two named ranges using the OFFSET function, one is the Sales Column, and another one is the Month Column.

The steps to create the named ranges, Month, using the OFFSET function are:

  • Step 1: Select the “Formulas” tab → go to the “Defined Names” group → click the “Define Name” option drop-down, as shown below.
Using Named Range and Offset - Step 1
  • Step 2: The “New Name” window opens. In the “Name:” box, type “Month”.
Using Named Range and Offset - Step 2
  • Step 3: In the “Refers to:” box, enter the formula

=OFFSET(‘Named_Range’!$A$2,0,0,COUNTA(‘Named_Range’!$A$2:$A$13)), and click “OK”.

Using Named Range and Offset - Step 3
  • Step 4: The named range “Month” for the Month Column is ready.
Using Named Range and Offset - Step 4

[Note: Formula Explained:

=OFFSET(‘Named_Range’!$A$2 → OFFSET will start its offsetting from cell A2.

=OFFSET(‘Named_Range’!$A$2,0, → It offsets zero rows.

=OFFSET(‘Named_Range’!$A$2,0,0, → It offsets zero columns.

=OFFSET(‘Named_Range’!$A$2,0,0, COUNTA(‘Named_Range’!$A$2:$A$13)) → Then the COUNTA excel function will provide the numbers cells that have the data in the range A2:A13. Based on the number of cells provided by the COUNTA function, the OFFSET function offsets those many numbers of rows.]

To test this, enter an equal sign in an empty cell, and type the name we created, i.e., Month.

Using Named Range and Offset - Step 4a

We can see that the IntelliSense list shows the named range that we have created. Select the same, i.e., Month, and press the “Enter” key to get the result.

Using Named Range and Offset - Step 4b

The formula displays the values from the month column starting from cell A2 to A13. Now, enter a few names in rows 10 and 11.

Using Named Range and Offset - Step 4c

The named range dynamically picks up the month names that we have entered.

The steps to create the named ranges, Sales, using the OFFSET function are:

  • Step 1: Select the “Formulas” tab → go to the “Defined Names” group → click the “Define Name” option drop-down, as shown below.
Using Named Range and Offset - Step 5
  • Step 2: The “New Name” window opens. In the “Name:” box, type “Sales”.
Using Named Range and Offset - Step 6
  • Step 3: In the “Refers to:” box, enter the formula

=OFFSET(‘Named_Range’!$B$2,0,0,COUNTA(‘Named_Range’!$B$2:$B$13)), and click “OK”.

Using Named Range and Offset - Step 7
  • Step 4: Now, we have two named ranges ready, i.e., “Month” and “Sales”.
Using Named Range and Offset - Step 8

This formula also works similarly to the previous one. OFFSET will start from cell B2 and offset the number of rows of the data returned by the COUNTA function between cells A2 to A13.

The steps to create a blank chart and then customize it to use the created named ranges are as follows:

  • Step 1: Select an empty cell in the worksheet, here, cell D2.
Dynamic Chart in Excel - Customizing a blank chart using named range
  • Step 2: Select the “Insert” tab → go to the “Charts” group → click the “Insert Column or Bar Chart” option drop-down → select the “Clustered Column” chart type from the “2-D Column” category, as shown below.
Customizing a blank chart using named range - Step 2
  • Step 3: A blank chart outline is generated, as shown below.
Customizing a blank chart using named range - Step 3
  • Step 4: Right-click on the blank chart, and click the “Select Data” option.
Customizing a blank chart using named range - Step 4
  • Step 5: The “Select Data Source” window opens. In this window, click the “Add” button in the “Legend Entries (Series)” category.
Customizing a blank chart using named range - Step 5
  • Step 6: The “Edit Series” window opens. Here, enter “Sales” in the “Series name:” field.
Customizing a blank chart using named range - Step 6
  • Step 7: In the series values, we need to enter the “Sales” named range we have created. Hence, select the box “Series Values” and press the shortcut key F3 to bring the list of named ranges in the workbook. The “Paste name” box pops up.
Customizing a blank chart using named range - Step 7
  • Step 8: Choose “Sales” named range, and click “OK”.
Customizing a blank chart using named range - Step 8
  • Step 9: Now, we should see the named range in the “Series Values” box.
Customizing a blank chart using named range - Step 9

However, this isn’t the right way to give the named range reference. The named range should include the worksheet name as well.

Enter the worksheet name in single quotes followed by an exclamation sign (!) as ‘Named_Range!’

Customizing a blank chart using named range - Step 9b

Now give the named range as “Sales”.

Customizing a blank chart using named range - Step 9c
  • Step 10: Click “OK”, and we are back to the “Select Data Source” window. In this window, click on the “Edit” option under “Horizontal (Category) Axis Labels”.
Customizing a blank chart using named range - Step 10
  • Step 11: This will open the “Axis Labels” window like the following one.
Customizing a blank chart using named range - Step 11
  • Step 12: For the named range, i.e., Month, enter the worksheet name too, in a single quote followed by an exclamation sign (!).
Customizing a blank chart using named range - Step 12
  • Step 13: Now press the F3 shortcut key to bring the list of named ranges that we created. Choose the named range “Month” and click on “OK”.
Customizing a blank chart using named range - Step 13
  • Step 14: This will insert the named range after the worksheet name.
Customizing a blank chart using named range - Step 14
  • Step 15: Click on “OK” in the next two windows. The dynamic chart looks as shown below.
Dynamic Chart in Excel - Customizing a blank chart using named range - Step 15

To test the dynamic nature of this report, let us add a couple of months’ data to the table.

Dynamic Chart in Excel - Customizing a blank chart using named range - Step 15a

As soon as we have added two months “September” and “October” in rows 10 and 11, the above chart shows the newly added month’s data.

Now let us delete those newly added records and see the changes in the chart.

Dynamic Chart in Excel - Customizing a blank chart using named range - Step 15b

We have deleted the newly added rows, and the chart doesn’t show any empty spaces, as shown in Excel Table format.

This way, we can create dynamic charts in Excel by using Excel Table and Named Ranges.

Important Things To Note

  • Ctrl + T is the shortcut key to create an Excel Table format.
  • While using the named range in Excel, we should first provide the worksheet in single quotes then followed by an exclamation mark (!).
  • F3 is the shortcut key to bring the list of named ranges created in the workbook.
  • The names we create should be unique, and there shouldn’t be any duplicates.
  • When the named range is created, there should not be any blank cells in the middle of the data.
  • Excel Table would take the new data into consideration only if we added it to the immediate row or column. If we add rows after leaving one blank row, it won’t take it into consideration.

Frequently Asked Questions (FAQs)

1. How to Create a Dynamic Chart in Excel using VBA?

To create a dynamic chart, we need to write a VBA code in Excel, and execute it using the VBA Editor, found as follows:

Select the “Developer” tab → go to the “Code” group → click the “Visual Basic” option, as shown.

Dynamic Chart in Excel - FAQ 1

2. What are the Advantages of using Dynamic Charts in Excel?

The advantages of creating a Dynamic Chart In Excel are:
We can save a lot of time by creating a dynamic chart. We need not have to worry about the new added to the list.
Any modification of data will automatically reflect in the chart.
Quick visualizations and end users are up-to-date with the data in the visualization.

3. How do we create Dynamic Charts in Excel in a drop-down list?

We have the following data in Excel.

Dynamic Chart in Excel - FAQ 3

Create a drop-down list of products.

FAQ 3 - Step 1

Next, copy the month names before the drop-down list column.

FAQ 3 - Step 2

Select cell H2, apply the following formula, and drag it to all the other cells as well.
=INDEX($B$2:$E$9,MATCH(G2,$A$2:$A$9,0),MATCH($H$1,$B$1:$E$1,0))

FAQ 3 - Step 3

Now using the range G1:H9 create a chart.

FAQ 3 - Step 4a

Now change the drop-down list in cell H1 and see the changes happening in the chart.

FAQ 3 - Step 4b

We have changed the value to VIVO, and the chart dynamically shows the VIVO sales numbers.

Download Template

This article must help understand the Dynamic Chart in Excel’s formula and examples. We can download the template here to use it instantly.

Guide to Dynamic Chart In Excel. Here we create dynamic charts with Excel Table, Named Range & OFFSET with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *