How to change what appears in an Excel drop-down box

A drop-down box in Excel (officially called data validation) shows a list of choices when someone clicks a cell. To change what options appear in that list, you go back to the cell or cells where the drop-down lives, open the data validation settings, and edit the source list. The steps differ slightly depending on whether your list is typed directly into the validation rule or stored in cells elsewhere in the spreadsheet.

The fastest way: click the cell with the drop-down, go to the Data tab at the top, select Data Validation, and you will see the current list. From there you can add items, remove them, or point to a different range of cells entirely.

Key Takeaways

  • Click any cell with a drop-down, then go to Data > Data Validation to see and edit the list of options.
  • You can type items directly into the validation rule, or point the drop-down to a range of cells that holds your list.
  • Changes you make to the validation rule update the drop-down when ready for that cell and any others using the same rule.
  • If your list is stored in cells and you edit those cells later, the drop-down updates automatically without reopening the validation dialog.

Edit a list typed directly into the validation rule

If the drop-down options were entered as text when the validation was created, you edit them in the same place. Click the cell with the drop-down you want to change. Open the Data tab and click Data Validation (in some versions of Excel, this is under Validity or Validation depending on your region).

In the dialog box that opens, look at the Source field. If it shows a list of items separated by commas — like Red, Blue, Green, Yellow — those are your current options. Click in that field and edit the text directly. To add a new option, type a comma and the new word. To remove an option, delete it and the comma next to it. Click OK when you are done, and the drop-down updates when ready.

Change a drop-down that points to a cell range

Many spreadsheets store the list in actual cells rather than typing it into the validation rule. This is more flexible because you can change the list by editing the cells, and the drop-down updates without touching the validation settings. To see whether your drop-down works this way, click the cell, open Data Validation, and look at the Source field.

If it shows something like $A$1:$A$10 or ListName, the drop-down is pulling from cells. To change the options, you can either edit the cells directly (just click the cell in the list and type a new value), or you can change which range the drop-down points to. To change the range, open Data Validation again, click in the Source field, and type or select the new cell range. Click OK.

Add or remove a single option from an existing list

If you only need to add one or two items to a list that is stored in cells, the easiest path is to edit the cells directly. Find the range where your list lives (you can see it in the Source field of the validation dialog), click an empty cell in that range, and type the new option. The drop-down will include it the next time someone clicks the cell.

To remove an option, click the cell containing it and delete the text. If you delete from the middle of the range, you may leave a blank cell, which will show as an empty line in the drop-down. To clean this up, you can either delete the entire row or column and adjust the range in the validation rule, or you can leave the blank and users will straightforward skip over it.

explore the same drop-down to multiple cells at once

Once you have created a drop-down in one cell, you can copy it to other cells so they all show the same list. Click the cell with the drop-down, copy it (Ctrl+C or Cmd+C), select the range where you want the drop-down to appear, and paste (Ctrl+V or Cmd+V). Excel copies the validation rule along with the cell contents.

If you later edit the list — either by changing the cells the drop-down points to, or by editing the text in the validation rule — the change applies to all cells that share that rule. This is why storing your list in cells is often smarter: you edit the cells once, and every drop-down that points to them updates automatically.

Fix a drop-down that stopped working

Sometimes a drop-down stops showing options or shows an error. The most common cause is that the cell range it points to no longer exists — perhaps someone deleted a column or moved the list. Click the cell, open Data Validation, and check the Source field. If it refers to cells that are now empty or deleted, update it to point to the correct range.

Another cause is a circular reference: if the validation rule points to the same cell it is in, Excel will not allow it. Check that the Source range does not include the cell you are validating. If you see an error message when you try to open the validation dialog, the rule may be corrupted; in that case, delete the validation (click Data Validation and select Clear All) and create a new one.

Frequently Asked Questions

Can I have a drop-down that shows different options depending on what is in another cell?

Yes, but it requires a more advanced setup using named ranges or formulas. You would create separate lists for each option and use a formula in the validation rule to point to the correct list based on another cell's value. This is beyond basic data validation, but Excel tutorials on "dependent drop-downs" or "cascading lists" cover the steps.

What if I want to let people type something that is not in the drop-down list?

By default, Excel stops users from entering values outside the list. To allow both drop-down choices and free typing, open Data Validation, go to the Error Alert tab, and change the alert style from Stop to Warning or Information. Users will see a message but can ignore it and type anyway.

How do I delete a drop-down entirely?

Click the cell with the drop-down, open Data Validation, and click Clear All. The validation rule is removed and the cell becomes a normal text cell. If you want to delete the drop-down from multiple cells at once, select all of them first, then clear all.

Can I copy a drop-down to a different spreadsheet?

Yes, if the drop-down points to cells in the same spreadsheet. Copy the cell with the validation and paste it into the new spreadsheet. However, if the validation points to a named range or cells in a different sheet, you may need to recreate the rule in the new file or adjust the cell references.