What a drop-down list does and why you'd use one

A drop-down list in Excel is a cell that shows a small arrow when you click it, and clicking that arrow reveals a list of preset choices. Instead of typing "New York" or "California" or "Texas" into a cell every time, you click the arrow and pick from the list. Excel then enters your choice into the cell automatically.

Drop-down lists solve two real problems. First, they prevent typos — if someone types "Califonia" instead of "California", your data breaks when you try to sort or filter by state. A drop-down list makes that impossible. Second, they speed up data entry. Picking from a list is faster than typing, especially when you're entering the same values over and over across many rows.

You'll see drop-down lists used for things like status fields (Open, In Progress, Closed), department names, yes/no questions, or any field where the same handful of values repeat. They work in any version of Excel — desktop, web, or Mac — though the steps differ slightly between versions.

Key Takeaways

  • Drop-down lists are created using the Data Validation tool, found in the Data menu on the ribbon.
  • You first select the cell or cells where you want the drop-down to appear, then tell Excel what choices to show.
  • You can type the list items directly into the validation dialog, or point Excel to cells elsewhere in the spreadsheet that contain your list.
  • Once created, a drop-down list shows a small arrow in the cell; clicking it reveals your preset choices.
  • You can copy a cell with a drop-down list to other cells, and the drop-down will copy along with it.

Selecting the cell or cells that will have the drop-down

Start by clicking the cell where you want the drop-down to appear. If you want drop-downs in multiple cells — for example, a whole column of status values — click the first cell, then hold Shift and click the last cell in the range you want. This selects all the cells between them at once.

You can also click the column header (the letter at the top) to select an entire column, though this is usually overkill. Most of the time you'll select just the cells you actually plan to use, so the drop-down doesn't appear in empty rows below your data.

Opening the Data Validation dialog

With your cell or range selected, look at the ribbon at the top of Excel. Click the Data tab. In the ribbon that appears, look for a button labeled Data Validation (in some older versions of Excel it may say Validity). Click it.

A dialog box will open with several tabs at the top. Make sure you're on the Settings tab — this is where you tell Excel what choices to show in your drop-down.

Entering your list of choices

In the Settings tab, you'll see a dropdown that says Allow. Click it and select List. This tells Excel you want to create a drop-down with a fixed set of choices.

Below that, you'll see a field labeled Source. This is where you enter your choices. You have two options: type them directly, or point Excel to cells that already contain them.

If you're typing the choices directly: Click in the Source field and type your choices separated by commas. For example: Open, In Progress, Closed or New York, California, Texas. Do not add spaces after the commas unless you want spaces to appear in your drop-down. When you're done typing, click OK.

If your choices are already in cells elsewhere: Instead of typing, click in the Source field and then click and drag to select the cells that contain your list. For example, if you have a list of department names in cells A1 through A5, click and drag across those cells. Excel will automatically fill the Source field with a reference like $A$1:$A$5. Then click OK.

Testing your drop-down and copying it to other cells

Click on the cell where you created the drop-down. You should see a small arrow appear on the right side of the cell. Click that arrow and your list of choices should appear. Click one to select it — the choice will appear in the cell and the list will close.

If you want the same drop-down to appear in other cells, select the cell that has the drop-down, copy it (Ctrl+C on Windows, Command+C on Mac), then select the range where you want it to appear and paste (Ctrl+V or Command+V). The drop-down will copy along with the cell, and it will work the same way in each new location.

Fixing common problems with drop-downs

If the arrow doesn't appear in your cell, the drop-down may not have been created. Go back to the cell, open the Data Validation dialog again, and check that the Allow field is set to List and the Source field has your choices in it. Click OK again.

If your drop-down shows choices you didn't intend, check the Source field in the Data Validation dialog. If you pointed to cells, make sure you selected the right range. If you typed the choices, look for extra spaces or typos. Edit the Source field and click OK.

If you want to remove a drop-down from a cell, select the cell, open Data Validation, and click the Clear All button. This removes the drop-down but leaves any value that was already in the cell.

Using a named range to organize your lists

If you have many drop-downs that use the same list of choices, or if your list of choices is long, you can create a named range. This is a way of giving a name to a group of cells so you can refer to them by name instead of by cell reference.

First, select the cells that contain your list of choices. Then go to the Formulas tab on the ribbon and click Define Name (or New Name in some versions). Type a name for your list — for example, StatusOptions or DepartmentList — and click OK. Now, when you create a drop-down, you can type that name into the Source field instead of typing the choices or selecting cells. This makes it easier to update your list later: you only have to change the cells once, and all the drop-downs that use that named range will update automatically.

Frequently Asked Questions

Can I have a drop-down that shows different choices depending on what's in another cell?

Yes, but it requires a more advanced technique called dependent drop-downs or cascading drop-downs. You would use a formula in the Source field instead of a straightforward list. This is beyond the basic steps above, but it's possible in Excel if you need it.

What happens if someone types a value that's not in the drop-down list?

By default, Excel allows it. If you want to prevent this and force people to choose only from your list, open the Data Validation dialog, go to the Error Alert tab, and set it to show an error message if someone tries to enter a value that's not on the list. You can customize the message they see.

Can I use a drop-down in Excel on the web?

Yes. The steps are similar: select your cell, go to the Data tab, click Data Validation, and enter your list. The web version of Excel has fewer options than the desktop version, but basic drop-downs work the same way.

If I delete a row that contains choices my drop-down uses, what happens?

If your drop-down points to cells (using a range like $A$1:$A$5), and you delete one of those cells, the drop-down will show an error. To avoid this, use a named range instead. If you delete rows within the named range, the range automatically shrinks, and your drop-downs keep working.

Can I make the drop-down list appear in a specific order?

Yes. If you're pointing to cells, arrange those cells in the order you want them to appear in the drop-down. If you're typing choices directly, type them in the order you want. Excel will show them in the order you provide.