Dynamic dropdowns in Excel allow you to create dependent lists where options in one are dependent on what you selected in another. They are a smart way to streamline data entry: instead of scrolling through irrelevant options, users can choose from lists that automatically adjust, saving time and reducing errors.

What Is a Dependent Dynamic Dropdown List?

A dynamicdropdown list in Excelis a dropdown that provides options based on the selection you made in another dropdown. For example, if the first dropdown contains a list of departments, the second dropdown will show employees who belong to the department selected.

To create dynamic dropdown lists, you typically need to use named ranges and theINDIRECT function in Excel. The function references a range of cells that change based on what you selected in the first dropdown, and this will then update the options in the second dropdown based on your choice.

An excel sheet with Department and Employee headings.

Dynamic dropdowns make Excel smarter by improving data accuracy and user-friendliness. They also adapt to changes in your data, making your worksheets more flexible.

How to Create a Dependent Dynamic Dropdown List

This section covers how to create a simple dynamic dropdown that depends on another to display its options.

Prepare Your Data

In our example, we will create a dependent dropdown that allows you to select an employee based on the department selected. So, the headers will beDepartment(A1) andEmployee(B1).

Next, we are going to create the headers for the departments:HR(F1) andSales(G1). Then we are going to enter the names of the employees below.

Adding HR and Sales employee names to the Excel sheet.

Here is a sample table in case you want to follow along:

Create Named Ranges

Next, we need tocreate named rangesfor the departments. Let’s start withHR, which includes the namesAlice,Bob, andGrace.

Select the names (F2:F4), click theName Boxon the left side of theFormula Bar, and typeHR. Then, pressEnterto name the range.

Creating a named range in Excel.

Do the same for the names in the sales department by selecting them (G2:G4) and enteringSalesin theName Box.

Creating named ranges is not absolutely necessary, but it makes the process smoother. With named ranges, you won’t have to keep looking at the spreadsheet to find the right cell references.

The Data Tools group of the ribbon in Excel.

Create an Independent Dropdown

The first Excel dropdown will be independent, meaning it does not rely on another for its options.

Under theDepartmentheader in columnA, select the range of cells where you want to insert the dropdown (don’t select the heading).

Creating an independent dropdown list in Excel.

Select theDatatab in the top menu and clickData Validationin theData Toolsgroup of the ribbon.

In theSettingstab, set the dropdown underAllowtoList. Then, click inside the text box underSourceand select theHRandSalesheaders (F1:G1). ClickOKin the dialog box to insert the independent dropdown into the worksheet.

If you click the dropdown, you can select one of the two departments.

Create the Dynamic Dependent Dropdown

The dependent dropdown will be underEmployees, and it will display the names based on the department selected. That means, if we chooseSalesas a department, the dependent dropdown should give us the optionsEve,Frank, andIvy.

Select the range of cells under theEmployeeheading where you want to insert the dynamic dropdown list. Then, clickData Validationin theData Toolsgroup of the ribbon. Next, set the dropdown underAllowtoListin the dialog box.

TheSourcetext box is where we will use the INDIRECT function. Click inside theSourcetext box and enter the following formula:

To make it a dynamic dropdown, we need the INDIRECT function to reference the independent dropdowns we created in the previous section. Click inside the parentheses()of the formula and select the first dropdown below theEmployeeheading, which isA2.

Here is what the formula should look like now:

Next, remove the second dollar sign so the formula looks like the one below:

By doing this, it has gone from being anabsolute referenceto a mixed reference. The source of the dropdown will now be all of columnAinstead of just cellA2.

Furthermore, although we selected cellA2, the reference will automatically change as we move down the range of cells. That means the dynamic dropdown inB3will referenceA3as a source and so on.

Now, clickOKto close the dialog box and insert the dynamic dependent dropdown into the worksheet.

If the dropdown values are blank, you might get an error. But don’t worry; just clickYesto continue.

When you select a department in columnA, you should see the appropriate set of names in the dropdown in columnB.

This is a basic dynamic dropdown, but you may take it further and create multi-level dependent dropdowns. All you have to do is make the INDIRECT function from a dependent dropdown list reference the one before it and so on. You can create as many levels as you want, as long as you organize the named ranges well for seamless cascading.