Excel Dashboards and Reports: Combo Box Example
You can use Combo Box controls to give your Excel dashboard and report users an intuitive way to select data via a drop-down selector. This figure shows a thermometer chart that’s controlled by the combo box above it. When a user selects the Southwest region, the chart responds by plotting the data for the selected region.
To create this example, start with the raw dataset shown in the following figure. This dataset contains the data for each region. Near the raw data, reserve a cell where the combo box will output its value (cell M7, in this example). This cell will catch the index number of the combo box entry selected.
You then create the analysis layer (the staging table) that consists of all formulas, as shown in the following figure. The chart reads from this staging table, allowing you to control what the chart sees. The first cell of the staging table contains the following INDEX formula:
The INDEX function converts an index number to a value that can be recognized. An INDEX function requires two arguments to work properly. The first argument is the range of the list you’re working with. The second argument is the index number.
In this example, you’re using the index number from the combo box (in cell M7) and extracting the value from the appropriate range (2012 data in P7:P14). Again, notice the use of the absolute $ signs. This ensures that the cell references in the formulas don’t shift when they’re copied down and across.
Take another look at the preceding figure to see what’s happening. The INDEX formula in cell P2 points to the range that contains the 2012 data. It then captures the index number in cell M7 (which traps the output value of the combo box).
The index number happens to be 7. So the formula in cell P2 will extract the seventh value from the 2012 data range (in this case, Southwest).
When you copy the formula across, Excel adjusts the formula to extract the seventh value from each year’s data range.
After your INDEX formulas are in place, you have a clean staging table that you can use to create your chart; see the following figure.