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

A drop-down box (also called a data validation list) is a cell in Excel that shows a small arrow when you click it. Click the arrow and you see a list of preset choices instead of typing anything you want. You pick one choice and it fills the cell.

Drop-downs prevent typos and inconsistency. If you're tracking project status as "Not Started", "In Progress", or "Complete", a drop-down means everyone types it the same way every time. Without one, you get "not started", "In progress", "COMPLETE", and "pending" all in the same column, which breaks sorting and filtering.

They also speed up data entry. Picking from a list is faster than typing, especially on a phone or tablet where typing is slower.

Key Takeaways

  • Drop-downs are created using Data Validation, found in the Data menu on Windows or the Data tab on Mac.
  • You first select the cell or cells where you want the drop-down to appear, then set the list of choices.
  • The list of choices can come from cells elsewhere in your spreadsheet, or you can type them directly into the validation dialog.
  • Once created, a drop-down shows a small arrow in the cell that users click to see and pick from the list.

Select the cell where the drop-down will appear

Click the single cell where you want the drop-down, or click and drag to select multiple cells if you want the same drop-down in several places at once. If you're creating a status column, for example, you might select cells B2 through B100 so that every row in that column has the same drop-down.

The cells don't need to be empty. If they already contain data, the drop-down will work alongside it, but the existing content won't change.

Open the Data Validation dialog on Windows

On Windows, click the Data tab in the ribbon at the top of Excel. Look for a button labeled Data Validation (it may also say "Validity" in some versions). Click it and a dialog box opens.

If you don't see Data Validation in the ribbon, look for a small arrow or dropdown next to a validation-related icon. Some versions of Excel hide it under a menu. If you still can't find it, use the keyboard shortcut: press Alt, then D, then L (one key at a time).

Open the Data Validation dialog on Mac

On Mac, click the Data tab in the ribbon. Look for Validation in the toolbar. Click it and the dialog opens.

If the toolbar is crowded, you may need to click a small arrow or "more options" button to find Validation. It's sometimes grouped with other data tools.

Choose "List" and enter your choices

In the dialog that opens, you'll see a dropdown that says "Allow" or "Criteria". Change it to List. A new field appears below it.

You now have two ways to add your choices. The first way is to type them directly into the field, separated by commas. For a status column, you would type: Not Started, In Progress, Complete. The second way is to point Excel to cells elsewhere in your spreadsheet that already contain the list. If you have your choices in cells D1, D2, and D3, you would type $D$1:$D$3 (the dollar signs lock the reference so it doesn't change if someone copies the formula).

The second method is better if your list might change later, because you only have to update it in one place. The first method is simpler if the list is short and won't change.

Set error handling and finish

Before you click OK, look for an Error Alert or Input Message tab in the dialog. You can leave these blank, or you can add a message that appears when someone tries to enter something not on the list. For example, you could set the title to "Invalid Entry" and the message to "Please choose from the list provided." This helps users understand why their entry was rejected.

Click OK when you're done. The drop-down is now active in the cells you selected. Click any of those cells and you'll see a small arrow appear. Click the arrow to see your list of choices.

Test the drop-down and troubleshoot

Click one of the cells with the drop-down and verify the arrow appears. Click the arrow and confirm your list shows up. Pick an item and confirm it fills the cell correctly.

If the arrow doesn't appear, the validation may not have been applied. Go back to the Data Validation dialog and check that you selected "List" and entered your choices correctly. If you used a cell reference like $D$1:$D$3, make sure those cells actually contain your list and that the range is correct.

If the list appears but looks wrong (missing items, extra spaces, or items in the wrong order), go back to the dialog and check your comma-separated list or your cell reference. Spaces after commas can cause problems — Not Started, In Progress (with a space after the comma) will create an item that says " In Progress" with a leading space, which won't match if someone types "In Progress" without the space.

Frequently Asked Questions

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

Yes. Instead of typing cell references like $D$1:$D$3, use the sheet name: $SheetName.$D$1:$D$3. Replace "SheetName" with the actual name of the sheet where your list lives. This is useful if you keep your choices on a hidden reference sheet and don't want users to see them.

What happens if someone pastes data into a cell with a drop-down?

By default, Excel allows pasted data even if it's not on the list. If you want to prevent this, go back to Data Validation, look for an option called "Show input message when cell is selected" or "Show error alert after invalid data", and set it to reject invalid entries. This forces users to pick from the list, even when pasting.

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

Yes, but it requires a more advanced technique called dependent drop-downs, which uses named ranges and indirect formulas. This is beyond basic data validation. Search for "dependent drop-down Excel" if you need this, or ask someone familiar with Excel formulas to set it up for you.

How do I remove a drop-down from a cell?

Select the cell, open Data Validation, and change the "Allow" dropdown back to "All" or "Any value". Click OK. The drop-down disappears and the cell becomes a normal text cell again.

Can I copy a drop-down to other cells?

Yes. Select the cell with the drop-down, copy it (Ctrl+C on Windows, Cmd+C on Mac), then select the cells where you want the drop-down and paste (Ctrl+V or Cmd+V). The validation copies along with the cell.