How to Use Dependent Dropdown Lists in Excel

A post from our Video Tutorials blog

When working with Excel it could be useful to select data from a drop down list of options and have that selection further filter information available from another drop down list.

E.g. you could select the application name such as Excel and a second drop down list would only show the relevant topics from the Excel bank of topics. A Word list would only show topics from the Word only list, etc.



In the image below, Word has been selected from the first drop down list:



The list below that will then only show topics from the Word course:



In order to recreate this, create a sheet with all the data listed that you would like to create a list from. Your headings will be listed in column A e.g. Word, Excel, PowerPoint, Access etc.

Create the drop down box using Data Validation on the Data ribbon. This is available on our Excel Intermediate course.



The subsequent columns contain the data for each option e.g. column B lists the information required for Word, column C lists the Excel data, etc.

Next we would need to name the ranges. Highlight cells B1 to B5 and select Define Name on the Formula Ribbon. Name it Word and click on OK. Repeat these steps for columns C to F with the respective names.

The second drop down uses the INDIRECT Function needs to get the data from the first drop down as below using the INDIRECT Function in the Data Validation settings.



So this will get the data from B2, it sees its content is "Word" and then the list is taken from the correct column offset position. So it would be 1 in this case. If we chose say Windows that would be 8 cells down so it will get the topics out of the 8th column across for the drop down list.

Clever I trust you will agree so now you can get drop downs to talk to each other.