PlayInfoBox

Home /Utilities /How to Create a Dropdown List from Another Sheet in Excel

How to Create a Dropdown List from Another Sheet in Excel

Tap through the steps below to see exactly how it works.

  1. Prepare your list on a separate sheet

    List the items you want to appear in the dropdown vertically on a separate sheet (for example, a sheet named "Lists").

  2. Select the cells for the dropdown

    Select the range of cells where you want people to be able to pick from the dropdown.

  3. Open "Data > Data Validation"

    On the "Data" tab, click "Data Validation" and choose "List" as the validation type.

  4. Reference the range on the other sheet

    Click the "Source" field, then navigate to the sheet containing your list and select that range directly.

  5. Why this is useful

    When your list has many items or changes often, managing it on a separate sheet means the dropdown automatically reflects every edit you make to the list.

Why a separate list sheet beats typing options directly

You can type dropdown options directly into the Data Validation dialog, but that approach becomes painful the moment the list grows or needs updating β€” you have to reopen the dialog and manually edit the text every time. Keeping the source list on its own sheet means updating one cell there instantly updates the dropdown everywhere it's used.

Making the list expand automatically

For a list that keeps growing over time, format the source range as a proper table before referencing it, or use a named range with a formula that expands automatically as new items are added below. This way, new list items you add later show up in the dropdown without you having to redefine the source range each time.

Frequently Asked Questions

Can I select items from more than one column for the dropdown?

No, a standard dropdown list can only reference a single column or row of values, so if you have multiple categories, keep each category's options in its own separate column.

What happens if someone types a value that isn't on the list?

By default, Data Validation will show a warning and block the entry, though you can adjust the alert settings to only warn without blocking if you want more flexibility.