How to Make Dynamic Charts in Excel (3 Useful Methods)

Looking for ways to make Dynamic Charts in Microsoft Excel? Then, you’ve come to the right place. Gone are the days of updating charts manually, the dynamic charts will update as soon as you enter the data. Granted this, the following article demonstrates 3 handy ways how to make dynamic charts in Excel.

Dynamic Chart automatically updates itself when the data source gets updated. In contrast, the Static Chart does not update when the data source is changed.

Let’s say we have the following dataset shown in the B4:C11 cells below. The dataset shows the Month, and the Total Sales in USD respectively.

Luckily, Excel makes it very easy to make Dynamic Charts. So, without further delay, let’s see the process step-by-step.

1. Making Dynamic Charts Using Excel Table

Starting with the simplest method, we’ll utilize Excel Table to make a Dynamic Chart. Let’s see it in action.

📌 Steps:

To start, select any cell within the dataset and go to Insert > Table.

Now, select the range of cells from B5:C11 and check the My table has headers option.

The results appear as shown in the image below.

Secondly, select the dataset.
Next, navigate to Insert > Insert Column or Bar Chart > Clustered Column.

Following this, you can customize your chart using the Chart Elements option.

That’s it you’ve made a Dynamic Chart. Now. let’s check if the chart updates automatically.

Lastly, let’s add the Total Sales for July and as you can see it shows in the chart given below.

2. Using Dynamic Named Range with OFFSET Function to Make Dynamic Charts

For those of you who are proficient with Excel formulas, our following method has you covered.

Here, we’ll employ Excel’s OFFSET and COUNTIF functions to create Dynamic Charts.

Now, allow me to demonstrate the process bit by bit.

📌 Step 01: Define Named Range

At the very beginning, go to Formulas > Define Name.

Now, click the Define Name button and a wizard pops up.
Here, enter a suitable name for the data range and paste the formula in the Refers to box.

=OFFSET('Named Range'!$C$6,,,COUNTIF('Named Range'!$C$6:$C$100,"<>"))

Here, the C6 cell refers to the first cell in the Total Sales column.

⚡ Formula Breakdown:

In the above formula, the OFFSET function returns a range of cells from the specified rows and columns.
Firstly, the ‘Named Range’!$C$6 is the reference argument that provides the initial point of the range.
Secondly, we leave the rows and cols argument blank.
Thirdly, the COUNTIF(‘Named Range’!$C$6:$C$100,”<>”) serves as the optional height argument.
Now, we include the cells from C6:C100 cells (range argument) since we want to add rows to the data table.
Lastly, the “<>” (criteria argument) is the not-blank operator which returns the values of all the cells which are not blank.

Similarly, we create a second Named Range for the Month column using the expression below.

=OFFSET('Named Range'!$B$6,,,COUNTIF('Named Range'!$B$6:$B$100,"<>"))

Here, the B6 cell indicates the starting point of the Month column and again we select up to the B100 cell.

In turn, if we go to Formulas > Name Manager we can see all the Names.

📌 Step 02: Insert Column Chart

Secondly, go to Insert > Insert Column or Bar Chart > Clustered Column.

Next, select the chart and right-click on the mouse to go to the Select Data option.

Following this, a dialog box appears, now click on the Edit button.

Then, select the cell reference as shown below and press the F3 key to bring the list of Names. In this instance, we chose Sales.

Now, click OK to confirm your choice.

In turn, click the Edit button to change the x-axis labels.

In a similar fashion, change the Series value for the Month as shown in the image below.

Eventually, we can add a row for July to check whether the chart updates automatically.

You can explore more ways to make Dynamic Chart in Excel if you wish.

3. Making Dynamic Charts with Multiple Drop-Downs in Excel

In case you’re wondering if it’s possible to add multiple drop-downs to select values that will be displayed by the chart instantly. Then, you’re in luck, because our next method has the answer. Here, we’ll use the EOMONTH and SUMIFS functions, so just follow along.

Considering the dataset shown in the B4:D14 cells. Here, the dataset shows the Location, Date, and Total Sales in USD respectively.

📌 Step 01: Insert Table and Helper Columns

Firstly, select any cell in the dataset and navigate to Insert > Table.

Next, select the B5:D14 cells and check the My table has headers option.

Now, in the B18 cell type in the formula given below.

=EOMONTH(C5,0)

In this expression, the C5 cell refers to the start date of the dataset.

As a note, the EOMONTH returns the last day of the month. In this case, the C5 cell (start_date argument) and the 0 (months argument) tells the function to return the last day of the same month.

Likewise, we obtain the last day of the second month.

=EOMONTH(B21,1)

Here, the B21 cell (start_date argument) points to the last day of the previous month. Moreover, the 1 (months argument) instructs the function to return the last day of the next month.

Then, we copy the names of the Location and paste them into their respective column.

📌 Step 02: Insert Data Validation List

Secondly, select the G4 cell and go to Data > Data Validation.

In this step, choose List from the Allow drop-down and select the C19:C21 cells as the Source.

Similarly, follow the same process for the Date and choose the B19:C21 cells.
Next, for the Sales value type in or copy the expression given below.

=SUMIFS($D:$D,$B:$B,G4,$C:$C,"<="&G5)

⚡ Formula Breakdown:

In this formula, the SUMIFS function adds up all the cells that meet the multiple, given criteria.
Firstly, the $D:$D (sum_range argument) are added Sales values.
Next, the $B:$B (criteria_range1 argument) refers to all the Locations.
Secondly, the G4 cell (criteria1 argument) indicates the criteria to apply to the Location column.
Thirdly, the $C:$C (criteria_range2 argument) which points to the Date.
Lastly, the “<=”&G5 (criteria2 argument) represent the criteria for choosing all the Dates less than and equal to the value chosen in the G5 cell.

📄 Note: Make sure to provide Absolute Cell Reference ($) for columns B, C, and D in this expression.

That’s it, this completes the dropdowns. You can select the Location and the Date and Sales amount up to that date will be shown.

📌 Step 03: Insert Bar Chart

Thirdly, select the G4 and G5 cells.
Then, navigate to Insert > Insert Column or Bar Chart > Clustered Column.

Immediately, the Bar Chart shown in the following screenshot appears.

Consequently, we can check by adding a new row. In this case, we’ve added an entry for the USA, and the Sales value updates accordingly.

🔔 Things to Remember

Firstly, you should not leave any blank cells in the Named Range.
Secondly, make sure to follow the naming convention when entering the Series values. For instance, enter the ‘Named Range’ (Sheet Name) within single quotes, followed by an Exclamation (!) sign, and lastly, the Sales (Defined Name).

Download Practice Workbook

Making Dynamic Charts.xlsx

Conclusion

I hope this article helped you understand how to make Dynamic Charts in Excel. If you have any queries, please leave a comment below.

<< Go Back to Dynamic Excel Charts | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!