Hello
guys, welcome to this tutorial on “how to create a dynamic drop down list in
excel”. Some of you may have tried to create a drop down list in excel using
the data validation tab. It may be for different purpose. In this tutorial, you
learn how to create a basic drop down list in excel and I will also show you
how to create a dynamic excel drop down list to save your time.
So, let’s get started…………………..
I have here sets of data
we are going to use for the purpose of this presentation. I will try to explain
it to you as simple as possible to help you better understand.
How
to Create a Basic Dropdown list using Data Validation in Excel
The first sheet is used
to show you how to create a basic dropdown using the data validation tab in
excel. We have two sets of data here, the Cellphone Brand and the Model of
phones available.
Highlight the Brand
Column (Cell B15:B21). Go to DATA > Data
Validation > Data Validation. A dialog box will open. On the dropdown,
select List. Click on Source, put the = sign, highlight Cell E15:17 then click OK.
By that time, you will already have your basic dropdown on the Brand Column. Repeat
the process for the model column.
How
to Create a Dynamic Dropdown list in Excel
The process of creating
dynamic drop down list in excel is quite similar to the basic drop down. We are
also going to use the Data Validation Tool but with some help of the NAME MANAGER and INDIRECT Function in Excel. Before we go further, let’s have a look
on this data set first.
Our objective here is to
have a dynamic dropdown on the Model Column. We want the drop down list on
Column B that is dynamically related to the values provided in column A. For
example, if under the Brand, we select Vivo, the items that should appear on
the dropdown on model are only those under VIVO. If it’s Samsung, then only Samsung
models will appear.
First, we need to do some
setup. We have to define the brand name of each model. The trick here is to
make sure that the name we will give is similar Brand Name we have added.
Example, if in our Brand Table, we have SAMSUNG, VIVO and OPPO, the name
manager should also follow these names.
To do this, highlight cell
F13:F15, Go to FORMULAS>CREATE FROM SELECTION. Check the Top Row to use the
Samsung as our Define Name. Then, click on OK. What it does is if you will look
on the Name Manager, you will see
that there is a new data which is named Samsung. Do the same with the Vivo and
OPPO to define name for both.
After we have already set
our names, we can now go to creating the drop down. Highlight the Brand Column
(Cell B14:B20). Go to DATA > Data
Validation > Data Validation. A dialog box will open. On the dropdown,
select List. Click on Source, put the = sign, highlight Cell D14:16 then click OK. As of now, nothing fancy happened.
Next step is create the
DYNAMIC Dropdown for the Model Column. Go to DATA > Data Validation > Data Validation. On the dropdown,
select List.
Now on the Source, instead of just putting the
equal sign and the range, we will use the INDIRECT Function. What it does is it
returns a reference to a range. You can use this function to create a reference
that won't change if row or columns are inserted in the worksheet. Or, use it
to create a reference from letters and numbers in other cells.
Add the formula,
=Indirect(A14) then click OK.
There you have it. If you
select the Samsung as the Brand, only Samsung models will appear on the list of
drop down on the next column. If Vivo, only VIVO models will appear. If OPPO,
then oppo models only shows.
You
already have a dynamic drop down in excel only by using the Name Manager and
the Indirect Function in Excel. So easy trick but very helpful.
Thank you.