Dropdown List in Excel: Create, Edit, and Fix One

In Excel for Microsoft 365 on Windows, select the cells for the dropdown, then choose Data > Data Validation > Data Validation. Set Allow to List, enter the choices in Source or select a range containing them, make sure In-cell dropdown is checked, and select OK.

What a dropdown list does

A dropdown list lets someone choose a value from a defined set instead of typing it freely into a cell. In Excel, the usual way to make one is Data Validation with the Allow setting set to List. The cell displays an arrow when selected, and choosing an option enters that value into the cell.

This is useful wherever consistent wording matters. A project tracker might offer Not started, In progress, and Complete. An order sheet could provide a fixed set of product categories, while an attendance log might use Present, Absent, and Excused. If everyone chooses from the same list, sorting, filtering, counting, and summarizing the entries is generally more reliable than when people type variations such as Complete, completed, and done.

A dropdown does not automatically make a workbook secure or guarantee that every value is valid. Someone can paste data into a validated cell, and pasting can bypass the usual entry prompt. Data Validation is an entry aid and a consistency check, not a substitute for protecting sensitive data or reviewing imported information.

Create a dropdown from typed choices

For a short list that is unlikely to change, you can type the choices directly into the Source box. First select the cell or range that should have the dropdown. Then use this sequence in Excel for Microsoft 365 on Windows:

Choose Data > Data Validation > Data Validation.
On the Settings tab, set Allow to List.
Click Source and type the choices separated by commas, such as New,In progress,Complete.
Leave In-cell dropdown selected, then select OK.

Select a validated cell to see the arrow and choose an item. If you applied validation to a range, each selected cell gets its own dropdown. You can also copy a validated cell and paste it into other cells, although copying and pasting may replace existing rules or content, so check the destination before doing so.

Typed choices are convenient for a small, stable list. They are less suitable when choices contain commas, when there are many choices, or when the options will need regular editing. List separators can also depend on regional settings, so a workbook created with one locale’s separator conventions may not behave as expected in another.

Use a worksheet range as the source

A source range is easier to maintain when a list has several items or may change. Put the choices in a column, ideally on a separate worksheet named Lists, with no blank cells between the options. For example, store New, In progress, and Complete in Lists!A2:A4. Select the destination cells, open Data > Data Validation > Data Validation, set Allow to List, and enter a source reference.

With the choices in Lists!A2:A4, the source reference is =Lists!$A$2:$A$4. However, Excel’s Data Validation list source does not accept a direct reference to a range on another worksheet in the usual way. To use the Lists sheet, create a defined name for the choices instead:

Select Lists!A2:A4.
Choose Formulas > Define Name, enter a name such as StatusOptions, and confirm the workbook scope and range.
Select the cells that need the dropdown and open the Data Validation dialog.
Set Allow to List and enter =StatusOptions in Source.

The cell layout for that source is the three choices in Lists!A2:A4, with the defined name StatusOptions referring to those cells. The leading equals sign tells Excel to use the named range as the list source. Names cannot contain spaces; use a clear name that is easy to recognize later. You can review or edit defined names through Formulas > Name Manager.

If your choices are on the same worksheet, you can select their cells in the Source box rather than type a reference. Keep the source list separate from the area where people enter records, and label it so workbook editors know what it controls. Avoid including blank cells in the source range, since those can create an empty-looking choice or make the list seem incomplete.

Choose the right list setup

The best setup depends on how often the options change and who maintains the workbook. For a temporary form with three fixed answers, a typed list may be quickest. For a shared tracker, a range on a Lists worksheet is usually clearer because an editor can see and update the options in one place. A defined name makes a list on a separate worksheet practical and gives the Source box a readable reference.

Use typed choices for a short list that rarely changes and has no separator-related complications.
Use a worksheet range when options need editing, when the list is longer, or when you want a visible source that can be reviewed.
Use a defined name when the source is on another worksheet or when a meaningful label is easier to manage than a cell address.

Consider who will update the workbook as well as who will fill it in. Hiding a source worksheet can make a form tidier, but it also makes the options less obvious to a casual editor. If lists change often, document where they live and who is responsible for updating them. A dropdown is only as current as its source.

Set prompts and control invalid entries

The Data Validation dialog includes more than the list source. On the Input Message tab, you can show a short prompt when a user selects a validated cell. For example, a prompt might say Choose the status that best describes the current stage. This is helpful when the options are abbreviated or when a field’s purpose is not self-evident.

On the Error Alert tab, you can choose whether Excel shows a message when someone enters a value that is not on the list. A Stop alert blocks an ordinary typed entry that does not meet the rule. Warning and Information alerts allow the user to continue after displaying a message. Use a clear title and explanation rather than assuming that people know why an entry was rejected.

The alert does not prevent every route by which an invalid value can appear. Pasting, filling, importing, or using some automation can leave values that do not match the rule. If the workbook is used for important reporting, periodically check the data, especially after importing or merging records. Also consider whether users need an intentional choice such as Not applicable; if so, include it explicitly rather than relying on a blank cell to communicate that meaning.

Edit, copy, or remove a dropdown

To change an existing rule, select a cell that has the dropdown and open Data > Data Validation > Data Validation. Edit the Source or other settings and select OK. If you want the change to affect several cells, select the full destination range first. Excel may ask whether to apply the change to other cells with the same settings; review the selection carefully before confirming.

To remove validation, select the relevant cells, open the Data Validation dialog, and choose Clear All, then select OK. This removes the validation rule; it does not necessarily erase values already entered in those cells. If the cells contain data you need to keep, removing the rule is different from clearing the cell contents.

When copying a dropdown, copying the cell can carry its validation along with other cell contents or formatting, depending on the paste method. If you only want to transfer the rule, use Paste Special and choose Validation when that option is available. After copying, test a destination cell. A copied rule may still point to the original source range, which is fine if that range is intended to be shared, but not if the new area needs different choices.

Common dropdown problems and fixes

The arrow is missing: Select the cell and reopen Data Validation. Confirm Allow is set to List and In-cell dropdown is checked. The arrow generally appears when the validated cell is selected, not constantly beside it.
The list is incomplete: Check the source range for omitted rows. If you added choices below the original range, expand the source reference or update the defined name so it includes them.
The list is blank or has an empty choice: Inspect the source range for empty cells, formulas that return an empty string, or a reference to the wrong area. Keep the source entries together and verify the range in Name Manager if it uses a defined name.
Excel rejects a valid-looking entry: Compare the typed value with the source exactly. Extra spaces, different punctuation, or alternate wording can cause a mismatch. Selecting an item from the dropdown avoids many typing differences.
Invalid values still appear: The entry may have been pasted or imported. A validation rule is not a guarantee that existing data is clean; review the cells and correct any values that do not match the intended choices.

If the Data Validation command is unavailable, check whether the worksheet is protected or the workbook is in a mode that prevents editing. Also make sure you have selected ordinary worksheet cells rather than an object such as a chart. In a shared workbook, editing permissions or workbook features can affect what changes are allowed.

Excel for Mac and Excel for the web

In Excel for Mac, the core approach is the same: select the cells, open the Data tab, choose Data Validation, and set the rule to a list. Ribbon placement and dialog appearance can vary slightly by Excel release, but the source, input message, and error-alert concepts are familiar. Keyboard shortcuts also differ between Windows and Mac, so using the ribbon is the most consistent way to follow these steps across platforms.

Excel for the web supports working with data validation, but the available controls and their placement can differ from the Windows desktop dialog and may change as the web app is updated. If an option you need is unavailable in the browser, open the workbook in the desktop app and make the change there. When sharing a workbook, test the dropdown in the version your collaborators will actually use.

Frequently Asked Questions

Can I make a dropdown list in Excel without a formula?

Yes. For a small list, type the choices separated by commas in the Source box after setting Allow to List. For a list stored in cells, you can select the source cells in the dialog. A defined name is useful for referencing a list on another worksheet, but you do not need to write a calculation formula.

Can a dropdown list update when I add a new option?

Not if the validation source is a fixed range that stops before the new entry. Expand the source range or update its defined name to include the added option. Keeping the choices together in a clearly marked list makes this maintenance easier and reduces the chance of leaving out a new value.

Can I use a list from another worksheet?

Yes. A common approach is to put the choices on a separate sheet, define a name for their range, and use that name as the Data Validation source. This avoids relying on a direct cross-sheet range reference in the Source box.

Why can someone type something that is not in the dropdown?

The Error Alert settings may allow the user to continue, or the value may have been pasted, imported, or entered by a process that does not trigger the usual validation prompt. Choose a Stop alert to block ordinary invalid typing, and check pasted or imported data separately.

Does deleting a dropdown delete the cell’s current value?

No. Clearing the validation rule removes the dropdown behavior, while the cell’s existing content normally remains. To remove both the rule and the value, clear the contents separately, taking care not to erase information you still need.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.