How to Edit Macros in Excel's Visual Basic Editor
Save Custom Functions from Excel VBA in Add-in Files
How to Add a Control to Excel Dashboards and Reports

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.

image0.jpg

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.

image1.jpg

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:

image2.jpg
=IF($A$8=1,B9,IF($A$8=2,B13,B17))

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.

blog comments powered by Disqus
How to Assign Macros to the Ribbon and the Quick Access Toolbar in Excel 2013
VBA Functions for Excel VBA Programming
3 Reasons to Use Macros for Your Excel Dashboards
Enable and Trust Macros for Your Excel Dashboards and Reports
Where Is the Visual Basic Editor in Excel 2013?
Advertisement

Inside Dummies.com