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.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Methods 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.

Dataset 1

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.

Using Table

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

Using Table

The results appear as shown in the image below.

Using Table

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

How to Make Dynamic Charts in Excel Using Table

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

How to Make Dynamic Charts in Excel Using Table

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.

How to Make Dynamic Charts in Excel Using Table


2. Using Dynamic Named Range with OFFSET Function

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.

Using Named Range

  • 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.

Using Named Range

  • 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.

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

📌 Step 02: Insert Column Chart

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

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

  • 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.

How to Make Dynamic Charts in Excel Using Named Range

  • Now, click OK to confirm your choice.

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

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

How to Make Dynamic Charts in Excel Using Named Range

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

Read More: How to Use Dynamic Named Range in an Excel Chart (A Complete Guide)


3. Making Dynamic Charts with Multiple Drop-Downs

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.

Dataset 2

📌 Step 01: Insert Table and Helper Columns

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

Create Data Table

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

Create Data Table

  • 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.

Create Data Table

  • 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.

Create Data Table

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

How to Make Dynamic Charts in Excel with Multiple Drop-down

📌 Step 02: Insert Data Validation List

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

How to Make Dynamic Charts in Excel with Multiple Drop-down

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

How to Make Dynamic Charts in Excel with Multiple Drop-down

  • 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.

How to Make Dynamic Charts in Excel with Multiple Drop-down

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.

How to Make Dynamic Charts in Excel with Multiple Drop-down

📌 Step 03: Insert Bar Chart

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

How to Make Dynamic Charts in Excel with Multiple Drop-down

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

How to Make Dynamic Charts in Excel with Multiple Drop-down

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.

How to Make Dynamic Charts in Excel with Multiple Drop-down

Read More: How to Create Chart with Dynamic Date Range in Excel (2 Easy Ways)


🔔 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).

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. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo