How to Create Drop-Down Lists in Excel

Drop-down lists let you pick from a set list instead of typing

A drop-down list in Excel is a cell that shows a small arrow when you click it. Click the arrow and you see your predefined options — you pick one instead of typing it yourself. This prevents typos, keeps data consistent across a spreadsheet, and makes data entry faster when you have the same choices over and over.

Excel calls this feature data validation. You set it up once, and then anyone using that spreadsheet sees the list and can only pick from your options (or you can allow typing too, if you prefer). It works in Excel on Windows, Mac, and in Excel Online.

Key Takeaways

Data validation creates a drop-down list by restricting what can be entered in a cell to options you define.
You can build a list from cells in your spreadsheet, type the options directly, or pull from a named range.
The drop-down appears in any cell where you explore the validation rule, and you can copy it to other cells.
You can allow users to type values not on the list, or lock them to your options only.
Excel shows an error message if someone tries to enter something not on your list when typing is restricted.

Create a drop-down list from cells in your spreadsheet

The simplest method uses cells you already have. Say you have a list of department names in column A (rows 1 through 5). Click the cell where you want the drop-down to appear. Go to the Data tab at the top, then click Data Validation (in Excel Online it's under Data Tools). A dialog box opens.

In the dialog, change the first dropdown from "All" to List. In the "Source" field, type the range of cells that holds your options — in this example, A1:A5. Click OK. Now that cell has a drop-down arrow. Click it and you see your department names. Pick one and it fills the cell.

To copy this drop-down to other cells, click the cell with the validation, copy it (Ctrl+C or Cmd+C), then select the range where you want the drop-down and paste. The validation rule copies with it, and the cell references adjust automatically if you used relative references.

Type your options directly into the validation rule

If your list is short and won't change, you can type the options straight into the validation dialog instead of putting them in cells first. Click the cell where you want the drop-down. Go to Data > Data Validation. Set the first dropdown to List.

In the Source field, type your options separated by commas with no spaces — for example: Yes,No,Maybe. Click OK. The drop-down now shows those three choices. This method is faster for short lists but harder to edit later if you need to change an option, because you have to go back into the validation dialog for each cell.

Use a named range to manage a longer list

If you have a long list of options that you use in multiple drop-downs, a named range makes it easier to update. First, select the cells that hold your options (say, B1:B20 with a list of product names). Go to the Formulas tab (or Sheet in Excel Online), then click Define Name. Type a name for this range — something like ProductList — and click OK.

Now when you create a data validation rule, you can type that name in the Source field instead of the cell range. If you later add or remove products from your list, the drop-down updates everywhere you used that named range. This saves you from having to edit the validation rule in every cell.

Allow typing or restrict to list only

By default, Excel lets someone type a value that is not on your list — the drop-down is just a shortcut. If you want to force users to pick only from your options, open the Data Validation dialog again and check the box that says In-cell dropdown (or Show dropdown arrow). Make sure Allow invalid data is unchecked or set to show an error.

When you restrict to list only and someone tries to type something not on your list, Excel shows an error message. You can customize that message by clicking the Error Alert tab in the validation dialog. Type a title and message — for example, "Please pick from the list" — and choose whether the error stops the entry or just warns them.

Copy a drop-down to many cells at once

Once you have one cell with the drop-down you want, you can paste it to a whole column or range quickly. Click the cell with the validation. Copy it (Ctrl+C). Click the first cell in the range where you want the drop-down, then hold Shift and click the last cell. Paste (Ctrl+V). The validation rule applies to every cell in that range.

If your list is in cells and you copy the validation down a column, Excel adjusts the cell references automatically. For example, if your first drop-down references A1:A5, and you paste it down to row 10, that cell's validation will reference A6:A10 instead. If you want the same list in every cell, use a named range or type the options directly so they do not change.

Edit or remove a drop-down list

To change what options appear in a drop-down, click any cell with that validation. Go to Data > Data Validation. The dialog shows your current settings. Edit the Source field — change the cell range, update the comma-separated list, or pick a different named range. Click OK and the change applies to that cell and any others with the same rule.

To remove a drop-down, click the cell, go to Data > Data Validation, and click Clear All. The validation rule disappears but the value in the cell stays. If you want to remove the validation from many cells at once, select the range, open Data Validation, and click Clear All.

Frequently Asked Questions

Can I use a drop-down list from another sheet in the same workbook?

Yes. In the Source field, type the sheet name followed by an exclamation point and the range — for example, Sheet2!A1:A10. If your sheet name has spaces, put it in single quotes: 'Sheet 2'!A1:A10. This works the same as referencing cells on the current sheet.

What happens if I delete a row that holds the options for my drop-down?

If you delete cells that your validation rule references, Excel shows an error when someone tries to use the drop-down. To avoid this, use a named range instead of a direct cell reference. If you delete rows within a named range, the range shrinks automatically and the drop-down updates.

Can I make a drop-down list that depends on what someone picks in another cell?

Yes, but it requires a formula. This is called a dependent or cascading drop-down. You use the INDIRECT function in your validation Source field to reference a named range that changes based on another cell's value. This is more advanced and requires setting up multiple named ranges first.

Does a drop-down list work the same way in Excel Online as in the desktop version?

Mostly yes. The steps are slightly different — Data Validation is under Data Tools in Excel Online — but you can create lists from cells, type options directly, and restrict to list only. Some advanced features like dependent drop-downs may have limitations in the online version.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.