Create INTERACTIVE Excel Dashboards With Option Buttons | How to use Radio Buttons
Create INTERACTIVE Excel Dashboards With Option Buttons | How to use Radio Buttons
Radio buttons or Option buttons in Microsoft Excel are a great way to add interactivity. In this video I’ll show you how you can allow a user to choose the data they want to see on a chart. You’ll learn to switch the chart view based on user selection. I’ll show you how to add the developer tab to Excel, how to use form controls and how to group radio buttons together in case you have more than one set of option buttons.
We’ll also cover how to use the FILTER and the SWITCH functions in Excel for the data preparation. And finally I’ll show you a trick to make your chart range dynamic to avoid gaps or missing data in the chart. This way your chart range and chart axis adjusts based on the size of your data.
★★★ My Online Excel Courses ► https://www.xelplus.com/courses/
00:00 How to Create an Interactive Chart with Radio / Option Buttons
00:14 How to Insert Option Buttons in Excel
04:07 Prepare the Raw Data for the Chart
06:38 Insert and Format The Chart
08:16 How to Make Chart Ranges Dynamic
12:14 How to Create Dynamic Chart Titles
13:27 Wrap Up
⯆ Download the Workbook from here: https://www.xelplus.com/excel-dashboa…
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
GEAR I use: https://www.xelplus.com/resources/#gear
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel #dashboard
Content
0.08 -> Today, you're gonna learn how you can switch
between Charts with a Radio Button in Excel.
4.32 -> This is an easy trick that you can use when
you're setting up interactive dashboards.
14.08 -> Okay, so I have the sales data for different
apps by division. I want to create radio buttons
18.88 -> for the division, this way I can control
what sales data shows up in the chart.
23.52 -> Now, as you can see, my source data is
unfortunately messy. Before I create the chart,
29.04 -> I need to clean up the data that I want in
the chart. I don't want to change my source,
34.88 -> I just want to add an in-between table that gets
the job done. This is going to be my data prep.
42.24 -> But before I get my hands dirty,
let's add our radio buttons.
46.8 -> To do that, you need the Developer Tab.
So if you don't see this Developer Tab,
51.44 -> you can easily add it to your Ribbon. Just
Right-Mouse Click, go to Customize the Ribbon,
56.72 -> place a Checkmark beside Developer, and then
click on OK. Then you're going to see this,
62.24 -> and then you can go ahead and insert your radio
buttons, which you can do from Insert Controls
67.92 -> under Form Controls. Here is the radio button,
or the Option Button. Now just click on it
75.76 -> and then draw it out here. Click inside and let's
overwrite this, so instead of Option Button 1,
82.48 -> I want to have Game here. So, this is one of the
divisions that I have. Then I want to add two
89.2 -> more option buttons, one for Utility and
another for Productivity. So here, I can
94.56 -> hold down Control, so I'm going to click on this,
hold down Control, and then drag this to the side.
101.2 -> That's going to create a second copy, and now
a third copy. Okay, so select the second one.
107.36 -> You can also Right-Mouse Click and Edit Text if
that's easier. Then let's go with Productivity,
114 -> and last one is Utility. Okay, so I have these
set up, and let's just arrange them wherever
121.44 -> we want to have them in a report. Now, what
happens when I click on one? I get this black
127.36 -> dot in the middle, and notice that it's smart; it
knows that they belong together. I can't select
133.92 -> two of these at the same time. Now, the reason
it thinks that they belong together is because I
140.4 -> don't have them inside a group. So, whenever
I add another option button to this sheet,
147.28 -> it's automatically going to belong to this set. If
I want to create different sets of option buttons,
153.28 -> I need to put them inside a grouping that looks
like this, so that's a Group Box. If you click it,
160 -> drag it out here, and carefully place
your option buttons in a Group Box,
166.24 -> then they're going to be independent to other
option buttons that you might have on that sheet.
172.08 -> Now, in this case, I don't need this grouping
because these are going to be the only option
177.2 -> buttons I have on this sheet. Now, after I add
these, the next step is to select one of them.
183.84 -> Right-Mouse Click, go to Format Control, and add
a Cell Link to it. This way we know what option
190.32 -> button has been selected. So, where are we going
to put the cell link? Let's put it in Cell F1.
196.8 -> So first, I'll link here. I'm just going to go
and click on F1. That's going to put the results
201.6 -> of this option button. The result is whatever
is selected. So notice when I'm on Game, we
207.76 -> see one. When I'm on Productivity, we see two.
I'm just going to bring this to this side here,
214.56 -> so that we can easily see what's selected. Then
when I click on Utility, we see three. Now, how
220.64 -> did it add a Cell Link to these when I only added it
to Game? Well, because they automatically belong
227.52 -> together, If you Right-Mouse Click on any other
one, go to Format Control, you're going to see F1
234.08 -> in the other ones as well, right? So this was
automatically added when we added the cell link
241.28 -> to one of these option buttons. That saves us the
work of having to do this for each of these. Okay,
248 -> so far so good. Next step in the process is
to grab the data that I need. So, if someone
253.36 -> selects Game, I need the App and Sales of
the Game division here. How do I do that? I can
259.92 -> use the FILTER Function. Let's add the headers here.
So, I want App, and I want Sales. Now, right
266.4 -> here we're going to start off with FILTER. This
is a function in Office365. It needs an Array.
271.68 -> The Array is the result that I want to get back.
In this case, I want the App Column and the
277.2 -> Sales Column, so I'm going to select those. The
Include Argument is what I want to have included.
282.56 -> This is similar to the logical test that you do in
the If Statement, except that we can reference
289.44 -> an entire column and not just a single cell. So
here, I want to check whether Division equals
297.2 -> the Division that's selected here. Now, what's the
problem? Well, Division here is reflected as a
303.12 -> number, because Game was the first option button
that I inserted, it gets a value of one. When I
309.92 -> select Productivity, it was the second option
button, it gets a two. I need to somehow translate
316.32 -> these numbers into the Divisions. So, if it's one,
I want Game. There are different ways you can do
322.8 -> this. One function that I haven't yet covered
on the channel is the SWITCH Function. So,
329.28 -> that's the one that we're going to use in
this case. This function is really easy to
333.12 -> use. You can tell it to switch one value for
another value. All you need is an expression.
339.84 -> This is F1. This cell has the value that I want to
switch. Next is the value itself. So, if it's a 1,
347.36 -> what do I want? I want the result to be Game.
If it's 2, I want it to be Productivity. And if
355.04 -> it's 3, I want it to be Utility. I'm not sure if
I spelled everything correctly, we're going to see
362.08 -> in a second if the formula works correctly or not.
So, I'm going to close the bracket for SWITCH,
367.2 -> close it again for FILTER, press Enter,
and this works! This is Arcade. It's the
374.08 -> first game app. Then I have deRambler, Fightrr,
and so on. And when I switch to Productivity,
379.92 -> I get the App and Sales for the
Productivity Division and Utility.
384.96 -> Okay, so far so good. Let's also
add some number formatting to this.
390.4 -> It works great. So if I wanted a dynamic table,
I'd already have one, but I want to go beyond
396.56 -> this. I want to add a Chart. Let's highlight the
Range. So, I'm going to include the headers. Here,
402.72 -> go to Insert, and let's insert a Bar
Chart. Right her we have a 2D Bar. Okay,
409.6 -> so I'm going to place it right here. Let's
organize these a bit better, just holding down
415.44 -> Control, selecting these, and let's bring them a
bit over, so it's above my Chart. Let's quickly
422.8 -> adjust some settings. I'm going to remove the
Labels here. Right-Mouse Click on the Data Bars,
428.8 -> and add the Data Labels to these. Okay, let's also
remove the Grid Lines. Okay, so far so good. What
437.28 -> do we have here? We have Twistrr showing
up first, then Perino, Kryptis, Fightrr,
442.56 -> and so on. Now, it would be nice if we could
automatically sort this. That's easy to do,
449.28 -> because we can use the SORT Function here. Right
after the equals, we're going to add a SORT.
455.36 -> The Array is the result that we have
from FILTER, which is all of this,
460.72 -> and then we can specify which column we want
to sort. This is the first column, and then
466.4 -> the second column. So, that's easy. I'm going to
go with the second column, and then we can decide
471.68 -> if I want it in ascending and descending order.
Now, notice because Excel shows the last app here
478.96 -> first, I want to go in this case with ascending.
So, with a 1, Close Bracket, press Enter,
486.48 -> and now we get everything properly sorted,
right? Because the biggest one is going to end
492 -> up last year, which is going to show up first
in the chart. Okay, so this looks great. Now,
497.12 -> let's switch to Productivity. That's good, and
Utility. It looks like we have a problem here.
503.68 -> Utility has less apps than Game, so we get
a gap right here, and actually Productivity
512.16 -> has more apps than Game, so we are missing
something. We are missing that last app here.
519.84 -> If your data has the same number of categories,
you don't have to do anything right. You'd be done
526.56 -> right now at this point. But because we have
this issue of varying number of categories,
532.48 -> we need to take this a step further. We need to
make our chart smarter, so it can detect the range
538.24 -> it needs to plot. Now, Chart Ranges unfortunately
can't take Direct Formulas. They can only take
544.16 -> Cell References. If we Right-Mouse Click, go to
Select Data, this is our Sales Value. So,
550.56 -> if I go to Edit, we can see the Series Values are
these. This is a Direct Cell Reference. I cannot
557.12 -> add a formula here, or I can't use Hash to get
Dynamic Ranges. Instead, I need to use the Name
565.2 -> Manager to create a dynamic range and then here I
can use the name. I know it sounds a bit complex,
572 -> but it's actually quite easy to set up. We're
just going to go to the Name Manager. So let's
577.04 -> go to Formulas, Name Manager. Right here, we're
going to add a new name. The first dynamic range
583.6 -> can be for our Values, and the second one is going
to be for our Apps. Right, because our chart has
590.08 -> both values and apps. Both of these ranges
need to be made dynamic. So for the first one,
596.4 -> I'll just call it myVal for
values, and for the dynamic range,
601.6 -> well, I want to take advantage of the spilling
right of this hash, so I'm going to go to E3#,
608.72 -> which is going to take the entire range. So if
we just take a look at this, if I click on OK,
614.32 -> and then I click on this, notice that this
area is selected - it's taking the whole range.
619.84 -> But I don't want the whole range. Instead, I just
want the Sales Column. So let's go and edit this
626.64 -> and then put this inside the INDEX Function.
That's my whole range. Now in the INDEX function,
633.12 -> after we specify the range, we have the Row
Argument. Well, I want to include every single
639.28 -> row here, so we can skip that. The next argument
in INDEX is the column number. I want the sales
646.88 -> column, so that's the second one in this range
here, right? So that's it. Now I have a dynamic
653.52 -> range that references the sales column. So I'm
going to click on OK, and when I click on this,
658.96 -> it's only this column. To create the dynamic
range for app, that's really easy. I'm just
664.32 -> going to copy this, click on New, call it myApp,
paste the Formula here. The only thing I need to
672.24 -> change is this right? I want the first column
instead of the second column. Click on OK. OK,
678.16 -> so now I have myApp and myVal. Click on Close.
Let's go back to our chart. Right-Mouse Click,
686.4 -> select Data. For the series, Click on Edit. For
series values, here you need to carefully
693.52 -> delete the Range Reference, keep the Sheet Name
(so my sheet is called Data), keep that name,
699.76 -> and this is going to be myVal, and click on
OK. Let's go and update the categories as well,
706 -> so that's the horizontal axis. Click on Edit,
carefully delete that last portion where we
712.24 -> have the cell referencing, and put in myApp.
Click on OK and click on OK. Now let's test this.
720.4 -> Click on Productivity. Do we get everything? Take
a look at this - our range updated WenCaL
726.88 -> is there. Click on Utility, our range got smaller.
So we created a fully dynamic chart range.
734.72 -> Now, as a last step, just to take this one
step further, let's also create a dynamic
740 -> chart title. Now, just like chart ranges,
chart titles can't have formulas in them,
745.92 -> but you can reference the cell that has a
formula. So somewhere to the side of your report,
751.36 -> you can already write the function that you need.
So we're going to go with "=Sales for Division:". We
758.72 -> have to put these in quotation marks. I'm going
to put a Column, Space, and Quotation Mark, and I
765.36 -> want to combine it with this. But here, I'm going
to use the SWITCH Function. So, since I've already
771.44 -> written that out here, let's go and cheat and copy
this part of the Function. Press Escape to leave
778.96 -> to this part here. So, right after the quotation
mark, we're going to connect this with an "&", and
785.68 -> add the SWITCH Function. Click on Enter, we have
Sales for Division: Game. Now, I need to connect
792.64 -> this to this, so click on the Chart Title, then
go to the Formula Bar, type in an Equal, and
799.12 -> select the cell where you wrote the formula.
Now we have a fully dynamic chart title as well.
807.04 -> I hope you found this trick useful. Now
you can apply this technique to other
810.48 -> types of form controls as well, not just radio
buttons. Before you leave, hit that thumbs up,
816.32 -> subscribe if you aren't subscribed, and
I'm gonna see you in the next video.
Source: https://www.youtube.com/watch?v=oSTgFUiRDGI