How to populate a dropdown with data from another sheet in Excel

If your list and dropdown are on different pages, you’ll need to know this trick to make the dropdown dynamic.

Offering users choices using a dropdown is a good way to prevent input errors because the user can’t enter anything other than one of the items in the dropdown. These dropdowns also facilitate data entry in general. It’s quicker to click and choose than it is to manually enter values from the keyboard.

Fortunately, Microsoft Excel’s data validation feature lets you create a dropdown, and the process is quick and easy as long as the list data is on the same sheet as the dropdown. You can reference a list on another sheet, but it takes a bit more work. In this tutorial, I’ll show you how to populate a dropdown with data on another sheet.

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use older .xlsx versions. Excel for the web will support an existing dropdown, but you can’t use Excel for the web to create the dropdown.

Throughout this article, I’ll refer to the data validation control as dropdown. This technique won’t work with content controls or the legacy combo box control.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

How to populate from another sheet in Excel

You can populate a dropdown with a list on another sheet by referencing the cells, but the resulting dropdown won’t be dynamic, even when the list is in a table object. Instead, you must apply a name to the list of items (excluding the table’s header cell) and then refer to the list name when creating the dropdown on another sheet. That’s a piece of the puzzle that would otherwise drive you nuts if you didn’t know it.

Figure A shows a short list of animals on a sheet named Lists. The name of the table is Animals, but we won’t reference it that way. In a large app, you might have many lists on this sheet and lots of dropdowns might refer to different lists on this sheet.

Figure A

Before we continue, I want to list the different elements because the term animal occurs a few times and it’s easy to confuse them:

• Animals: the name of the table with the animal list. It isn’t necessary to name the table for this technique to work. This table comprises the entire list, including the header cell.
• Animals_List: the named range of the list items, excluding the header cell, of the animal list.
• Animals List: the only column in the Animals table.

First, I’ll show you how to name the list, Animal_List—don’t confuse this with the table’s name (Animals):

1. Select only the list items, don’t include the header cell. In this case, that’s B3:B6.
2. Click the Formulas tab.
3. In the resulting dialog, name the range Animals_List.
4. Leave the Scope setting as Workbook.
5. Click inside the Source control and enter =Animals[Animals List], as shown in Figure B.
6. Click OK.

Figure B

The range name of the source list warrants a bit of explanation. The first component, =Animals, refers to the table named Animals on the Lists sheet. The second component, [Animals List] is the column name. Subsequently, this reference, =Animals[Animals List], references the Animals List column in the Animals table. If you’re not using a table, the reference is much simpler, Lists!$B$3:$B$6.

Now, let’s create a dropdown on another sheet named Dropdown and reference Animals_List :

1. Create a second sheet and name it Dropdown (strictly speaking, it isn’t necessary to name this new sheet).
2. Select a cell — I selected B2.
3. Click the Data tab.
4. In the Data Tools group, click Data Validation, and then click Data Validation from the dropdown.
5. From the Allow dropdown, choose List.
6. For Source, enter =Animals_List, as shown in Figure C.
7. Click OK.

Figure C

As you can see in Figure D, Excel populates the dropdown using the list of animals on the Lists sheet — a completely different sheet, but in the same workbook.

Figure D

If you modify the list of animals on the List sheet, the dropdown will automatically update accordingly. Try that now.

Return to the Animals table on the Lists sheet and add rabbit. Then, check the dropdown on the Dropdown sheet. It will include rabbit!

If this doesn’t work for you, be sure you expand the table to enter the new record by selecting the last cell in the table and pressing Tab to create a new row.

It does seem like a lot of work, but it’s necessary if the list and dropdown aren’t on the same sheet. In a nutshell, you must define the list by giving it a range name. Don’t include the header cell in that name. Then, use the named range as the source reference for the dropdown. As long as the Scope setting is the entire workbook, everything will update as expected, even though the list is on another sheet.

Stay tuned

Populating a dropdown with a named range on another sheet isn’t difficult, but doing so does require a few unexpected tasks. In a future article, I’ll show you how to populate a dropdown using a list in another workbook.