How to Insert a Drop-Down List in Excel

To insert a drop-down list in Excel, select the cell or cells, choose Data > Data Validation, set Allow to List, and enter either a comma-separated set of options or a range containing them. The cell will then offer a menu of choices, which helps keep entries consistent. The instructions below are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

An Excel workbook and the cell or cells where people should choose an option.
The list of allowed choices, such as Low, Medium, and High. You can type short options directly into the validation settings or put them in worksheet cells first.

If you plan to use a cell range, decide where to keep the source list. Keeping it in a clear, labeled area makes it easier to update later. You do not need a formula, add-in, or macro to create a basic drop-down.

Step-by-Step

Prepare the choices. For a short, unchanging list, write down the options you want to offer, in the order they should appear. For a list you may update, enter one option per cell in a column or row. For example, put Draft, In Review, and Approved in cells H2:H4. Avoid blank cells within the list; blanks can appear as empty choices. Check for spelling differences and duplicates before using the range.
Select the destination cell or range. Click the cell that should contain the drop-down. To apply the same list to several cells, select them together before opening Data Validation. For example, select B2:B50 if each row in that range needs the same status choices. You can also set up one cell first and copy its validation to other cells later.
Open Data Validation. On the ribbon, select Data > Data Validation. In the Data Tools group, choose Data Validation. If the button opens a menu, choose Data Validation again. The Data Validation dialog includes settings for the type of allowed entry, the list source, and optional messages or error warnings.
Choose the List setting. On the Settings tab, open the Allow menu and select List. Leave In-cell dropdown selected so Excel displays the arrow when the destination cell is selected. Ignore blank is selected by default in many Excel installations; it allows a blank cell to remain blank, rather than making a choice mandatory. Decide whether that fits how the sheet will be used.
Enter the source for the list. For a short list, click in Source and type the options separated by commas, for example: Open,In progress,Complete. Do not add a leading equals sign for this directly typed list. If the choices are already in cells, click the Source box and select the range, or enter a reference such as =$H$2:$H$4. The dollar signs keep the source range fixed if the validation is copied elsewhere. Click OK to apply the rule.
Test the drop-down. Select the destination cell. A small arrow should appear at its right edge; click it and choose an option. If the arrow is not immediately visible, click the cell first. Type a test value that is not on the list, too, to check how Excel handles invalid entries. Undo the test or clear it before sharing the workbook.
Set a helpful prompt or error message if needed. Reopen Data > Data Validation with a cell containing the rule selected. On the Input Message tab, you can show a short instruction when a user selects the cell. On the Error Alert tab, keep Show error alert after invalid data is entered selected and choose Stop if users must use an allowed option. Add a clear title and message, then click OK. A warning-style alert can inform users without blocking an invalid value, so use it only when that flexibility is intentional.

Using choices stored on another sheet: Excel’s list validation source is easiest to manage when the source cells are on the same worksheet. To use a list on a different worksheet, create a named range for the source cells first. Select the source cells, choose Formulas > Define Name, enter a name with no spaces, such as StatusOptions, and confirm the range in Refers to. Then select the destination cells, open Data Validation, choose List, and enter =StatusOptions in Source. This gives the rule a workbook-level name it can refer to without a direct cross-sheet range reference.

Common Mistakes

Using the wrong separator in a typed list. Commas separate choices in many Excel regional settings, but list separators can depend on your computer’s locale. If Excel treats the whole entry as one option or rejects it, put each choice in a cell and use that range as the source instead.
Including spaces by accident. A typed list such as Open, In progress, Complete may include a leading space before the second and third options. It can look harmless but make pasted or formula-generated values differ from the choices. Type carefully, or use a source range where you can inspect each cell.
Leaving gaps or unwanted values in the source range. A blank cell in the middle of a source range may create an empty option, and a heading included in the range may appear as a selectable choice. Select only the cells that contain actual options.
Expecting validation to block every invalid entry. A Stop error alert restricts ordinary typing, but data pasted into a cell can sometimes bypass validation. If the workbook is important, review entries or use Excel’s circle invalid data feature where available; validation is a helpful control, not a security barrier.
Assuming a cell’s arrow is always visible. The in-cell arrow typically appears when the cell is selected, not all the time. The absence of an arrow while the cell is unselected does not necessarily mean the rule is missing.

Tips

Use a source range for lists that may change. Editing the source cells is clearer than reopening validation to change a typed list. Keep the source list labeled and away from data-entry areas, or place it on a dedicated worksheet.
Use names for lists on other sheets. A name such as StatusOptions is easier to understand later than a bare cell address. Use Formulas > Name Manager to review or edit workbook names. If you change which cells the name refers to, check that the drop-down still contains the intended choices.
Apply the rule to the full entry area. If new rows will be added, apply validation to enough rows in advance or copy a correctly configured cell into new rows. After copying, test a new cell to make sure the rule came along.
Make the choices easy to distinguish. Use concise, mutually clear options. For example, use Not started, In progress, and Complete rather than choices that overlap in meaning. Put the most common or natural starting option near the top; Excel does not automatically select one for users.
Keep instructions close to the input. An input message can tell people what the list means or whether a selection is required. If the worksheet is shared, a short note in a nearby heading can also explain the expected format.

Troubleshooting

The arrow does not appear: Click the cell and look at its right edge. If there is still no arrow, open Data > Data Validation and check that Allow is set to List and In-cell dropdown is checked. If the controls are unavailable, the worksheet may be protected or the workbook may be in a state that prevents editing. Check Review > Unprotect Sheet if you are authorized to change the sheet, and confirm that the file is not read-only.

The list shows one combined option instead of several: Excel may be using a different list separator from the one you typed. Replace the typed options with a range, entering one choice per cell, then set Source to that range. Also remove any extra separator characters from the option text.

The list is empty or contains unexpected entries: Reopen Data Validation and inspect the Source box. Confirm that the reference points to the intended cells, that the cells contain text, and that the range does not include blank rows or a heading. If the source is a named range, open Formulas > Name Manager and check its Refers to value.

Excel accepts a value that is not in the list: Check the Error Alert tab and make sure Show error alert after invalid data is entered is selected. Choose Stop if the input should be rejected. If the value was pasted, recheck the affected cells: pasting can replace or bypass validation. Reapply the rule and review any existing entries that do not match the list.

You cannot edit the rule: A protected worksheet may prevent changes to validation settings. Ask the workbook owner to unlock the sheet or grant the needed access. If you are working in Excel for the web and a particular setting is unavailable, open the workbook in the desktop app and make the change there. Excel for the web also uses Data > Data Validation for supported list setup, though the dialog and available options can differ from the desktop version. On Mac, use the Data tab and Data Validation; the dialog is similar, but ribbon placement and control labels may vary slightly.

Frequently Asked Questions

Can I put a drop-down list in more than one cell?

Yes. Select all the destination cells before opening Data Validation, then create the list once. If the cells are in separate areas, set up one cell and copy it, then use Paste Special to paste validation to the other cells. Check a few destination cells afterward to confirm the source and rule are correct.

Can the list choices come from another worksheet?

Yes. Create a named range for the source cells with Formulas > Define Name, then enter the name preceded by an equals sign in the Source box, such as =StatusOptions. This is the practical way to refer to a list stored on another sheet.

How do I change or remove a drop-down?

To change it, select a cell with the rule and choose Data > Data Validation. Edit the source or other settings and click OK. To remove it, select the affected cells, open the same dialog, and click Clear All, then confirm with OK. Clear All removes the validation settings from the selected cells, so check your selection first.

Can users type a choice instead of selecting it?

Yes. A user can type a value, but whether Excel accepts a value not in the list depends on the Error Alert settings. With a Stop alert enabled, an invalid typed value is rejected. Users can still paste values in ways that bypass validation, so inspect data when strict consistency matters.

Can a drop-down list update automatically when I add choices?

A fixed source such as =$H$2:$H$4 will not include entries added below row 4. You can extend the source range in Data Validation when the list grows, or use a named range whose definition includes the additional cells. For a simple workbook, leave spare cells in the source range if you are comfortable with empty entries potentially appearing in the menu.

Can I make a drop-down selection required?

Data Validation can reject invalid typed entries with a Stop alert, but it does not force someone to fill every cell. Ignore blank controls whether blanks are allowed by the validation rule, and a blank may still be left in place. For a required-field workflow, use clear instructions and check for empty cells before relying on the completed data.

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.