Show Different Excel Drop Down Lists in Cell



Go to to get the sample workbook
Select an item from an Excel drop down list, and see only the related items in the next drop down list. In this example, select Fruit from the first drop down, and only fruit names appear in the next column’s drop down list.

Instructor: Debra Dalgleish, Contextures Inc.

Get Debra’s weekly Excel tips:

More Excel Tips and Tutorials:

Video Timeline:
00:00 Intro
00:39 Set Up the Worksheets
01:15 One-Word Names
01:41 Create Tables
02:55 Name the List Items
04:15 Main Drop Down List
05:48 Dependent Drop Down Lists
07:39 Get the Sample File

Video Transcript (abridged)

In this video, you’ll see how to create two dropdown lists. The first one, for produce type, fruit or vegetable. In the next column, there’s a dependent dropdown list, it will change based on what you selected in the other column.

Here it shows a list of vegetables. If I go to next row and select fruit, Column C shows list of fruit.

Here’s a new workbook where we’re going to set up dropdown lists. We’ve got 2 sheets. One is Data Entry, other one is called Lists. Start a new blank workbook and name the 2 sheets.

I typed Produce Type and Item on this sheet, where we’re going to create dropdown lists.

On the other sheet, I’ve got 3 lists set up, Produce List with Fruit and Vegetable, and a fruit list, and vegetable list, with 4 vegetables.

The produce list is our main list. We have 2 items, and it’s important to use a one-word name in this list for each produce type. We’ll those names later, and Excel won’t allow spaces.

Each list will be changed to a formatted Excel table. In the first one, click anywhere in the list. On the Home tab, go to Format as Table, select a format that you like.

My list is B2 to B4, and has a header. Click OK, and there’s our formatted list. It has a dropdown list in the header cell, to filter or sort, and a corner tab here to change the size of this table. If we add new items, the table automatically expands to include them.

We’ll do the same thing for the fruit and vegetable list. Click anywhere in this list, Format as Table. Select a color. Again, it has headers. And same for vegetable, green, has header.

The next step is name cells with items in each list. Select cells B3 and B4, with produce type names, and to name these 2 cells, click the Name Box. Type a one-word name, Produce, then press Enter, to complete the name.

It still shows Table1, but click the arrow to see Produce. Give the fruit list a name, it has to be an exact match for the item in this produce list. Select these 4 cells, click in Name Box, type fruit, press Enter. Upper or lower case doesn’t matter, as long as spelling is the same.

Now we have a fruit list, do the same here, this will be called Vegetable, press enter. Now we can see all 3 names, Fruit, Produce, and Vegetable, and table names.

Next, set up the data entry sheet. I’ve got headings entered, and I’ll create a table from these 2 cells, then add items in rows below.

With a heading cell selected, on Home tab, Format as Table, select style you like. Again, my table has headers, OK. It creates a table with one blank row below headings.

We’ll set up a very simple dropdown in this cell, where we can select a produce types, fruit or vegetable.

Go to Data tab, click data validation, on Settings tab, select List.

For source, we’ll use our produce list. On a Windows computer, press F3 key, to see names you’ve created. Click Produce, OK, it puts in an = and the name of that range.

If you don’t have that shortcut, type =produce, click OK.

Now we’ve got a dropdown arrow when this cell is selected, select fruit or vegetable.

In this cell, we’ll create dropdown that depends on what we selected. If Fruit is selected, it will show fruit list, if we chose Vegetable, we’ll only see vegetables.

To create this dropdown, on Data tab, click Data Validation. We want a list, and this time, we’ll use a formula.

Type =. The function we’re going to use in this formula is INDIRECT, then click an open bracket.

We want to use whatever name has been selected, so type B3, close that bracket.

Instead of typing fruit or vegetable, we’ll use INDIRECT function to get name that we want to use from cell B3, and create a reference to that list, click OK.
It will create a list based on that produce type. Select lemon. Go to next row and type fruit again, it shows fruit list.

Change to vegetable, it shows list of vegetables. So this is dependent dropdown list.

We create a main dropdown, then a dependent dropdown- items in that list depend on cell we told it to look at.

Go to to get the sample workbook

Nguồn: https://dantrionline.com.vn

Xem thêm bài viết khác: https://dantrionline.com.vn/lam-dep/

Article Tags:
Article Categories:
Làm Đẹp

Comments

  • Perfect! Thank you

    Unique Karki May 29, 2020 7:26 am Reply
  • In the latest Excel this will not work if you convert each of your lists in the List page into a list; I was going nuts trying to figure out what I was doing wrong, then I saw another video, and in that one he just selected the ranges and gave them names without turning the original lists into actual lists and it worked.

    Steve Private May 29, 2020 7:26 am Reply
  • What would you suggest if there are two columns ( i.e a corresponding id column) next to the column?

    Moe Sadr May 29, 2020 7:26 am Reply
  • Thank you! You saved my life!

    Cheri Love May 29, 2020 7:26 am Reply
  • WHY IS MY INDIRECT FORMULA FUNCTION NOT WORKING IM TYPING =INDIRECT(CELL VALUE) AND I GET ERROR "THE SOURCE CURRENTLY EVALUATES TO AN ERROR. DO YOU WANT TO CONTINUE"

    RAINBOW AC May 29, 2020 7:26 am Reply
  • i guess this video with this subject is unique

    Resam Eftekharzadeh May 29, 2020 7:26 am Reply
  • thanks

    majed adell May 29, 2020 7:26 am Reply
  • 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.

    Heng ZHANG May 29, 2020 7:26 am Reply
  • I am trying to know, if I double click on a cell, how a table will come up?

    Lokabhiram Dikshit May 29, 2020 7:26 am Reply
  • This was a very good tutorial – thank you

    Morne May 29, 2020 7:26 am Reply
  • who the hell downvoted this excellent tutorial?

    Savu Dan May 29, 2020 7:26 am Reply
  • 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.

    Y Abhishek May 29, 2020 7:26 am Reply
  • 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 ??

    Tendai Mundoringisa May 29, 2020 7:26 am Reply
  • If only she could english as well as she can excel….prawdoose

    Monil Singh May 29, 2020 7:26 am Reply
  • Brilliant vid thank you 🙂

    Xenia Demetriou May 29, 2020 7:26 am Reply
  • just what I needed to do!! 🙂 thank you (and subscribed)

    ernesto pellegrino May 29, 2020 7:26 am Reply
  • 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

    Pie God May 29, 2020 7:26 am Reply
  • Very useful

    Abdul Hafiz May 29, 2020 7:26 am Reply
  • 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!!

    Joshua Crossing May 29, 2020 7:26 am Reply
  • finally my question has been answered.

    littleshyboy May 29, 2020 7:26 am Reply
  • Very nice

    ravisahu18 May 29, 2020 7:26 am Reply
  • thank you

    Pong SK May 29, 2020 7:26 am Reply

Leave a Reply

Your email address will not be published. Required fields are marked *