Show Different Excel Drop Down Lists in Cell

, , 29 Comments


[Debra] In this video, you’ll see how to create
two dropdown lists. The first one, for produce type, we select fruit or vegetable. Then in the next column, there’s a dependent dropdown list, and it will change based on what you’ve selected in the other column. So here, it shows a list of vegetables. If I go to the next row and select fruit, Column C shows a list of fruit. Here’s the new workbook where we’re going to set
up the dropdown lists. We’ve got two sheets. One is called Data Entry, and the other one is called Lists. So you can start a new blank workbook and just name the two sheets. I have typed Produce Type
and Item on this sheet. This is where we’re going to create the dropdown lists. And on the other sheet, I’ve got three lists set up. So one is called Produce List, and I’ve got Fruit and Vegetable here, and I’ve created a fruit list, and vegetable list, and I’ve got four vegetables. This list, the produce list, is our main list. So we have two items, and it’s important that we’re using a one-word name in this list for each of the produce types. We’re going to be using those names later, and Excel won’t let us use anything with a space in it. Each of these lists is going to be changed into a formatted Excel table. I’ll do the first one. I’ll just click anywhere in this list. And on the Home tab, go to Format as Table, and select any format that you like. My list is B2 to B4, and
it does have a header. So I’ll click OK, and there is our formatted list. This table has a dropdown
list in the header cell, that lets us filter or sort, and we’ve also got a
little corner tab here that lets us change
the size of this table. And if we add new items, the table would automatically
expand to include them. We’re going to do the same thing for the fruit and vegetable list. So for this one, I’ll click anywhere in this list, Format as Table. I’ll select that color. Again, it does have headers. And the same for vegetable. I’ll make that green. Has header. And the next step will
be to name the cells that have items in them in each list. So I’m going to select cells B3 and B4, where we have the produce type names, and to name these two cells, I’m going to click on the Name Box here. I’m going to type a one-word name. And this list, I’m going to call Produce, so I’ll type it in here, and then press the enter key. And that completes the name. Now, it still shows Table1, but if I click the arrow here, you can see Produce. I’m going to give the fruit list a name, and it has to be an exact match for the item in this produce list. So I’ll select these four cells, click in the Name Box, and type fruit, and press enter. Upper or lower case doesn’t matter, as long as the spelling is the same. So now we have a fruit list, and I’m going to do the same here, and this will be called Vegetable. And press enter. So now, we can see all three names, Fruit, Produce, and Vegetable, and the table names as well. Next, I’m going to set
up the data entry sheet. So go over to it. I’ve got my headings entered already, and I’m going to create a table from these two cells, and then we’ll add
items in the rows below. So with one of those
heading cells selected, on the Home tab, Format as Table, and select the style you like. And again, my table has headers. And OK. So it creates a table with one blank row below the headings. We’re going to set up a very simple dropdown in this cell, where we can select one
of the produce types, fruit or vegetable. So I’ll go to the Data tab, click here for data validation, and on the Settings tab, we want a list, so instead of any value, I’ll click and select List. For the source, we want to use that produce
list that we created. If you are on a Windows computer, you can press the F3 key, and you’ll see the names you’ve created. So click Produce, and OK, and it puts in an equal sign, and the name of that range. If you don’t have that shortcut available, you can just type=produce, and then click OK. Now we’ve got a dropdown arrow when this cell is selected. And we can select fruit or vegetable. And in this cell, we’re going to create a dropdown that depends on what we’ve selected here. So if Fruit is selected, it will show the fruit list, and if we’ve chosen Vegetable, we’ll only see vegetables here. To create this dropdown, on the Data tab, again, click Data Validation. We want a list, and this time, we’re
going to use a formula. So I’ll type=. The function we’re going
to use in this formula is called INDIRECT. I-N-D-I-R-E-C-T. And then click an open bracket. And what we want to use
as the name of our list is whatever name has been selected here. So I’m gonna type B3, close that bracket. So, instead of typing
fruit or vegetable here, we’re going to say, use the INDIRECT function to get the name that we
want to use from cell B3, and create a reference to that list. So I’m going to click OK. So now it should create a list based on that produce type. And so now, we can select lemon. If I go to the next row
and type fruit again, it would show the fruit list. If I change this to vegetable, it’s going to show the list of vegetables. So this is dependent dropdown list. We create a main dropdown, and then a dependent dropdown, where the items in that list depend on the cell that
we told it to look at. For more Excel tips and tutorials, and to download the sample
file for this video, please visit my Contextures website at www.contextures.com.

 

29 Responses

  1. OptionGal

    May 31, 2018 1:10 am

    Many times I've struggled with dependent drop down lists… this is the best explanation by far!

    Reply
  2. Juan Pablo Rojas W.

    June 11, 2018 7:47 pm

    For some reason I did this same procedure in Excel 2010. The difference is that I used 4 selection sets instead of two. (Role) and then Vehicle, motorcycle, peasant and occupant. ONce I select the second field Excel generates an error and it closes. (if you or anyone reading this post has had the same issue) and if they have found any workarounds. Thanks for the Vid. 🙂

    Reply
  3. Nick Zouein

    June 29, 2018 3:58 am

    Thank You very much, very well explained.
    What if the fruits or vegetables are priced "in a seperate column", how can we get to show the related price once the item is selected?

    Reply
  4. Dianne Drechsler

    July 4, 2018 2:47 am

    This is really great and very well explained in a simple manner. Thank you! I don't suppose you have a solution for multiple words such as departments and sub-departments?

    Reply
  5. Joshua Crossing

    October 4, 2018 8:51 pm

    This video is great! I didn't know you could do this… I knew about single drop down list, but not dependent drop lists. Not only did I learn something new, the way the video was done is really great. perfect pace, perfect level of detailed explanation. I know a lot about Excel, but even a new bee could follow along and get it right. GREAT JOB!!

    Reply
  6. Pie God

    January 11, 2019 3:32 pm

    Is their a way to make a drop down that changes another drop down at the same time

    For example:

    Carrots = $15
    but if i change the item to Pizza
    it changes the price and the item at the same time
    pizza=$8

    Reply
  7. Tendai Mundoringisa

    March 3, 2019 5:55 pm

    Great video ,i realised that if you had selected fruit or vegetable and i change my produce list the previous entry on the item remains until i select the drop down box ,how can i create an error message if the produce type is changed ??

    Reply
  8. Y Abhishek

    April 26, 2019 7:40 am

    I like your explanation, Your shared information need to me , I search in youtube for how to create this option, finally i got a solution through your video thank you.

    Reply
  9. Heng ZHANG

    October 10, 2019 10:04 pm

    Hi, are you going to do an update for the Excel Data Validation Combo box using named ranges? i have some problems when the named range is a table-based one. Thank YOu.

    Reply

Leave a Reply