Microsoft Excel is the go-to software for many. However, some data entry methods can lead to errors. One example is when you want to limit the entries on an Excel drop-down list based on a previous condition. This tutorial will show how dependent drop-down lists and Excel data validation can help avoid errors and allow you to spend more time on your analysis rather than fixing mistakes. (Includes example spreadsheet)
Benefits of Excel Dependent 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 another column 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.
This conditional dropdown list approach has several benefits.
- It limits the list box choices to valid entries.
- It’s faster to find the correct entry because you’re not seeing all the agents. So you might think of this as a conditional filter for the second drop-down list.
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 worksheets that include 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 box choices are tied to a previous selection with a “named range.”
The second worksheet is called “Agents” and has all our named regions and list values. In the first column (A), we have a list of our regions, some states, and Canada. 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)
Creating a Dependent Drop Down List
To build these conditional list boxes, 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. First, 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.
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.
- Click cell A1 to select your whole column.
- Click the Data tab
- Click Data Validation. This will open the Data Validation dialog.
- From the Allow drop-down list, select List
- In the Source text box, enter the following:
- Click OK
In this example, we’re saying pull from the named range list called “Region,” which is on the Agents sheet. So when the user clicks a cell in Column A, they will now see a drop-down arrow and region names.
Linking Sales Agents to Region
Now, we’ll move back to the Sales worksheet and link our specific agent lists. In this example, I opted to use the INDIRECT function since the list was small. However, there is another Excel function called OFFSET that can be used with more complex lists.
- Place your cursor where you want the dependent list. In our example, we’ll start with cell B2 for California.
- Click the Data tab
- Click Data Validation. This will open the Data Validation dialog box.
- From the Allow drop-down list, select List.
- In the Source text box, enter the following function.
Note: We’re referencing cell $A2 because we want the California list.
- Click OK
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 a data source evaluation error that reads, “The Source currently evaluates to an error. Do you want to continue?” it’s most likely caused by an empty cell in Column A. Click Yes and add your entry to Column A.
Why Isn’t My Dependent List Updating?
Sometimes you’ll add an entry to your spreadsheet but don’t see it reflected in the drop-down list. This typically has to do with how you defined your named range.
For example, if I add John Diamond to Virginia, the entry shows on the Agents sheet. However, if I save the workbook and switch over to the Sales tab and click the Virginia dropdown list, John Diamond doesn’t show. This is because the list entries are not dynamic.
One solution is to adjust the Named Range so it includes your new values. You can do that using Name Manager from the Formulas tab. For example, in the screen snap below, I adjusted the range to include all of column F. This ensures your new entries will show. One drawback to this method is you’ll see a blank line at the end of your list box entries.
A cleaner solution is to turn the cells into an Excel table.
- Select your cell range.
- Click the Home tab.
- Click Format as Table.
- Select table styling.
- Click OK on the Create Table dialog.
The list will now be dynamic and adjust for any new entries.
Test Your List 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.
Hopefully, you see the value of these conditional drop down lists in Excel. Although they take a little time to create, the dependent drop down list can save you from making errors and speed up your data entry.