Data Analytics
explore our new search
The EASIEST Excel multiple row drop down technique you've ever seen!
Power BI
May 22, 2023 5:00 AM

The EASIEST Excel multiple row drop down technique you've ever seen!

by HubSite 365 about Wyn Hopkins [MVP]

Microsoft MVP | Author | Speaker | Power BI & Excel Developer & Instructor | Power Query & XLOOKUP | Purpose: Making life easier for people & improving the quality of information for decision makers

Data AnalyticsPower BIM365 Hot News

⏬ Click to see more: One-Off dependent drop down lists in data validation are relatively straightforward: here's a technique using XLOOKUP.

The easiest Excel multiple row drop down technique is to use the Data Validation feature. This feature allows you to create a drop down menu of multiple rows in Excel by creating a list of items in a range of cells or within a single cell.

To use the feature, select a cell or group of cells where you would like the drop down menu to appear, then go to the Data tab, select Data Validation, and then select List from the Allow drop-down menu. You can then enter the list of items you want to appear in the drop down menu. Once you have done this, you will be able to select multiple rows from the drop down menu in the cell/cells you selected.

Questions and Answers about

Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.

Create a drop-down list

You can create multiple drop-down lists in Excel using the same source data. However, sometimes, it is needed to make the selection exclusive (such that once selected, the option should not appear in other drop-down lists).

How to make dynamic drop down list in ExcelIn this video. 00:00Intro00:46Counting the number of items in a dependent drop-down list01:29Different ways to create a multiple dependent drop-down list02:02Creating a table03:45The INDIRECT function05:18Making changes to data validation06:00Indirect formula08:24Testing the dependent drop-down lists09:16Making changes to the drop-down list11:15Conclusion