Hi. In this Excel tutorial, we’re going to go over a couple of ways to make lists in Excel where users can select items from. We'll first create a
dropdown list and then we’ll do a List Box. We’ll do this using Form Controls. We’ll then use the item chosen to calculate a discount.
And, if you you want to also learn how to create dropdown lists using the Data Validation options, please take a moment to watch our
tutorial on that. But, first, what are Form Controls? So, Form Controls in Excel are objects that make it easier for users to select
data. So, in order to access the Form Controls, you'll need the Developer tab. If you don't have it, simply right-click on your Ribbon -
up here, the gray area at the top, and select Customize Ribbon.
From here, it's probably easier to select All Commands, look for Developer on the left-hand side,
select the Add button to move it to the right-hand side, and then click OK when you're done.
So, from the Developer tab, in the Control section, click on Insert. And for our first example, we're
going to use the Combo Box to create a drop down list.
So, after you click on Combo Box, your cursor will change to resemble a plus sign. Drag that to form a rectangle with the longer side
horizontal - and, if you drag while holding down the Alt key, your box will automatically snap into the cell. When your box is selected, you'll
see these little circles around it, letting you know that it is selected. If you accidentally unselect the box, you can just right-click on it
to select it again. So, next, you'll want to right-click on it and select Format Control.
In the Control tab that comes up, we'll need to enter a few items. So, the Input Range will be the range of cells from which users can select
from. In this case, it'll be our Discount Percents here. Select that...
So, let's move this box over for a minute. So, the next field we need to fill out is the Cell Link. So, when a user selects one of the items on
the list, Excel will return the number of the position of that item in the list. For example, 15% is the third item listed, so Excel will
return the number 3. The Cell Link will be the cell in which you want for Excel to display this number. In this case, I've already designated a
cell for this. So, I'm going to select that cell. Also, just FYI, the Input Range and Cell Link can be in a different sheet. I just have it in this
one to make it easier to see. So, the Drop Down Lines will be the number of lines in your drop down menu. For this example, I'm going
to enter 6 because that's the number of options that I have to select from. However, if you enter
a number less than the number of items available,
not all items will display for selection. So, you'll want to be careful of that. And, if you enter a number that's larger than the number of
items available that's not really a big deal, it'll just adjust. So, then, when done, click OK.
So, this creates a drop down box. But we still need to translate the numerical result that we got from it into something more user-friendly.
So, we'll use the INDEX function for that. Now, the INDEX function uses the position number of an
item in a list and returns the contents in that location.
So, it'll help us get to the item chosen from our dropdown. So, we'll type it here, next to Discount %, where we can then use it to
calculate the amount due after the discount. So, we'll type =INDEX and then select
our list, comma, and then we'll need to reference the result from our dropdown.
In our second example, we'll create a List Box where an item can be chosen from. And, by the way, if you find this tutorial helpful, don’t forget
to Like the video and subscribe to the channel for even more tips. So, in a List Box, we can select items kind of like with the dropdown box,
except that there's no dropdown, and the List Box does not automatically resize,
so you'd need to resize it to fit your selections and your sheet settings.
So, back in the Developer tab, in the Control Section, click Insert. This time, select the List Box from the Form Controls.
And when you see your cursor look like a plus sign again, simply draw out and rectangle, but this time we'll be going where the longer side is
vertical. And, with it selected, right-click and select Format Control. So, the Input Range will again be the Discount Amount, but this time
we're going to select the Named Range I've assigned to it - and you can use Named Ranges in the drop down options also, but I
just wanted to show you both ways of selecting your Input Range - and for the Cell Link, we'll select the cell that I've already
designated for this, and, in this example, we're only going to select a Single Option, and click OK.
I'm going to make my box a little bit smaller
and again use the INDEX function
to get to my result, but this time I'm going to use my Named Range in my INDEX formula.
and this way, like with the dropdown box, when someone selects a certain percent, you'll see the Total Due discounted to reflect that
percent off. So, I hope you enjoyed this tutorial on how to make lists in Microsoft Excel using Form Controls. If you did, please make sure to
to Like the video and don’t forget to subscribe to this channel. Thanks!