Have you ever seen the below (two combo boxes – one dependent of the other) in Excel and wondered how it was set up?

This feature – adaptive response drop-down lists – is popular on websites that ask for items of information that are drilled down from each other (e.g. Country – State – Province/Region, Industry – Sector – Sub Sector). And here’s the fun part – it can actually be replicated in MS Excel! Here’s how:

To get dependent dropdown boxes set up in Excel, you will need to use the following:

• Form Controls (combo boxes in particular) – These can be accessed from the Developer Tab (activate the Developer Tab in Excel Options) if you cannot see it on your view):
• Named ranges – These are set up by highlighting a range and inputting a name in the Name Box. The named ranges you have set up in a workbook can be viewed in the Name Manager
• Worksheet function (=INDEX and =INDIRECT in particular)

Let’s get started.

Step 1: Set up the category list, and the item lists you want your combo boxes to drill down on. I am using 5 lists, to be organized through two combo boxes. Note that you can set up more than 2 dependent combo boxes, but I’m sticking with two for this illustration. I want the user to be able to select a category in the 1st dropdown, and then select an item from the selected category in the 2nd dropdown.

Step 2: Name the range of the category list, by going to the Name Box and typing the name. I have named mine “CategoryList”

Step 3: Name the ranges of the item lists, one after the other. You will end up with 5 new named ranges – one for each list. Ensure the names you are using for the named ranges are the same as what is on the category list (i.e. the Category list contains items as Month, Customers, Region, Staff, etc, the named range for the month list should be Month, the named range for the customer list should be Customers, etc.).

Watch out for additional spaces or spelling variants (i.e. naming the list Customer when the category list contains the word Customers), as these are common errors that cause dependent dropdown boxes not to function properly.

Step 4: Set up two combo boxes by clicking the Developer Tab, Form Controls section, and clicking the Combo Box icon. Drag the box out to fit the shape you want.

Step 5: Right-click the first combo box, and select “Format Control” in the menu that comes up…

Step 6: …and select the list of categories as the Input Range (or you could just type CategoryList since we have set that up as the name of the range)

Step 7: Designate a cell on the worksheet, and select it as the Cell link for this dropdown. We will still go back to color this cell and name it for future use

Once you click “OK”, the dropdown works as shown below. Clicking the down arrow brings up the category list, and returns the index of the item selected in the cell we selected as Cell link (for instance, Staff was selected, and since it is the 4th item on the list the cell link returns the index of 4)

Step 8: Name this cell link cell using the Name Box. I used the name “CatIndex” (this step is optional, but it makes the formula creation easier to follow as you will see in the next snapshot)

Step 9: Go to the Name Manager and create a new Name (Formula Tab > Define Name). Enter the name “SelectedCategory“, and the field where you are asked for the range of cells, enter the below formula:

=INDIRECT(INDEX(CategoryList, CatIndex,1)

This formula nests two functions – INDEX and INDIRECT.

INDEX returns the name of an item in a range given the range (CategoryList) and the index number (CatIndex) of the item, and INDIRECT converts the name given into a reference (the reference being the named range bearing the same name). Recall we set up 5 named ranges to represent the 5 lists we are using, so when the INDEX function returns the name of an item based on the number in CatIndex (i.e. 2), the INDIRECT function will convert that item name (Customer) into the named range we set up earlier – Customer – that contains the list of customers.

Step 10: Right-click the second dropdown box, select “Format Control”, and…

Step 11: …type “SelectedCategory” (the named range we created in Step 9 that contains the INDEX-INDIRECT formula) in the Input range. If you are creating more dependent combo boxes you would use the Cell Link to set up another CatIndex that will return the index of the item selected for the next drop down, but since this demo shows only 2 drop-down boxes I left it blank.

That’s it! Your dependent combo boxes are all set up.