How to Create Multiple Linked Drop Down Lists in Excel:
Drop-down lists are powerful tools in Excel. They let you provide users with a drop-down arrow that, when selected, provides them with a list of options.
This can reduce data-entry errors as it saves users from typing the answer directly. Excel lets you drag items from multiple types of cells to those drop-down lists.
However, it doesn’t stop there. By using some creative ways to configure data validation for drop-down cells, you can also create multiple, linked drop-down lists where the items Available in second list Depends on the section the user created in the first list.
What are multiple linked drop-down lists good for?
Consider that most forms fill out secondary drop-down lists based on the answer you provide in the online drop-down list. This means you can upgrade your Excel data-entry sheet just like an online form. It will modify itself based on the user’s answers.
For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need it. computer repair.
Entry options might look like this:
- computer part: Monitor, Mouse, Keyboard, Base System
- Part Type:
- Monitor: Glass, Housing, Power Cord, Interior Electronics
- Rat: wheel, led light, cord, button, cover
- keyboard: key, housing, membrane, cord, internal electronics
- base system: casing, buttons, ports, power, internal electronics, operating system
As you can see from this tree, the information that should be available to select “Part Type” depends on which computer part the user selects in the first dropdown list.
In this example, your spreadsheet might look like this:
If you create multiple, linked drop-down lists, you can use the selected item from the drop-down list in B1 to play the contents of the drop-down list into B2.
Let’s see how you can set it up. Also, feel free Download our example excel Sheet with the example below.
Create your own drop-down list source sheet Create
The cleanest way to set up something like this is to create a new tab in Excel where you can configure all of your drop-down list items.
To set up these linked drop-down lists, create a table where the headers at the top are all the parts of the computer that you want to be included in the first dropdown list. Then list all the items (part types) that should fall under that header.
Next, you’ll want to select and name each category, so that when you’re setting up data validation later, you can choose the right category.
To do this, select all items under each column and select a name that matches the same range as the header. To name a table, you simply type the name in the field above the “A” column.
For example, select a cell A2 Through this A5, and name that category “Monitor”.
Repeat this process until you have all the categories named appropriately.
An alternative way to do this is to use Excel’s Create From Selection feature. It lets you name all categories like the manual process above, but with a single click.
To do this, simply select all the ranges in the second sheet you just created. then choose sources From the menu, select More create from selection in the ribbon.
A pop up window will appear. just make sure top row is selected and then select Okay.
It will use the header values in the top row to name each of the ranges below it.
Set up your first drop down list
It’s time to set up your multiple, linked drop-down lists. To do this:
1. Back on the first sheet, select the blank cell to the right of the first label. then choose data From the menu, select More data validation in the ribbon.
2. In the Data Validation window that opens, select list Under Permission, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.
3. Select the second sheet where you set the drop-down list source data, and then select the Header field only. These will be used to fill in the initial drop-down list in the cell you selected.
4. Select the down arrow in the selection window to expand the Data Validation window. You will now see the category you selected displayed in the source field. Choose Okay to finish.
5. Now, back on the main sheet, you will see that the first drop-down list contains each of the header fields from the second sheet.
Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.
Set up your first drop down list
Select the second cell for which you want to load the list items, depending on what is selected in the first cell.
Repeat the above process to open the Data Validation window. Choose list in the Allow drop-down. The Source field is the one that will drag list items based on what is selected in the first drop-down list.
To do this, enter the following formula:
How does indirect function work?
This function returns a valid Excel reference (in this case a range) from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the category name and then provides the drop-down data validation with the correct category associated with that name.
pay attention: If you configure data validation for this second drop-down without selecting a value from the first drop-down, you will see an error message. you can choose Yes To ignore the error and continue.
Now, test your new multiple, linked drop-down lists. Use the first drop-down to select a part of the computer. When you select the second drop-down, you should see the appropriate list item for that computer part. These were the part types in the column on another sheet that you have filled for that part.
Using Multiple Linked Drop-Down Lists in Excel
As you can see, this is a great way to make your spreadsheets more dynamic. Responding to users by filling in the subsequent drop-down list select in other cellsWith this, you can make your spreadsheets far more responsive to users, and make the data more useful.
Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheet. Share some of your interesting tips in the comments section below.