How to Easily Create Multiple Dependent Drop-Down Lists in Excel

In this step-by-step tutorial, first we learn how to create a basic dropdown list in Microsoft Excel. Next, we look at how to create multiple dependent drop-down lists. With multiple dependent drop-down lists, the second or the subsequent drop-down list adjusts based on the selection in the first drop down list. This is helpful to validate data entry.

  • Few quick notes:
    • ✅ This requires the =xlookup function, which is available as part of Microsoft 365. Unfortunately, older versions of Excel don’t currently support this.
    • ✅ To use this on multiple rows, use =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you’ll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
    • ✅ You can access the sample sheet here: https://1drv.ms/x/s!AmxrofZZlZ-whMc5Q…
  • Additional resources
  • Timestamps
    • 0:00 Introduction
    • 0:42 Create simple drop-down list
    • 2:50 Create dependent drop-down list
    • 3:43 Use XLOOKUP function
    • 6:17 Create drop-down list with XLOOKUP output
    • 7:07 Use UNIQUE function
    • 9:20 Use SORT function
    • 10:44 Wrap up

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s