Hi. In this Excel tutorial we’ll add Option Buttons to filter a chart as well as make the chart interactive. Before we start, make sure
you have the Developer tab available. If you do not, just right-click anywhere on your Ribbon and select Customize the Ribbon and,
under Main Tabs, select Developer and click OK.
On my screen is data on average daily temperatures for 4 weeks and a corresponding Line Chart. To add more
interactivity to the chart, we can add Option Buttons, or Radio Buttons, to filter the chart. We're
going to do this by assigning a macro to each button - but don't worry, I promise it'll be easy!
First, let's add a Group Box below the chart. You'll find this in the Developer tab. Click Insert and select Group Box
And we'll draw it out just below our chart. And this is going to help us better visually organize our Option Buttons. And it's especially helpful if
you have multiple Form Controls on your sheet, it'll make it feel more organized to group related items together.
So, let's add our button. Back in the Developer tab, in the Control section, click Insert again
and select the Option Button.
And inside our Group Box, simply draw out an Option Button.
And then, with the Button selected, right-click on it and select Copy. And we'll make 4 copies of this.
We should have 5 buttons in the end. Now, let's line up our buttons. And, by the way, if you prefer to have them snap into a cell, simply
hold down the Alt key while you drag them and that will help them
snap into a cell.
We'll make the Group Box a little bit bigger.
Next, we'll right-click on each one, select Edit Text and we'll change these to read Week1, Week2,
and so on,
with the last button
reading All Weeks.
And, by the way, if you’re finding this tutorial helpful, don’t forget to Like the video and subscribe to the channel for more Excel tips!
Next, we'll create a macro that will update the chart to show only the data for the week selected. So, I find this easier to do with an
existing chart, which is why I created this Line Chart ahead of time. Plus, if you record a macro that creates a new chart each time,
you might get a "flashing" effect on your screen every time you select an option and that can be distracting.
So, back in the Developer tab,
this time we'll select Record Macro.
And the first one will be for Week1 and we'll store it in this workbook - this way no matter who I send this file to they'll be able to use it as
well. So, click OK to begin recording. And, with the chart selected, I'm simply going to revise the data area only for Week1.
And then I'm going to place my cursor
just in a cell outside of the chart because, otherwise, after the macro runs, the chart data
will still be highlighted. And then we'll click Stop Recording. Then we'll repeat this
for the remaining weeks,
with the one for all the weeks
including the data for all weeks.
Then, once your macros have been recorded, select each Option Button, right-click on it and
assign a macro to it. So, for the Week1 button we'll assign the Week1 macro,
the Week2 macro to the second button, and so on.
And, now, you can filter your chart simply by selecting a button.
So, I hope you enjoyed this tutorial on how to add Option Buttons to filter a chart in Microsoft Excel. If you did, please make sure to Like the
video and don’t forget to subscribe to this channel. Thanks!