Excel Dashboards and Reports: List Box Example
One of the more useful ways to use a list box in your Excel dashboards and reports is to control multiple charts with one selector. The following figure illustrates an example of this. As a region selection is made in the list box, all three charts are fed the data for that region, adjusting the charts to correspond with the selection made.
Happily, all this is done without VBA code, just a handful of formulas and a list box.
To create this example, start with three raw datasets — as shown in the following figure — that contain three categories of data: Revenues, Net Income %, and Gross Margin. Each dataset contains a separate line for each region, including one for All Regions.
You then add a list box that outputs the index number of the selected item to cell P2, as shown in the following figure.
Next, create a staging table that consists of all formulas. In this staging table, you use Excel’s CHOOSE function to select the correct value from the raw data tables based on the selected region.
In Excel, the CHOOSE function returns a value from a specified list of values based on a specified position number. For instance, the formula CHOOSE(3,”Red”, “Yellow”, “Green”, “Blue”) returns Green because Green is the third item in the list of values. The formula CHOOSE(1, “Red”, “Yellow”, “Green”, “Blue”) returns Red.
As you can see in the following figure, the CHOOSE formula retrieves the target position number from cell P2 (the cell where the list box outputs the index number of the selected item) and then matches that position number to the list of cell references given. The cell references come directly from the raw data table.
In the example shown in the figure above, the data that will be returned with this CHOOSE formula is 41767. Why? Because cell P2 contains the number 3, and the third cell reference within the CHOOSE formula is cell B9.
You enter the same type of CHOOSE formula into the Jan column and then copy it across, as shown in the following figure.
To test that your formulas are working, change the value of cell P2 manually by entering 1, 2, 3, 4, or 5. When the formulas work, all that’s left to do is create the charts using the staging table.
If Excel functions like CHOOSE or INDEX are a bit intimidating for you, don’t worry. There are literally hundreds of ways to use various combinations of Form controls and Excel functions to achieve interactive reporting. There are no set rules on which Form controls or Excel functions you need to use in your model.
Start with basic improvements to your dashboard, using controls and formulas you’re comfortable with. Then gradually try to introduce some of the more complex controls and functions. With a little imagination and creativity, you can take the basics and customize your own dynamic dashboards.