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

A drop-down box in Excel is a cell that shows a small arrow when you click it, and clicking that arrow displays a list of choices you've set up in advance. Instead of typing the same values over and over, you pick from the list. Excel calls this feature data validation.

Drop-downs are useful when you're building a spreadsheet that other people will fill in, or when you're entering data yourself and want to avoid typos. If you have a column for "Status" that should only ever say "Pending," "In Progress," or "Complete," a drop-down prevents someone from accidentally typing "Pening" or "in progres". They can only pick what's on your list.

Drop-downs also make a spreadsheet faster to use. Picking from a list is quicker than typing, especially if the entries are long or if you're entering hundreds of rows.

Key Takeaways

  • Data validation is the Excel feature that creates drop-down boxes, found in the Data menu under Validation.
  • You can type your list directly into the validation dialog, or point Excel to cells elsewhere in the spreadsheet that contain your list.
  • The drop-down only appears in the cells you select before setting up validation, so plan which columns need it before you start.
  • You can copy a cell with a drop-down and paste it to other cells to reuse the same list without setting it up again.

Setting up a drop-down by typing the list directly

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 in a column, select the entire range instead—click the first cell, hold Shift, and click the last cell you want to include.

Go to the Data menu at the top and click Validation (in Excel for Mac, it's called Validity). A dialog box opens. In the dropdown that says "Allow," select List.

In the field labeled Source (or List, depending on your version), type your choices separated by commas. For example: Pending,In Progress,Complete. Do not add spaces after the commas unless you want spaces in your list. Click OK.

Now when you click that cell, a small arrow appears on the right side. Click the arrow to see your list and pick one item. That item appears in the cell.

Setting up a drop-down using a list elsewhere in the spreadsheet

If your list is long or you want to reuse the same list in multiple places, it's cleaner to keep the list in one spot and point your drop-downs to it. Create your list in a column or row somewhere on the spreadsheet—often in a hidden area or on a separate sheet so it doesn't clutter your main data.

Select the cells where you want the drop-down. Go to Data > Validation. In the "Allow" dropdown, select List. In the Source field, type the range of cells that hold your list. For example, if your list is in cells A1 through A5, type $A$1:$A$5. The dollar signs lock the range so it doesn't shift if you copy the validation to other cells.

Click OK. Now your drop-down pulls from that list. If you later add or remove items from the source list, the drop-down updates automatically.

Copying a drop-down to other cells

Once you've created a drop-down in one cell, you can copy it to other cells without setting it up again. Click the cell with the drop-down, then copy it (Ctrl+C on Windows, Cmd+C on Mac).

Select the range where you want the same drop-down to appear. Paste (Ctrl+V or Cmd+V). Excel copies the validation rule to all those cells. If you used a cell range as your source (like $A$1:$A$5), the reference stays the same across all the pasted cells, which is what you want.

Controlling what happens when someone picks an item

By default, Excel just puts the selected item in the cell. But you can add a message that appears when someone clicks the cell, or an error message if they try to enter something that's not on the list.

Select the cells with your drop-down. Go to Data > Validation. Click the Input Message tab (or similar, depending on your version). Check the box that says "Show input message when cell is selected." Type a title and message—for example, "Choose a status" and "Pick one: Pending, In Progress, or Complete." This message appears as a small box when someone clicks the cell, reminding them what to do.

Click the Error Alert tab. Check "Show error alert after invalid entry." Choose Stop if you want to prevent invalid entries entirely, or Warning if you want to allow them but show a message. Type a title and message. Now if someone tries to type something that's not on your list, they see your error message and can either fix it or ignore the warning.

Removing or editing a drop-down

To remove a drop-down, select the cells that have it. Go to Data > Validation. Click the button that says Clear All (or delete the contents of the Source field and click OK). The drop-down disappears, but any values already in the cells stay there.

To edit a drop-down—for example, to add a new choice to the list—select a cell with that drop-down and go to Data > Validation. Change the Source field to include your new item, then click OK. If other cells use the same drop-down, you'll need to update each one, unless they all point to the same cell range (in which case you only update the range itself).

Common mistakes and how to avoid them

The most common mistake is forgetting to select the cells before creating the validation. You set up a perfect drop-down in one cell, then realize you needed it in twenty cells. The fix is straightforward: select all the cells that should have the drop-down, then go back to Data > Validation and set it up once for the whole range.

Another mistake is adding spaces after commas in your list. If you type "Pending, In Progress, Complete" (with a space after each comma), the list will show "Pending" and " In Progress" (with a leading space), which looks odd and can cause matching problems if you're using formulas elsewhere in the spreadsheet.

If you're using a cell range as your source and you later add rows above or below that range, the validation might not include your new items. Use a larger range than you think you'll need, or use a named range (a feature that lets you give a range a name and update it in one place).

Frequently Asked Questions

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

Yes, but it requires a more advanced feature called dependent drop-downs or cascading lists. You use named ranges and a formula in the Source field instead of a straightforward cell range. This is beyond basic data validation, but tutorials for "Excel dependent drop-down" will walk you through it step by step.

What if I want to let people type something that's not on the list?

Go to Data > Validation, and in the "Allow" dropdown, change it from "List" to "Custom" or "Whole Number" or whatever type of data you want. You can set rules (like "must be a number between 1 and 100") without restricting to a specific list. People can then type anything that fits your rule.

Can I make the drop-down list appear as a button instead of an arrow?

Excel's built-in data validation always shows as a small arrow. If you want a larger button, you'd need to create a form control or use a macro, which are more advanced features. For most spreadsheets, the arrow is fine and is what most people expect to see.

Does the drop-down work the same way in Google Sheets?

Google Sheets has a similar feature called data validation, found in the Data menu. The steps are nearly identical: select cells, go to Data > Data Validation, choose "List" or "List of items," and enter your choices. The main difference is the menu names and layout, but the concept is the same.