A drop-down list limits what someone can type into a cell

A drop-down list (also called data validation) is a small arrow that appears in a cell. When you click it, a menu of preset options appears. The person filling out the spreadsheet picks from that menu instead of typing anything they want. This keeps data consistent — everyone enters "New York" the same way, not as "NY" or "new york" or "NY State".

Drop-down lists are useful when you are sharing a spreadsheet with other people, or when you are filling one out yourself but want to avoid typos. They take about two minutes to set up and work the same way in Excel on Windows, Mac, and the web version.

Key Takeaways

  • Select the cell or cells where you want the drop-down to appear, then go to the Data tab and choose Validation.
  • Set the validation type to "List" and enter your options separated by commas, or point to a range of cells that already contain your list.
  • You can type the options directly into the validation box, or create them in a separate part of your spreadsheet and reference that range.
  • Once you create a drop-down in one cell, you can copy it to other cells by selecting the cell and dragging the fill handle down.

How to create a drop-down from a typed list

Open your spreadsheet and click on the cell where you want the drop-down to appear. If you want the same drop-down in multiple cells, select all of them at once by clicking the first cell, holding Shift, and clicking the last cell in the range.

Go to the Data tab at the top of the ribbon. Click Validation (in Excel on Mac, it is called Validity). A dialog box will open. In the "Allow" dropdown, select List. In the box labeled "Source" or "List", type your options separated by commas. For example: New York, California, Texas, Florida. Click OK.

The drop-down is now active. Click the cell and you will see a small arrow appear. Click the arrow to see your list of options. Pick one and it fills the cell.

How to create a drop-down from cells elsewhere in the spreadsheet

If your list of options is long, or if you want to use the same list in multiple drop-downs, it is cleaner to type the options in one place and point the drop-down to that range. Start by typing your list in a column or row — for example, put "New York" in cell E1, "California" in E2, "Texas" in E3, and so on.

Select the cell where you want the drop-down. Go to Data > Validation. Set "Allow" to List. In the "Source" box, type the range of cells that holds your list. If your options are in cells E1 through E4, type $E$1:$E$4 (the dollar signs lock the range so it does not change if you copy the drop-down elsewhere). Click OK.

The drop-down now pulls its options from those cells. If you later add a new option to that list, the drop-down automatically includes it.

How to copy a drop-down to other cells

Once you have created a drop-down in one cell, you can copy it to many others without setting it up again. Click the cell that has the drop-down. Copy it (Ctrl+C on Windows, Cmd+C on Mac). Select the range of cells where you want the same drop-down — click the first cell, hold Shift, and click the last cell. Paste (Ctrl+V or Cmd+V).

Alternatively, click the cell with the drop-down and look for the small square handle in the bottom-right corner of the cell. Drag that handle down to copy the drop-down to the cells below. This method is faster if you only need to fill a few rows.

What to do if the drop-down is not working

If you click a cell and no arrow appears, the validation may not have been applied. Select the cell, go to Data > Validation, and check that "Allow" is set to List and the "Source" field is not empty. Click OK.

If the arrow appears but the list is wrong, go back to Data > Validation and check the "Source" field. If you typed the options directly, make sure they are separated by commas with no extra spaces. If you pointed to a range of cells, make sure that range is correct — for example, $E$1:$E$4 instead of $E$1:$E$5.

If someone types something that is not on the list, Excel can reject it or just warn them. To set this, go to Data > Validation, click the Error Alert tab, and choose whether to "Stop" (reject the entry) or "Warning" (let them know but allow it anyway).

Drop-downs in Excel on the web

Excel on the web works the same way, but the steps are slightly different. Click the cell, then go to Data > Data Validation. The dialog looks the same — set "Allow" to List, enter your options or range, and click Save. The drop-down works exactly as it does in the desktop version.

One limitation: if you use Excel on the web, someone using the desktop version can still type anything into a cell with a drop-down, even if validation is turned on. The drop-down is a guide, not a lock. If you need to prevent invalid entries completely, you will need to use the desktop version of Excel.

When a drop-down is better than free text

Drop-downs work best when the list of options is short and stable — states, departments, yes-or-no answers, product names. They slow down data entry if the list is very long (more than 20 or 30 items), because scrolling through a huge menu takes longer than typing.

Drop-downs also help when multiple people are filling out the same spreadsheet. They make sure everyone uses the same spelling and format, which makes it easier to sort, filter, and count later. Without a drop-down, one person might enter "NY" and another "New York", and your totals will be wrong.

Frequently Asked Questions

Can I use a drop-down that pulls from a list on a different sheet?

Yes. In the "Source" field, type the sheet name followed by the range: Sheet2!$A$1:$A$10. If the sheet name has a space, put it in single quotes: 'Sheet 2'!$A$1:$A$10. The drop-down will pull options from that other sheet.

What happens if I delete one of the options from my list?

If you delete an option that someone already entered in a cell, that entry stays in the cell but the drop-down no longer shows it as an option. If you want to clean this up, you can use Find and Replace to change all instances of the deleted option to something else, or manually fix the cells.

Can I make a drop-down that depends on what is in another cell?

Yes, but it requires a more advanced feature called dependent drop-downs. You would create separate lists for each option in the first drop-down, then use a formula to make the second drop-down show only the relevant list. This is beyond basic validation and usually requires help from someone familiar with Excel formulas.

Can someone delete the drop-down after I create it?

Yes, unless you protect the sheet. Go to Tools > Protect Sheet (or Review > Protect Sheet in desktop Excel) and set a password. This prevents others from changing validation rules, though they can still use the drop-downs normally.