Excel Dropdown Based on a List: Setup and Troubleshooting

Yes. In Excel for Microsoft 365 on Windows, select the cell or cells, choose Data > Data Validation, set Allow to List, and point Source to a range of cells containing the permitted choices. Turn on In-cell dropdown, then select OK. The resulting dropdown lets someone choose a listed value instead of typing it, and you can use a fixed range, a named range, or a list designed to expand.

What a list-based dropdown does

An Excel dropdown based on a list is a Data Validation rule. It can make a small input area easier to fill in and help keep entries consistent: for example, choosing “Open,” “In progress,” or “Complete” is more reliable for later sorting and counting than accepting variations such as “In Progress” and “in progress.” The choices may be stored elsewhere in the workbook, so they do not need to clutter the worksheet where people enter data.

Data Validation can also show an input message when a user selects a cell and an error alert when they enter an unapproved value. It is useful for controlled worksheet entry, but it is not a security barrier. Pasting or filling data can sometimes introduce values that do not comply with validation, so check the data if accuracy is important.

Create a dropdown from a cell range

First, enter one option per cell in a single column or row. For example, put the department names Sales, Support, and Operations in Lists!A2:A4. Keeping the choices together on a separate worksheet makes them easier to maintain. Then create the rule on the worksheet where users will make selections:

Select the destination cell or range. Selecting a range applies the same rule to each selected cell.
On the ribbon, choose Data > Data Validation. If Excel presents a menu, choose Data Validation again to open the dialog.
On the Settings tab, set Allow to List. Leave Ignore blank selected if an empty entry should be permitted.
In Source, select the cells containing the choices or enter their worksheet reference. For a source on another sheet, a direct cross-sheet reference may not be accepted; use a workbook name as described below.
Make sure In-cell dropdown is selected, then choose OK.

Click a validated cell to see its arrow and select an option. In-cell dropdown controls whether the arrow appears; it does not create the rule by itself. In Excel for Mac, the Data Validation command is also on the Data tab, although the dialog layout can differ. Excel for the web provides data validation tools too, but the available controls and dialog appearance can vary from the desktop app.

Choose where the list values live

The simplest Source is a range on the same worksheet, such as =$H$2:$H$4. This is easy to understand, but it may expose helper values on a user-facing sheet. You can put the list on a separate worksheet and hide that sheet if appropriate; hiding is a presentation choice, not protection for sensitive information.

A named range is often the cleanest way to use a list stored on another worksheet. To set one up, select the source cells, open Formulas > Define Name, give the range a descriptive name such as DepartmentOptions, and confirm the workbook scope. In Data Validation, set Allow to List and enter =DepartmentOptions in Source. The leading equals sign tells Excel to use the defined name. Names cannot contain spaces, so use a name such as ProjectStatus rather than Project Status.

Another basic option is to type the choices directly into Source, separated by commas, such as Open,In progress,Complete. This suits a very short list that rarely changes. It is less convenient to edit, and the list separator may depend on regional settings. A cell range or named range is preferable when options are numerous, contain punctuation, or need regular maintenance.

Make the choices expand as you add items

A fixed source such as $A$2:$A$10 does not automatically include a new item typed in A11. If the list is likely to grow, make the source dynamic instead of having to edit the validation rule each time. One practical Microsoft 365 approach is to create a cleaned, unique list in a spare area and define a name that refers to its spill range.

Assume the raw options are in Lists!A2:A100, and enter the following formula in Lists!C2, outside an Excel Table. It removes blank cells, removes duplicates, and sorts the remaining choices:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

For example, if the nonblank source entries are Pear, Apple, Pear, the spilled result is Apple and Pear. This formula uses FILTER, UNIQUE, and SORT, which are available in Microsoft 365 and Excel 2021 or later. It needs room to spill below C2; cells in that output area must be clear. It is not a formula to enter in the Data Validation Source box.

Next, use Formulas > Define Name to create a workbook-level name such as CategoryOptions, with Refers to set to =Lists!$C$2#. The # refers to the whole spill range. In the validation rule, set Source to =CategoryOptions. When the raw list changes, the spilled options update and the named range follows them. If you use a Table for raw source data, adding entries to the Table can help organize that input, but keep the spill formula outside the Table.

Build a dependent dropdown

A dependent dropdown changes its options based on a choice in another cell. For instance, a user might select a region in column A and then choose a city associated with that region in column B. This is more involved than a single list because Excel needs a separate set of city options for each region and a rule that points to the appropriate set.

One classic method is to define a named range for each group of options, then use INDIRECT in the second validation rule. Assume the region is selected in A2, and the named range for each region matches the text in A2, with spaces handled consistently. In the Data Validation Source field for B2, enter:

=INDIRECT($A2)

Here the row number is relative so the rule can be copied down, while the column A reference stays fixed. INDIRECT turns the text in A2 into a reference to a named range. This method is straightforward for small, stable lists, but names cannot contain spaces and the setup can become cumbersome as groups grow. For complex or frequently changing relationships, consider a more structured workbook design and test the rule with all expected parent choices.

Set useful messages and error behavior

Data Validation includes more than the list itself. In the Input Message tab, you can display a short instruction when a user selects the cell, such as “Choose a department from the list.” This can reduce confusion, especially when a column has a less obvious purpose. The message is optional and can be turned off if it would distract from frequent data entry.

On the Error Alert tab, choose whether Excel should show an alert after an invalid entry. A Stop alert prevents a user from accepting a value that is not on the list through ordinary typing in the cell. Warning and Information alerts allow the user to proceed after acknowledging the message. Use Stop when the value must match an approved choice; use a less restrictive alert only when exceptions are legitimate and understood.

Validation does not automatically replace existing cell values when you change the source list. If an option is removed, cells that already contain it may retain that old value. Review existing entries when revising a list, and use filtering or other checks to find outdated values. The rule controls future entry behavior; it is not a general cleanup tool.

Fix common dropdown problems

No arrow appears: Select the cell and open Data > Data Validation to confirm Allow is List and In-cell dropdown is checked. Also check that the cell is not being viewed in a context where its validation controls are unavailable.
Excel rejects the source: For a list on another sheet, define a workbook-level name and use that name in Source. Check that the name is spelled correctly and refers to a valid range.
New options do not show up: The rule may point to a fixed range that stops before the new entries. Expand the range, use an expanding source method, or check that a dynamic spill has recalculated and has space to display.
Blank choices appear: The selected source range may include empty cells. Use a range containing only populated choices or create a cleaned list that excludes blanks.
Duplicates appear: A basic range repeats whatever is in its source. Remove duplicates in the source or use a UNIQUE-based helper list where the Excel version supports it.
An invalid value remains in a cell: The value may have been present before the rule was added, or introduced by pasting. Review the data rather than assuming the rule has retroactively corrected it.

To inspect existing validation, select a cell and open Data > Data Validation. If Excel reports that the selection contains more than one type of validation, inspect cells individually or select a consistent range. You can also use the Data Validation menu’s Circle Invalid Data command to mark values that fail the current rules, where that command is available in the desktop ribbon. Correct the marked values and remove the circles afterward if needed.

Frequently Asked Questions

Can an Excel dropdown use a list from another sheet?

Yes. Define a workbook-level name that refers to the source cells on the other sheet, then use that name, prefixed with an equals sign, in the Data Validation Source box. This avoids relying on a direct cross-sheet range reference that the validation dialog may reject.

Can I make the dropdown list update automatically?

Yes. Use a dynamic source, such as a named range referring to a dynamic-array spill range, or another expanding-range setup. A fixed reference will not grow just because you add a value below its last cell.

Can users type a value that is not in the list?

That depends on the Error Alert setting and how the value is entered. A Stop alert blocks an invalid value typed normally, while Warning and Information alerts can allow an override. Pasting may bypass validation, so treat the rule as input guidance rather than a guarantee that all data is valid.

How do I remove a dropdown?

Select the validated cells, open Data > Data Validation, and choose Clear All in the dialog, then confirm. Clearing validation removes the dropdown rule; it does not erase the values already stored in those cells.

Can a dropdown show duplicate choices?

Yes, if the source range contains duplicates. Remove repeated source entries or create a distinct helper list with UNIQUE in Microsoft 365 or Excel 2021 and later. Keep the helper list and its validation reference in sync when the source data changes.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.