The fastest way to change a drop-down list
To change an existing drop-down list in Excel, select the cell or range containing the list, then go to the Data tab and click Data Validation. In the dialog box that opens, you can edit the source list, change the allowed values, or modify the error message that appears when someone enters invalid data. The change applies immediately to that cell or range.
If you want to change what options appear in the drop-down, you edit the Source field in the Data Validation dialog. If you want to change how the drop-down behaves — whether it shows a warning, what that warning says, or whether it allows blank entries — you adjust the other tabs in the same dialog.
Key Takeaways
- Select the cell with the drop-down, open the Data tab, and click Data Validation to edit it.
- The Source field holds the list of options; edit this field directly to add, remove, or change items.
- You can point the Source to a range of cells elsewhere in the workbook instead of typing items one by one.
- The Error Alert tab lets you change the message that appears when someone picks an invalid option.
- Changes to the validation rule apply only to the selected cells, not to the entire column or sheet.
Editing the list of options directly
When you open Data Validation for a cell with a drop-down, the Source field shows how the list was created. If the list was typed in manually, you will see the items separated by commas or line breaks. Click in the Source field and edit the text directly: add new items, delete old ones, or change the wording of existing options.
After you make changes, click OK. The drop-down in that cell now shows the updated list. If multiple cells share the same validation rule, you must select all of them and repeat the process to update them all at once. Changing one cell's validation does not automatically change the others.
Changing the source to a cell range instead of a typed list
If your drop-down currently uses a typed list and you want to switch to pulling options from cells elsewhere in the workbook, clear the Source field and enter a range reference instead. For example, type =Sheet1!A1:A10 to pull options from cells A1 through A10 on Sheet1.
Using a range as the source is useful when your list changes often or when you want one list to feed multiple drop-downs. If you add or remove items in that range, the drop-down updates automatically without you having to edit the validation rule. The range can be on the same sheet or a different sheet, but it must be a continuous block of cells.
Changing the error message and validation behavior
When someone enters data that is not on your drop-down list, Excel shows an error message. To change what that message says, open Data Validation for the cell, then click the Error Alert tab. The Title field holds the heading of the error box, and the Error Message field holds the text that appears below it.
You can also change the Style dropdown on the Error Alert tab. Set it to Stop to prevent invalid entries entirely, Warning to show a message but allow the entry if the user clicks Yes, or Information to show a message that does not block the entry. The default is Stop, which is the most restrictive.
Allowing or blocking blank cells
By default, Excel allows users to leave a cell blank even if it has a drop-down list. If you want to require a selection, open Data Validation, click the Settings tab, and uncheck the box labeled Ignore empty. Now if someone tries to leave the cell blank, the error message will appear.
If you want to allow blanks again, check the Ignore empty box. This setting is useful when some cells in your range are optional but others are required. You can set up validation differently for different ranges: make some require a selection and others allow blanks.
Copying a drop-down list to other cells
If you have a drop-down in one cell and want to copy the same validation rule to other cells, select the cell with the drop-down, copy it, then select the range where you want the drop-down to appear and paste. Excel copies the validation rule along with any cell formatting.
Alternatively, select the cell with the drop-down, then use the fill handle (the small square at the bottom-right corner of the cell) and drag it down or across to copy the validation to adjacent cells. This method is faster for filling a column or row with the same drop-down rule.
Removing or resetting a drop-down list
To remove a drop-down entirely, select the cell or range, open the Data tab, click Data Validation, and click the Clear All button. This removes all validation rules from the selected cells. The cells will no longer show a drop-down arrow, and users can type anything.
If you want to keep the drop-down but reset it to its original options, open Data Validation and change the Source field back to the original list or range. If you no longer remember what the original list was, you may need to check a backup of the file or recreate the list from scratch.
Frequently Asked Questions
Can I change a drop-down list in Excel on a Mac?
Yes. On a Mac, the process is the same: select the cell, click the Data menu, then click Validity (which is the Mac name for Data Validation). The dialog and options are identical to the Windows version.
What if I want the drop-down to show different options based on what is selected in another cell?
This requires a dependent drop-down, which uses a formula in the Source field instead of a simple list. You create a named range for each set of options, then use an INDIRECT formula to point the validation to the correct range based on the value in another cell. This is more advanced and requires some knowledge of Excel formulas.
Can I change a drop-down list that someone else created?
Yes, as long as the sheet is not protected. If the sheet is protected, you may not be able to edit the validation rule. Ask the person who created it to unprotect the sheet, or contact your IT department if this is a shared workbook.
Will changing a drop-down list affect data that is already in the cells?
No. Changing the validation rule does not change or delete data that is already in the cells. It only affects what new entries are allowed going forward. If a cell contains a value that is no longer on the updated list, that value stays in the cell.