Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to create a dependent drop-down list in Excel and Google Sheets
Excel & Google Sheets
=INDIRECT($B$1)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Create a named range for each category of data you want to use in your drop-down lists.
- 2In the first cell of your drop-down list, use Data Validation to create a drop-down list from the first named range.
- 3In the second cell, use the INDIRECT function to reference the selected value from the first drop-down list to dynamically populate its options.
Tips
- Ensure all named ranges are correctly defined and cover all possible categories.
- Check for typos in cell references as they can lead to errors in your drop-down lists.
- Test your drop-down lists thoroughly to ensure they behave as expected.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my data changes frequently?
Update your named ranges accordingly to reflect any changes in your data.
Can I have more than two levels of dependent drop-downs?
Yes, you can extend this method to as many levels as needed by using additional INDIRECT functions.
How do I handle errors in my drop-down lists?
Double-check your named ranges and cell references to ensure they are correctly set up.