Excel Interactive Dashboards with Radio Buttons (Option Buttons)

Interactivity is the ultimate feature when working with dashboards.

Having the ability to change the story based on the need of the moment saves report viewers time by not having to juggle multiple separate reports.

Switching from one report to another is not difficult, but what if the viewer of the report could click a button and have the report display a completely different set of information?  This feature could be extended to switching between different charts to showcase different aspects of the data.

This post will show you how to create a single report that can switch between various charts using Option Buttons.

This trick will not only become one of your most favored dashboarding features but it will become one of the most requested features by your report viewer.

We begin this journey into the Wonderful World of Interactivity with a sample data set that occupies range A1 through C19.

We want to create Radio Buttons for each Division.  These buttons will be used to control which data is displayed in the chart.

Bringing Order to Chaos

Our data is a bit messy, so we will begin by cleaning up the data to make it more “chart-friendly”.  This will be accomplished by creating what is known as a “data preparation table”.  This table acts as a go-between to take messy data and transform it into something more conducive to charting.

Start by entering a title in cell E1 labeled “Data Prep”.

Setting Up the Option Buttons

Next, we’ll create the three buttons used to control the data preparation area.

The buttons will change the prep area and the chart will be built from that same prep area.  This will give the user the illusion of the buttons changing the chart when really there is a go-between service behind the scenes.

The Option Buttons are accessible via the Developer tab.

If you do not see the Developer tab at the top of your program, right-click on any visible ribbon and select “Customize the Ribbon…”.

In the Excel Options dialog box, place a check in the box next to “Developer” on the righthand list and click OK.

With the Developer ribbon displayed, in the Controls group, select Option Button in the upper group of buttons labeled Form Controls.

After you click the Option Button control, click and drag a small rectangle somewhere on the screen to add your first option button.

Rename the button to “Game” by clicking within the text of the option button and rename the text.

We want two more Option Buttons, one for “Utility” and one for “Productivity”.

Right-click on the “Game” option button and select Copy.  Click the Paste button twice to produce two duplicates of the “Game” button.  Place the buttons in some temporary arrangement.

PRO TIP:  You can hold the CTRL key and click the first option button to select it, then continue holding the CTRL key and drag the button to a new location and release the mouse.  This will execute the mouse version of a Copy/Paste operation.

Rename the option buttons so each button represents a specific Division.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

Option Button Interactivity

When you click one of the option buttons, the empty circle is filled with a black dot.  This indicates a selection has been made.

If you select a different option button, the currently selected button will be deselected in favor of the new choice.

If you were to place additional option buttons on the sheet, they would work in unison.

But suppose you wanted two groups of option buttons that serve unrelated purposes (ex: shipping method and payment method)?

To inform Excel as to which option buttons should work together, you surround the related option buttons with a Group Box control.

Storing the Selected Button’s Result

When an option button is selected, we need to store a value in a cell to indicate which is the active button.

Right-click on one of the option buttons and select “Format Control…”.

In the Format Control dialog box, on the Control tab, set the Cell Link option to point to cell F1.

When we select one of the option buttons, a number between 1 and 3 is displayed in cell F1.

It is not necessary to set the Cell Link option for the other buttons.  As they are all automatically understood to be related, the cell selection of F1 has been assigned to all of the buttons.

Creating the Data Preparation Table

The next objective is to construct a table based on the user’s selection when using the option buttons.

This table will be a filtered version of the original table but only as it pertains to the selection Division.

If you are using Microsoft 365’s offering of Excel, you have access to the amazing new FILTER function.

Start by creating headings for our prep table.  Enter the words “App” and “Sales” in cells E2 and cell F2 respectively.

In cell E3, enter the following formula:

=FILTER(B2:C19, A2:A19 = SWITCH(F1, 1, “Game”, 2, “Productivity”, 3, “Utility”) )

The SWITCH function takes a value (cell F1) and checks it against a list of items.  If the value is in the list, the value is switched with a different value.  In this case, numbers are placed with words (Divisions).

To make the values in the “Sales” column appear more readable, we’ll add a comma style with no decimal places.

Visualizing the Data

Everybody loves a good chart, especially an interactive chart.

To create the interactive chart, select the cell range holding the results (E2:F9) and insert a 2-D Clustered Bar chart.

Let’s alter a few of the cosmetic aspects of the chart to make it easier to read.

  • Move and size it next to the data prep area
  • Move the option buttons to be near and above the chart
  • Remove the Horizontal Axis
  • Add Data Labels to the bars
  • Remove the Vertical Gridlines
  • Set the Shape Outline of the chart to “no outline

Sorting the Chart

As the data is presented in alphabetical order (from the bottom – up), it would likely be better to display the information in performance order (i.e., largest to smallest sales).

This is easily done by nesting the existing FILTER/SWITCH functions inside a SORT function.

=SORT(FILTER(B2:C19, A2:A19 = SWITCH(F1, 1, “Game”, 2, “Productivity”, 3, “Utility”) ), 2 )

The SORT function is telling the results of the FILTER/SWITCH to be sorted in ascending order by the 2nd column, the “Sales” column.

Because the 2-D Cluster Bar chart naturally plots from the bottom – up, we get the best sales at the top then descending in sales to the bottom.

“All is not well in the Kingdom”

When testing the various Divisions, we observe that when selecting a Division with fewer Apps, our chart has unwanted gaps towards the top of the vertical axis.

What may not be so obvious is that if a Division has more Apps than were originally charted, those additional Apps will not be displayed.

This is a huge problem.

To solve this problem, we need to make our chart smarter so it can dynamically detect the range it needs to plot.

Unfortunately, chart ranges will not accept formulas or Dynamic Array Hash references, only cell references.

A workaround to this limitation is to use the Name Manager to act as a translator.

All excel courses Leila Gharani

Master it ALL!

Whether you're a beginner or advanced Excel user, there is a course for you in our academy

GET ACCESS

Create a Named Reference for the Data Prep Results

Named Range for “Sales”

To create a named range that will point to the data prep area’s “Sales” results, perform the following steps:

  1. Select cell F3
  2. Click Formulas (tab) -> Defined Names (group) -> Name Manager
  3. In the Name Manager dialog box, click New
  4. In the New Name dialog box, enter the following information:

The formula in the “Refers to:” field reads as follows:

=INDEX(Data!$E$3#, , 2)

The $E$3# hash reference says to get all the results of the formula in cell E3, but the INDEX function effectively reduces the results to only display the 2nd column of the results.

Named Range for “Apps”

To create a named range that will point to the data prep area’s “Apps” results, perform the following steps:

  1. In the Name Manager dialog box, click New
  2. In the New Name dialog box, enter the following information:

The formula in the “Refers to:” field reads as follows:

=INDEX(Data!$E$3#, , 1)

The $E$3# hash reference says to get all the results of the formula in cell E3, but the INDEX function effectively reduces the results to only display the 1st column of the results.

Assigning the Named Ranges to the Chart

To assign the newly created named ranges as data sources for the chart, right-click anywhere on the chart and choose “Select Data…”.

Making the “Sales” Dynamic

Above the left panel labeled “Legend Entries (Series)”,  select the “Sales” entry and click “Edit”.

In the Edit Series dialog box set the “Series Values” option to read as follows:

=Data!myVal

NOTE: Make sure you retain the sheet name and only replace the cell range reference.

Making the “Apps” Dynamic

Above the right panel labeled “Horizontal (category) Axis Labels”,  click “Edit”.

In the Axis Labels dialog box set the “Axis Label Range” option to read as follows:

=Data!myApp

NOTE: Make sure you retain the sheet name and only replace the cell range reference.

Testing the Chart Range

If we click through the Divisions using the option buttons, we see that the proper range of data is selected for each Division.

Creating a Dynamic Chart Title

Let’s put a cherry on top of this amazing, dynamic chart by creating a chart title that changes with the user’s Division selection.

NOTE: Just like chart ranges, Chart Title elements can’t have formulas stored in them.  We can get around this limitation by creating a formula (i.e., logic) in a cell off to the side, then use that cell’s address in the Chart Title element.

We will create a formula that uses the same SWITCH function logic as before to select the Division based on the selected option button.  We’ll enhance it by concatenating some descriptive, fixed text to the beginning of the SWITCH function.

Select a cell somewhere off to the side of the report and enter the following formula:

=“Sales for Division: ” & SWITCH(F1, 1, “Game”, 2, “Productivity”, 3, “Utility”)

Next, select the Chart Title element in the chart.  In the Formula Bar, enter the cell address of the formula you just created.

When we switch between Divisions using the option buttons, our Chart Title element reflects the selected Division.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE