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,
- Mobile Phones
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.
So if you select Laptops, the list of brands to select from in another cell should be
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.
- 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’
- Categories names
- Create Category Drop-down lists
- Select cells where you want to select the category names, E4:E8 in our case (see image below)
- Go to Data tab -> Data Validation
- Under Allow, choose List
- In Source, type =Categories (this is the named range we gave earlier).
- You will see that you are able to select the Categories in each of the cells, from the drop-down.
- Create Brand lists – Dependent lists
- Select F4:F8 and go the Data Validation, Allow List
- In Source, type =INDIRECT(E4)
- A dependent drop-down list is created, based on your selection of the Category.
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..