How to Create a Dynamic Chart with Drop down List in Excel?

Last Updated : 12 Sep, 2022

The Dynamic Charts are the chart that gets updated itself when the range of underline data changes. In these types, of charts the dynamic range is used as the source data of the chart. So, as the data changes the dynamic range gets updated instantly which further updates the chart according to the new data range. These charts are very useful when we have a very large dataset and we need to perform a comparative analysis of that dataset. For example, in analyzing the revenue generated by different products of the company over a year. In this tutorial, we will look into a Dynamic chart with a drop-down list which will get updated itself, when we change the data of the drop-down list.

Making Dynamic Chart with Dropdown List

Now we are going to create a dynamic chart with a drop-down list for the revenue generated by different apps over a period of six months. (assume revenue in Cr.)

Step 1: Create Dataset

In this step, we will be inserting random revenue generated by various apps of google into our excel sheet. Below is the screenshot of the random data that we will use for the dynamic chart.

Step 2: Insert a drop-down list

In this step, we will insert a drop-down list. For this go to A separate cell > Data > Data Tools > Data Validations and select the data validation.

After clicking on the Data Validation button, it will give a popup that asks about validation criteria. For validation criteria, we need to add the following things

Allow: List
Source: Add all the month's names by selecting them in a range.

After adding the validation criteria we need to click on the OK button. This will create a drop-down list in the selected cell(Here, Cell A8) with the data value of all six months.

Before moving further, we will highlight the selected cell(A8) with different colors in order to easily make its position visible. For this Select your cell(Here, cell A8) > Home > Cell Style and we will be choosing an orange color(You can choose whatever color you want).

Changing-the-color-of-the-drop-down-list-cell

Step 3: Create a data preparation table

In this step, we will prepare a separate table that is used to retrieve the data from the dataset using the drop-down list. This table will show the data underlying the drop-down list item and we will use this table to generate our dynamic chart. For this first, we need to copy all the app's names to a different new cell(Here Cell J2-J9). Below is the screenshot attached for this.

Now, we need to find out which month is getting selected in the drop-down list. For we will use a different cell(Here, K1) and give this cell a reference to the drop-down list cell(Here, A8).

This operation will create a reference between the two cells(Here, Cell A8 and K1). So, the value of cell K1 will be updated according to the value selected in the drop-down list cell A8.

Step 4: Importing data into the preparation table

In this step, we will import the revenue data of each app into our preparation table. For this, we will use the index match formula. The formula for index matching is given below(You have to update the cell value in the formula according to your own dataset).

=INDEX($B$2:$H$6,MATCH(J2,$A$2:$A$6,0),MATCH($K$1,$B$1:$H$1,0))

Note: In above formula the dollar($) sign is used to fix the dataset, that can be done using F4 key. Just select your range of data(Here, the complete revenue dataset table) and press F4 key.

Below is the screenshot attached.

This will fetch a particular app data in the entire dataset and store that value in our preparation table. Now, we have to use the same formula for our remaining apps. For this we just need to double click on that particular cell it will auto-fill for the remaining rows.

Step 5: Creating a chart using the preparation table

We will prepare the dynamic chart using our preparation table in this step. For this Select table > Insert > Chats > Column chat. Below is the screenshot attached for this.