How to Limit Mistakes with Excel Dependent Lists

Ever had a situation where you thought your spreadsheet application should be smart enough to do a certain task? This question came when a reader wondered if the items on one Microsoft Excel drop-down list could be based on a previous list selection. It turns out the answer, which uses dependent lists and the INDIRECT function, speeds up data entry and reduces mistakes. (Includes sample Excel spreadsheet)

Why Use Multiple Drop Down Lists?

There are many scenarios where this setup could be helpful. In this tutorial, I’ll use an example of having one column representing sales regions and the next list representing the sales agents. For example, if you select “Florida” from the Region drop-down list, your Agent drop-down list will show Florida sales reps.

excel dependent list
Excel dependent list example

This conditional drop-down list approach has two main benefits.

  1. It limits the choices to valid entries.
  2. Since you don’t see all agents, it’s faster to find the correct entry. You might think of this as a filter.

I also think that using this type of conditional logic or dependency makes you plan your spreadsheet structure. Instead of entering everything free-form, you should take a little time and see if you have relationships. While this tutorial is only using two conditional lists, you could add more layers. Also, these lists are different than an Excel Custom List, which has no dependencies.

What We’ll Build in Excel

We’ll build a spreadsheet with 2 tabs that have a dependent list. The first tab, “Sales,” will be the main sheet someone would use to enter the data. The reason this is a “dependent list” is that our list choices are tied to a previous selection with a “named range.”

The second tab is called “Agents” and has all our named regions and list values. In the first column (A), we have a list of our regions. Each region (B) then has a column with the designated sales agents. For example, we can see our California region and the three sales reps. (Lars Monroe, James Stern, and Heidi Kaplan)

Agents worksheet with names and regions.
Agents sheet with named regions and employees

Creating a Dependent List

To build these type of lists, we’ll use some items we’ve discussed before and some new ones.

  • Named Ranges
  • Data Validation
  • INDIRECT function

Creating Named Ranges

I’m not going to detail this as we did an earlier named range tutorial for Excel. For this tutorial, I’ve created all the named ranges on a separate sheet called Agents. You can download the worksheet using the link at the bottom of this tutorial.

There are some key points that you want to consider. This is a case where you want to make sure your names agree. For example, on the Agents tab, I want my region names going down to be the same spelling and capitalization as the column headings across. For example, the “Florida” in cell A4 should match the “Florida” named range in cell D1. Otherwise, you will get an error.

Verifying names agree.
Make sure you match names

After you’ve set up your named ranges, you can start building the validation so the correct options appear.

Linking the Region List

This spreadsheet is designed so the user would add new rows to the Sales tab. When a new record is added, they can select from one of the pre-defined regions in the Region named range on the Agents tab.

Data Validation box with field labels.
Data validation to add region drop-down values
  1. Click cell A1 to select your whole column.
  2. Click the Data tab
  3. Click Data Validation. This will open the Data Validation dialog.
  4. From the Allow drop-down list, select List
  5. In the Source text box, enter the following:

=Region

  1. Click OK

In this example, we’re saying pull from the named range list called “Region,” which is on the Agents sheet. When the user clicks a cell in Column A, they will now see a drop-down arrow and region names.

Excel choices show on drop down list
Region drop-down options

Linking the Agents to Region

Now, we’ll move back to the Sales worksheet and link our specific agent lists. In this example, I opted to the INDIRECT function since the list was small. There is another Excel function called OFFSET that can be used from more complex lists.

  1. Place your cursor where you want the dependent list. In our example, we’ll start with cell B2 for California.
  2. Click the Data tab
  3. Click Data Validation. This will open the Data Validation dialog.
  4. From the Allow drop-down list, select List
  5. In the Source text box, enter the following function

=INDIRECT($A2)
Note: We’re referencing cell $A2 because we want the California list.

  1. Click OK
Adding Excel data validation.
Data validation linking agents

We can now progress down column B and add data validation for other regions we haven’t defined by adjusting the indirect cell reference in Step 5. For example, Canada would use $A3.

If you get an error like the one below, it’s most likely caused by an empty cell in Column A. Click Yes and add your entry to Column A.

Excel error example

Test Your Values

Finally, you should test your spreadsheet to make sure the correct entries show. As I mentioned earlier, if you make a mistake with your labels, you won’t see the desired results. In my sample spreadsheet, I would test that each of the 5 unique regions showed the expected sales agents.

attach_fileExcel spreadsheet: dependent drop-down lists

Microsoft Excel 2019 Data Analysis and Business Modeling (Business Skills)
Winston, Wayne (Author); English (Publication Language); 880 Pages - 04/15/2019 (Publication Date) - Microsoft Press (Publisher)
$38.92

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-03-07