Microsoft Office Tutorials and References
In Depth Information
Using the Option Button Control
Now, you could create three separate charts and show them all on your dashboard at the same time.
However, using this technique as an alternative saves on valuable real estate by not having to show
three separate charts. Plus, it’s much easier to troubleshoot, format, and maintain one chart than
To create this example, you start with three raw datasets (as shown in Figure 12-15) that contain
three categories of data; Income, Expense, and Net. Near the raw data, you 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.
Figure 12-15: Start with the raw datasets and a cell where the option buttons can output their values.
You then create the analysis layer (the staging table) that consists of all formulas, as shown in Figure
12-16. The idea is that 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 B1 is not 1 or 2, the value in cell B17 is returned.
Figure 12-16: Create a staging table and enter this formula in the first cell.