Dynamic Excel Charts (Including Filter and VBA Methods)

In this Excel tutorial, you will learn to create dynamic Excel charts using Excel Table, Define Name Range commands, and the VBA Macro tool. In addition, we will discuss the use of OFFSET, INDEX, COUNT, and COUNTA functions. Using the Excel FILTER function, we will explain the development of dynamic filtered charts.

We have used Microsoft Office 365 while preparing the content. Although the functions and tools are applicable in the older Excel versions from Excel 2007, Microsoft introduced the FILTER function in Excel 2019.

Today is the age of information. Creating dynamic charts in Excel is an important part for professionals nowadays. We observe extensive use of data in the case of research, experiments, surveys, etc. Therefore, representing enormous data via charts is no longer a choice, it’s a necessity.

Download Practice Workbook

What Is a Dynamic Excel Chart?

In Excel, the dynamic chart is a special type of chart that can be up to date automatically by nature when one or multiple rows are added or removed from the range or table. Whereas, static charts are unable to update themselves. Users can create Dynamic Charts from an Excel Table or Named Range with suitable functions such as OFFSET, INDEX, and so on.

How Many Ways to Create Dynamic Charts in Excel?

There are 3 convenient ways to create dynamic charts in Excel. They are as follows:

1. Using Excel Table.
2. Applying Named Range.
3. Using VBA Macro tool.

1. Can You Make Dynamic Charts from Excel Table?

Yes, you can create dynamic charts from the Excel Table which is the easiest way. A Table in Excel automatically updates itself once new rows are added.

• Initially, select the B4:E13 range >> Then press Ctrl + T keys to convert the range into a Table.

• Next, select the table and select as follows: Insert tab>> Insert Column or Bar Chart command >> 2-D Clustered Column chart type.

Click the image for details

• Therefore, we obtain a 2-D Clustered Column chart containing sales of representatives.

• As you can see in the picture, inserting a row in the B14:E14 range, consequently we observe the presence of data for Marc in the following chart.

2. Do Excel Functions Within Named Range Create Dynamic Charts?

Using the Named Range allows you to develop dynamic charts that respond when changes in rows or columns take place. The Named Range is generally used to group a range. We will use the OFFSET function to get the grouped range based on relative reference.

• First to group the representatives, go to the Formulas tab >> select the Define Name command.
• Next, type Representatives in the Name field of the Edit Name dialog.
• Further, insert the following formula containing the OFFSET and COUNTA functions in the Refers to field >> hit the OK button.

• By following the same instructions, type Sales in the Name field of the Edit Name dialog.
• As well as input the formula below in the Refers to field >> hit the OK command.

• Now go to the Insert menu >> Click on the Insert Column and Bar Chart command.
• Thus a blank chart appears >> Right-click on the blank chart >> Select the Select Data option from the context menu.

Click the image for details

• Therefore, a dialog box named Select Data Source appears.
• Then, click on the Add command from the Legend Entries(Series) field.

• Next enter =NamedRange!Sales in the Series values field >> Hit the OK button.

• Further, click on the Edit command from the Select Data Source dialog box.

• Then, insert =NamedRange!Representatives in the Axis label range field >> Hit the OK command.

• Finally, load the Select Data Source dialog box by clicking on the OK button.

• Thus we obtain the chart with all the data in the C4:C13 range.

• We also navigate the presence of Marc in the chart once we insert his information in the B14:D14 range.

3. How to Apply VBA Macro Tool to Develop Dynamic Charts?

By applying a simple VBA code with a Worksheet Change event, you can update your charts whenever it updates or changes the dataset such as the addition or removal of data. For instance, we are likely to insert charts for the Representatives and Sales ($) columns.

• In the beginning, we must insert a blank chart. To do so, click as follows, Insert tab>> Insert Column or Bar Chart command >> 2-D Clustered Column chart type.

• Next, write the following VBA code in the dedicated worksheet Module applying the VBA Worksheet Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
'Developed by ExcelDemy
Dim DataRangeA As Range
Dim DataRangeC As Range
Dim CombinedRange As Range
Dim ChartObject As ChartObject
Set ws = ThisWorkbook.Sheets("VBA")
Set DataRangeA = ws.Range("C5:C" & Cells(Rows.Count, 3).End(xlUp).Row)
Set DataRangeC = ws.Range("E5:E" & Cells(Rows.Count, 5).End(xlUp).Row)
Set CombinedRange = Union(DataRangeA, DataRangeC)
If Not Intersect(Target, CombinedRange) Is Nothing Then
Set ChartObject = ws.ChartObjects("Chart 2")
ChartObject.Chart.ChartTitle.Text = "Sales Data of 2022"
ChartObject.Chart.SetSourceData CombinedRange
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
[Your Code]
End Sub

Dim ws As Worksheet
Dim DataRangeA As Range
Dim DataRangeC As Range
Set ws = ThisWorkbook.Sheets("VBA")
Set DataRangeA = ws.Range("C5:C" & Cells(Rows.Count, 3).End(xlUp).Row)
Set DataRangeC = ws.Range("E5:E" & Cells(Rows.Count, 5).End(xlUp).Row)

Dim CombinedRange As Range
Set CombinedRange = Union(DataRangeA, DataRangeC)

Dim ChartObject As ChartObject
If Not Intersect(Target, CombinedRange) Is Nothing Then
Set ChartObject = ws.ChartObjects("Chart 2")
ChartObject.Chart.ChartTitle.Text = "Sales Data of 2022"
ChartObject.Chart.SetSourceData CombinedRange
End If

• Thus we get the updated charts after adding the data for Melissa in the B11:E11 range.

What Is the Way to Create Dynamic Charts Ignoring Empty Values in Excel?

You can ignore data containing blank cells from a range in the Excel charts. The procedure is almost the same as the previously mentioned approaches where we used the Named Range command. However, the previous approach doesn’t ignore the blank values. To count cells containing sales data (specifically integer), we will use the COUNT function with the INDEX function.

• Initially, type Person in the Name field >> Insert the following formula containing INDEX and COUNT functions in the Edit Name dialog.

• Next, type Sales(1) in the Name field >> Write the formula as follows in the Edit Name dialog.

• Get the Select Data Source dialog box by following the steps of the similar procedure of the Named Range approach.
• After that insert =IgnoreEmpty!Sales1 in the Series values field of the Edit Series dialog box.

• Further insert =IgnoreEmpty!Person in the Axis label range field of the Axis Labels dialog box.

• Finally, load the Select Data Source dialog box by hitting the OK button.

• Therefore, we obtain the chart containing information on the representatives and ignore the blank cells.

How to Create Filtered Dynamic Charts in Excel?

If you are likely to create a filtered dynamic chart, you must use the UNIQUE function to make a drop-down list with the Data Validation tool first.

• To begin the procedure, create a unique list in the G5:G7 range by inserting the following formula containing the UNIQUE function in the G5 cell.

• Next, make a drop-down list in the D16 cell by using the Data Validation tool.

• Further, write the following formula containing the FILTER function in the B19 cell to obtain filtered data based on the selection of the drop-down list.

• Therefore, we obtain a filtered result for AC in the B19:E21 range.
• Furthermore, create a chart by clicking on the Insert Column or Bar Chart command that we mentioned earlier while constructing the approaches.

• As you can see in the image, by selecting TV from the drop-down list of the D16 cell, we get filtered results in the B19:E22 range as well as a Clustered Column chart.

Note: You will not get the FILTER function in any other versions without Microsoft Office 365, Excel 2019, and 2021.

What Are Things to Remember For Creating Dynamic Charts in Excel?

• Excel Table to create dynamic charts with lower effort.
• OFFSET and COUNT functions in the Named Range to create a group for the range. that helps to create dynamic charts.
• Named Range is also applicable to avoid the blanks while creating charts.
• Use of Data Consolidation tool, UNIQUE, and FILTER functions to develop filtered dynamic charts.

Conclusion

Throughout the tutorial, we provided a complete guideline to create dynamic Excel charts using Excel Table, Named Range, and VBA Macro tool that will be beneficial to you. Additionally, you can construct dynamic charts by applying the Named Range to ignore empty cells from the dataset. Moreover, use the filtered dynamic chart when you have a large dataset and need to filter frequently where the UNIQUE and FILTER function plays a vital role. Don’t forget to leave your thoughts in the comment section. Hope to catch you soon with some new content.

Frequently Asked Questions

Q1. What does a dynamic chart do in Excel?

Answer: Dynamic charts in Excel update automatically once new rows containing data are added or removal of rows takes place.

Q2. What are dynamic charts vs static charts in Excel?

Answer: Dynamic charts update automatically with the inclusion or exclusion of data from the selected range. On the other hand, static charts don’t update by employing new rows in the dataset.

Q3. What is keyboard shortcut to create Excel charts?

Answer: By selecting the dataset and pressing the Alt + F1 keys, you can create Excel charts.

Dynamic Excel Charts: Knowledge Hub

<< Go Back to Excel Charts | Learn Excel