Want a way to quickly create a dynamic Excel chart or dashboard? Your users will be able to select the data they want to see by selecting from a drop-down list. While this takes a few minutes longer than using a data validation list for selection, it has more capability for modifying lists, creating sub-drop-down lists, etc.
Using a drop-down list to create a dynamic chart creates a professional look in your dashboard and the drop-down gives a visual reference to the user where they should click. But, this isn’t the only way to create a dynamic chart allowing users to select data from a list. You can also use a data validation list. Check out how to create a dynamic chart using a data validation list.
Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.
Create a Dummy Set of Data
Create your data by entering region titles in B15:B18. Use the RANDBETWEEN() function to create sample data like that in the range C15:H18. To freeze the formulas so the numbers don’t keep recalculating, copy the data range containing RANDBETWEEN() and paste it back over the same area using Paste, Paste Special, Values. This replaces the formulas with fixed values.
Use INDEX to Retrieve the Data to be Charted
This dynamic chart changes when you select a region from the drop-down list. An INDEX formula uses the number of the region from the drop-down list to identify which row of data to retrieve into the cells used for chart data. When the chart data changes, the chart updates.
To create the dynamic chart,
1. Create a chart using text label and numbers in the range B9:H10.
2. In cell B7 enter the label Selection. In cell C7 enter the value 3.
The value in C7 will be used to select which row of data from rows 15 to 18 is used in the chart.
3. In cell B10 enter the INDEX formula,
4. Copy the INDEX function in B10 across from B10 to H10.
This function retrieves data from B15:H18 according to the row and column in cell C7.
The INDEX function uses the parameters,
INDEX retrieves a value from the array B15:H18. row_num and col_num specify which cell in the array to retrieve. Row_num is the number 3 in cell C7. The column position is specified by the numbers in B13:H17.
Your sheet should now look like the following figure. You can test your dashboard by typing values from 1 to 4 in C7. When you change that number you change the row referenced in INDEX and retrieve different data.
Create the Drop-Down List
1. Finish your chart by inserting a drag-and-drop box from the Developers tab onto the worksheet. On the Developers tab, in the Controls group, click Insert, select the Combo List icon. Now drag across the worksheet to draw the drop-down (combo box). (If you do not have a Developers tab, see these instructions to add the Developer’s tab to your Excel.)
If you want to align the corners of your drop-down list with cell corners, hold -down the Alt key as you drag the drop-down list shape.
2. Right click the drop-down list and select Form Control to display the Format Object dialog box.
3. In the Input Range, enter $B$15:$B$18 so the titles on the left side of the data will appear in the drop-down.
4. Enter $C$7 in Cell Link to specify where to put the number of the item selected from the list. Click Ok.
You won’t be able to make a selection from the drop-down form if the form is still selected, so click on a blank cell.
At this point your dynamic dashboard is ready to use.
Check your dynamic chart dashboard by clicking on the arrow in the drop-down list and selecting a region name from the list. The number of that item in the list appears in C7. That is the row number corresponding to your selection. INDEX uses that row number and the column numbers from row 13 to retrieve the data into the range of chart data.
You can make drop-down lists more flexible by making the list of menu items automatically expand as new items are added to the list. Learn how to do that in the article How to Create Dynamic Range Names for Automatically Expanding Menus.
Download this dynamic Excel Chart with Drop-Down list and INDEX function example file.