Excel Dashboards and Reports: Option Button Example
One of the ways you can use option buttons in your Excel dashboards and reports is to feed a single chart with different data, based on the option selected. This figure illustrates an example of this. When each category is selected, the single chart is updated to show the data for that selection.
Now, you could create three separate charts and show them all on your dashboard at the same time. However, using option buttons as an alternative saves valuable real estate by not having to show three separate charts. Plus, it’s much easier to troubleshoot, format, and maintain one chart than three.
To create this example, start with three raw datasets — as shown in the following figure — that contain three categories of data; Income, Expense, and Net. Near the raw data, reserve a cell where the option buttons output their values (cell A8, in this example). This cell contains the ID of the option selected: 1, 2, or 3.
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 formula:
This formula tells Excel to check the value of cell A8 (the cell where the option buttons output their values). If the value of cell A8 is 1, which represents the value of the Income option, the formula returns the value in the Income dataset (cell B9).
If the value of cell A8 is 2, which represents the value of the Expense option, the formula returns the value in the Expense dataset (cell B13). If the value of cell A8 is not 1 or 2, the value in cell B17 is returned.
Notice that the formula shown in the preceding figure uses absolute references with cell A8. That is, the reference to cell A8 in the formula is prefixed with $ signs ($A$8). This ensures that the cell references in the formulas don’t shift when they’re copied down and across.
To test that the formula is working fine, you could change the value of cell A8 manually, from 1 to 3. When the formula works, you simply copy the formula across and down to fill the rest of the staging table.
When the setup is created, all that’s left to do is create the chart using the staging table. Again, the two major benefits you get from this type of setup are that you can make any formatting changes to one chart and it’s easy to add another dataset by adding another option button and you can edit your formulas easily.