Let’s dive into making our Excel sheets interactive! We’ll start with a small group of data from cells A1 to C19.

Our goal is to set up Excel Radio Buttons for different divisions. These buttons will let us choose what data shows up in our chart.

Let’s tidy up our messy data to get it ready for a chart. We’ll make a simple ‘prep table’ that helps turn our jumbled information into something easy to chart.

First, go to cell E1 and type in ‘Data Prep’ as the title.

Insert Radio Button in Excel

Next, we’ll add three option buttons to help us manage the data before it gets to the chart.

Think of these option buttons as magic wands: they don’t change the chart directly. Instead, they rearrange our prep area, and then our chart updates based on this neat, organized data.

To find these ‘Option Buttons,’ go to the ‘Developer’ tab on the Excel ribbon.

Insert radio button in Excel

Can’t see the ‘Developer’ tab?

Just right-click any tab you do see, choose ‘Customize the Ribbon…’, and you can turn it on from there.”

Next, let’s add the buttons to control our chart:

On the Developer tab, look for the ‘Controls’ section. Click on the ‘Option Button’ under ‘Form Controls.’

Insert radio button in Excel

Click on your sheet and drag to draw a button.

Insert radio button in Excel

This will be our first button, and we’ll call it ‘Game.’ Just click on the radio button’s text to change its name.

Insert radio button in Excel

We need two more buttons named ‘Utility’ and ‘Productivity.’

Easy trick: right-click on the ‘Game’ button, choose ‘Copy,’ then paste it twice. You’ll have two more buttons to rename and arrange as you like.

💡Quick Tip: Want to make a copy of your option button fast? Here’s how:

  1. Hold down the ‘CTRL’ key on your keyboard.
  2. Click on the button you want to copy.
  3. Still holding ‘CTRL’, drag the button to where you want the copy to be and let go of the mouse. Just like magic, you’ve got a copy!

Next, make sure each button is named after a different Division by clicking on them and typing the new names.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

Connect Radio Buttons with Group Box in Excel

When you click an option button in Excel, it shows you’ve made a choice by filling in with a black dot. If you click a different button, the first one clears, and the new one fills in. This is how you know which option you’ve selected.

What if you have two sets of buttons for different things, like choosing a shipping method and a payment method? You wouldn’t want clicking one to affect the choices in the other group.

To keep them working separately, you can use a ‘Group Box’ control to draw a box around each set of related buttons. This tells Excel they’re a group, and clicking buttons in one box won’t change what’s selected in the other.

Group box in Excel
Combining Excel radio buttons with group box

Linking Excel Radio Buttons to a Cell

To remember which option button you picked, we can make Excel show the choice in a cell.

Here’s how:

Right-click any button and choose ‘Format Control…’.

In the Format Control window that pops up, go to the ‘Control’ tab and link the button to cell F1 by setting the ‘Cell Link’ to F1.

Now, when you click a button, Excel puts a number (1, 2, or 3) in cell F1 to show which button you selected.

Don’t worry about doing this for each button; once you set it for one, Excel knows they’re all connected and does the rest.

Creating the Data Preparation Table

Next, let’s make a table that changes based on what you choose with the Excel radio buttons. This new table will show only the info related to your chosen division.

If you’re using Excel from Microsoft 365 or Office 2021, you can use the FILTER function to do this.

First, we need to set up our table.

Put “App” in cell E2 and “Sales” in cell F2 to start our headings.

In cell E3, enter the following formula:

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

The SWITCH function in Excel looks at a cell (like F1) and matches its content against a list you give it. If it finds a match, it swaps that content for something else you specify.

So, you can replace numbers with words, like turning division numbers into their names.

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

Combining Excel Radio Buttons with Charts

Using Excel radio buttons with charts

Everyone likes charts that you can interact with. Here’s how to make one:

  1. Pick the cells from E2 to F9 from the data prep table.
  2. Insert a 2-D Bar chart.

Now, let’s tweak the chart to make it clearer:

  • Drag the chart next to where your data is set up.
  • Place the option buttons above the chart so they’re easy to reach.
  • Take off the bottom (Horizontal) Axis – you don’t need it.
  • Put labels on the bars so you know what each one shows.
  • Get rid of the lines (Vertical Gridlines) running across the chart.
  • And finally, remove the chart’s border by setting the Shape Outline to ‘no outline’.

Sorting the Chart for Better Insights

Right now, our chart shows data in alphabetical order, starting from the bottom. But, what if we want to see which sales are the biggest right away? We can rearrange our chart to show sales from largest to smallest.

To do this, we wrap our current FILTER and SWITCH functions with a SORT function. This tweak sorts our chart’s data just the way we want.

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

The SORT function rearranges our data so that the sales figures, which are in the second column, go from smallest to biggest.

And since our 2-D Bar chart shows things from the bottom up, the highest sales end up at the top, with the rest following in order down to the lowest.

Chart Gaps and Missing Apps Issue

When we switch between different divisions to see their sales, sometimes our chart doesn’t look right. If a division has fewer apps, we see gaps at the top. And if it has more apps than we expected, those extra ones don’t show up at all.

This isn’t good for our analysis.

The chart needs to adjust automatically to show exactly what we need, no matter how many apps there are.

But, charts in Excel can’t directly use formulas to change what they show.

Here’s a clever fix: we can use the ‘Name Manager’ in Excel to help our chart understand and adjust to the number of apps it needs to display.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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. Then, we add a trick with the INDEX function to make it show only the sales column.

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

❗Remember: When you’re updating the formula, keep the sheet’s name as is. You only need to change the part that specifies the cells you’re working with.

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 give our dynamic chart a cool feature: a title that changes when you pick a different Division.

Quick heads-up: We can’t put a formula directly into a chart title. But we’ve got a workaround! We’ll write a formula somewhere else in the sheet, and then tell the chart title to follow that formula.

Here’s what to do:

  • Pick a cell out of the way and write the formula below. This formula will use the SWITCH function to change the title based on what Division you select. We’ll add some extra text to make it clear and descriptive.
=“Sales for Division: ” & SWITCH(F1, 1, “Game”, 2, “Productivity”, 3, “Utility”)
  • Click on your chart’s title. Then, in the Formula Bar at the top, type in the address of the cell with your formula.

Now, when you choose a different Division with the Excel radio buttons, the chart title updates to match your selection!

Download the Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.