The ability to control a chart with a form control such as a Combo Box provides interactivity for the user. Allowing the user to be able to interact with an Excel dashboard takes your dashboard to another level.
In this tutorial, I have a list of the top 5 goalscorers from 4 different European leagues. I want the user to be able to select a league from a list and for the correct chart to be displayed. The steps required to make this happen are outlined below:
Insert the Combo Box
The Developer tab is required to insert the Combo Box in Excel 2007 and later. If you cannot see the Developer tab;
- Click the Office button and then click the Excel Options button
- Select the Show Developer tab in the Ribbon option in the dialogue box
- Click Ok
- Click the File tab on the Ribbon and then select Options
- Select the Customize Ribbon category
- Check the Developer tab box and click Ok
With the Developer tab visible, the Combo Box can be inserted.
- Click the Developer tab on the Ribbon
- Click the Insert button in the Controls group and select the Combo Box (Form Control) button
- Draw the combo box onto the worksheet where you would like it to appear
- Right click on the form control and select Format Control from the menu
- Select the Control tab if necessary
- Click in the Input range: field and select the range of cells that contain the options you want to appear in the list
- Click in the Cell link: field and select a cell to place the result of the combo box selection
- Click Ok
Create a Nested IF Formula
The combo box is set up for the user to select a league and for the result of that selection to be stored in a cell. It is now time to do something with that selection.
I will create a nested IF formula to check the combo box selection and display data from the selected league.
Enter the formula below where $A$9 is the cell used for the combo box cell link, and the A1, D1, G1 and J1 cells is the data from the selected league. This formula should be entered in all the cells you want to pull data from the league for.
Build the Chart
You can now insert the chart using the data extracted by the nested IF formulas.
- Select the range of cells containing the nested IF formulas
- Click the Insert tab on the Ribbon
- Click the Column chart button and select the type of column chart you want to use
- Format the chart as desired
And that’s it. A chart which the user can interact with via a combo box.
Download the chart_with_combo_box.xls spreadsheet used in this tutorial for a better look. Normally all of the data required for the Combo Box to function would be stored on a separate hidden sheet.
The sheet should contain the combo box and the chart only. The data has been left on this sheet to aid the explanation of the technique only.