What you're actually editing when you change a drop-down box

A drop-down box in Excel is a data validation rule attached to a cell. When you click the cell, a small arrow appears and shows you a list of options to pick from. The list itself lives in one of three places: in cells somewhere on your sheet, in a named range you created, or typed directly into the validation settings. To edit the drop-down, you need to open the data validation dialog and change where Excel is pulling the list from.

The steps are the same whether you want to add new options, remove old ones, point to a different list, or delete the drop-down entirely. You do not need to know how the drop-down was originally built — Excel shows you what it's using and lets you change it.

Key Takeaways

  • Open the cell with the drop-down, go to the Data menu, and click Validity or Data Validation to see what list the drop-down is using.
  • If the list is in cells, you can edit those cells directly, and the drop-down updates automatically.
  • If the list is typed into the validation settings, you edit it in the Source field by adding, removing, or changing items separated by commas or line breaks.
  • To delete a drop-down entirely, select the cell, open Data Validation, and click Clear All.
  • If a drop-down stops working, it usually means the cells it points to were deleted or the named range no longer exists.

Finding the drop-down's source list

Click the cell that has the drop-down arrow. Open the Data menu at the top. In Excel for Windows, click Validity or Data Validation (the name varies by version). In Excel for Mac, click Validation. A dialog box opens.

Look at the Source field or List field. This shows you where the drop-down list is coming from. You will see one of three things: a range like $A$1:$A$10 (meaning cells A1 through A10), a named range like StatusOptions, or a list of items separated by commas or line breaks, like Pending, In Progress, Complete.

What you see here tells you how to edit the list. If it points to cells, go edit those cells. If it is a named range, you need to find and edit that range. If items are typed directly in, you edit them right here in this dialog.

Editing a list that lives in cells

If the Source field shows a cell range like $B$2:$B$8, the list is stored in those cells. Close the Data Validation dialog and go to those cells. Edit them the way you would edit any cell — click, type, press Enter. Add new items, delete old ones, change the wording. The drop-down updates automatically.

If you want the list to include more cells than it currently does, you have two choices. You can edit the cells themselves and then go back to Data Validation and change the range to include the new cells. Or you can create a named range that covers all the cells you might ever need, and point the drop-down to that instead. Named ranges do not shrink if some cells are empty, so this is useful if you plan to add items later.

Be careful if you delete the cells the drop-down points to. If you delete rows or columns, the drop-down may break and show an error. If this happens, open Data Validation again and point it to a new range, or delete the drop-down and start over.

Editing a list typed directly into the validation settings

If the Source field shows items like Red, Blue, Green or has items on separate lines, the list is typed directly into the validation rule. Click in the Source field and edit the text. Add new items by typing a comma and the new item, or press Enter to put each item on its own line — Excel accepts both formats.

To remove an item, select it and delete it. To change an item's wording, click in the field and edit it. Press Enter or click OK when you are done. The drop-down updates when ready.

This method works well for short, stable lists that do not change often. If your list is long or you change it frequently, storing it in cells is easier to maintain, because you can see all the items at once and edit them without opening a dialog.

Editing a drop-down that points to a named range

If the Source field shows a name like DepartmentList or StatusOptions, the drop-down points to a named range — a set of cells you or someone else gave a label to. To edit the list, you need to find those cells and edit them, just as you would with a regular cell range.

To find the cells, look at the Source field. It shows the name but not the location. In Excel for Windows, go to the Formulas menu and click Name Manager. In Excel for Mac, go to Sheet and click Manage Names. Find the name in the list, and it will show you which cells it refers to. Close the Name Manager and go edit those cells.

If the named range no longer exists — for example, someone deleted it — the drop-down will show an error. Open Data Validation and either point it to a new range or delete the drop-down and create a new one.

Deleting a drop-down entirely

Click the cell with the drop-down. Open the Data menu and click Validity, Data Validation, or Validation. Click the Clear All button at the bottom of the dialog. Click OK. The drop-down arrow disappears and the cell becomes a normal cell.

If you want to delete drop-downs from multiple cells at once, select all of them first. You can click one cell, hold Ctrl (or Cmd on Mac), and click other cells to select them individually. Or click and drag to select a range. Then open Data Validation and click Clear All.

Fixing a broken drop-down

A drop-down stops working when the list it points to no longer exists. This usually happens because someone deleted the cells the drop-down was using, or deleted a named range. The cell may show an error, or the drop-down arrow may disappear.

Open Data Validation and look at the Source field. If it points to cells that no longer exist, you will see a message or an empty field. You have two options: point it to a new range of cells, or delete the drop-down and create a new one. If you want to keep the list, create cells with the items you want, then change the Source field to point to those cells.

If the Source field shows a named range that no longer exists, you can create a new named range with the same name and point it to cells with your list. Or delete the drop-down and start fresh. Either way, you need cells with the actual list items before the drop-down can work again.

Frequently Asked Questions

Can I have the same drop-down list in multiple cells?

Yes. Create the list in cells once, then select all the cells where you want the drop-down to appear. Open Data Validation and point all of them to the same cell range. They will all show the same list and update together if you edit the source cells.

What if I want to add a drop-down to a cell that does not have one?

Click the cell, open Data Validation, and set up the Source field. Point it to cells with your list, type items directly separated by commas, or use a named range. Click OK. The drop-down appears when ready.

Can I edit a drop-down list without opening the Data Validation dialog?

Only if the list is stored in cells. Find those cells and edit them directly — the drop-down updates automatically. If the list is typed into the validation settings, you must open Data Validation to edit it.

Why does my drop-down show an error message?

The cells or named range it points to were probably deleted or moved. Open Data Validation, check the Source field, and either point it to a new range or delete the drop-down. If you want to keep the list, create new cells with the items and update the Source field.

Can I copy a cell with a drop-down to another sheet?

Yes, but be careful. If the drop-down points to cells on the original sheet, it will still point there after you copy it. If you want the copy to point to cells on the new sheet instead, you need to edit the Source field after pasting. If the drop-down uses a named range, it will work on any sheet as long as the named range still exists.