Creating a drop-down list in Excel is very easy, using Data Validation.

Click here to see how to create it. Then come back here.

Now we will see how to create a dependent list – a list based on a selection in another list. Thus you will be able to items items based on your selection in another list.

To give you a simple example, we are taking a list of Product Categories where the categories are,

  • Laptops
  • Mobile Phones
  • Televisions

Once you select 1 of the categories from the drop-down, in another cell, you should be able to select a Brand which is under only that particular category.

Categories & Brands

Categories & Brands

So if you select Laptops, the list of brands to select from in another cell should be

  • Dell
  • HP
  • Lenovo
  • Apple
  • Microsoft

Named Ranges

Named Ranges will need to be used to create such a list.

Click below 2 links to understand Named Ranges basics. Then come back here.

  1. Creating a named range
  2. Using it in Data Validation

 

Steps: –

  1. Create Named Ranges for the following: –
    Ensure that none of your named ranges have any spaces in between 2 words.

    • Categories names
      • Select A1:C1 and give a named range, say, ‘Categories’
    • Brands for individual categories
      Ensure that the named ranges and the Category names (column headers) should be exactly the same

      • Select A2:A6 and give a named range ‘Laptops’
      • Select B2:B6 and give a named range ‘MobilePhones’ (without a space in between)
      • Select C2:C6 and give a named range ‘Televisions’
  2. Create Category Drop-down lists
    1. Select cells where you want to select the category names, E4:E8 in our case (see image below)
    2. Go to Data tab -> Data Validation
    3. Under Allow, choose List
    4. In Source, type =Categories (this is the named range we gave earlier).
    5. You will see that you are able to select the Categories in each of the cells, from the drop-down.
  3. Create Brand lists – Dependent lists
    1. Select F4:F8 and go the Data Validation, Allow List
    2. In Source, type =INDIRECT(E4)
    3. A dependent drop-down list is created, based on your selection of the Category.
Creating DropDowns

Creating DropDowns

 

Indirect function

Indirect function

How did it happen!?

  • =INDIRECT(E4) goes into cell E4 and checks what is the content of that cell.
  • If the content is ‘Laptops’, it will go and fetch the entries in the Named Range ‘Laptops’.
  • If the content is ‘MobilePhones’, it will go and fetch the entries in the Named Range ‘MobilePhones’.
  • E4 when does below 1 row, becomes E5. Thus Whatever is selected in E5’s drop-down, the dependent list appears in F5. And so on..
Dependent list created

Dependent list created

Keep Excel-ing!