To edit a data validation list in Microsoft Excel for Microsoft 365 on Windows, select a cell that uses the list, choose Data > Data Validation, and change the entries in the Source box on the Settings tab. If the source points to worksheet cells or a named range, edit those source cells instead; the dropdown updates from them.
How Excel dropdown lists are set up
A data validation list gives a cell a dropdown of permitted choices. Its entries can be typed directly into the rule, or supplied by a range of cells. The distinction matters: changing a typed list means editing the validation rule, while changing a range-backed list usually means editing the cells that provide its choices.
For example, a cell might offer the directly entered choices Open, In progress, and Closed. Another workbook might keep those same statuses in a small table on a Lists worksheet and use that range as the validation source. The dropdown can look identical in both cases, but the maintenance steps differ.
Validation rules can also control other behavior, such as whether blank entries are allowed, whether the dropdown arrow appears, and what message or error appears when someone enters an unlisted value. Editing the list does not automatically mean those other settings need to change.
Edit a list typed directly into the rule
- Select a cell that contains the dropdown. If you want to change several cells, select the intended group first, provided those cells share the same validation rule.
- Go to Data > Data Validation. In the dialog, open the Settings tab and confirm that Allow is set to List.
- In Source, edit the comma-separated entries. For example, change
Open,In progress,ClosedtoOpen,In progress,Waiting,Closed. - Choose OK, then open the cell’s dropdown to confirm that the new choice appears.
Use commas between the choices and avoid adding spaces accidentally. A space after a comma can become part of an entry, depending on how the source is entered, and may make a value look different from the text users expect to enter. Directly typed sources are intended for short, stable lists; Excel limits this source text to 255 characters. For longer or frequently updated lists, use a worksheet range instead.
If the list already contains commas as part of an individual choice, a typed comma-separated source is not a good fit because Excel uses commas as separators. Put the choices in cells and use a range-based source so the commas can remain part of the actual text.
Edit a list that uses worksheet cells
When Source contains a cell reference, the dropdown reads its choices from those cells. Select the source range or go to the referenced worksheet, then edit the entries there. For example, if the source is =$H$2:$H$4, changing H3 changes the corresponding dropdown choice. To add an item, enter it in the next appropriate source cell and adjust the source range if that cell is not already included.
To inspect the source, select a dropdown cell and choose Data > Data Validation. Read the reference in the Source box. A reference such as =$H$2:$H$10 points to cells on the same sheet as the validation cell. A reference with a sheet name, such as =Lists!$A$2:$A$8, identifies another worksheet; Excel may require a defined name for a cross-sheet list source. If the displayed source is a name rather than an address, see whether that name points to a range in Name Manager.
A common maintainable approach is to store choices in a dedicated worksheet, keep the entries in a clear single column, and give the range a meaningful defined name such as StatusChoices. Then use =StatusChoices in the Source box. To create or inspect a name in desktop Excel, use Formulas > Name Manager. When adding entries, make sure the named range includes them; adding a row below a fixed range does not necessarily expand that range on its own.
Add, remove, or reorder choices safely
For a short direct list, place the new choice in the appropriate position in the Source box, or remove the unwanted text and its separator. For a range-backed list, insert or edit source cells and check that the source range still covers the intended entries. To reorder options, rearrange the source cells or the typed sequence. The order in the dropdown follows the order of the source; Excel does not automatically alphabetize a manually maintained list.
Before deleting a choice, consider whether existing worksheet cells already contain it. Removing an option from the dropdown does not erase matching values already entered in the workbook. Those old values can remain in cells even though the choice is no longer offered, so search or filter the relevant column if the change is meant to standardize existing records too.
Also decide how to handle blank cells in a source range. A blank within the range may appear as an empty choice or affect how users perceive the dropdown. Keep the source entries together without unintended gaps, unless an empty option is deliberately useful.
Apply the change to the right cells
Validation belongs to cells, not just to a column label or worksheet. Editing the rule while one cell is selected changes that cell’s rule. To update a whole group, select the intended cells before opening Data Validation, and make sure the selected cells use compatible settings. If the selection contains different rules, review the dialog carefully rather than assuming every cell will be changed as intended.
Excel’s Data Validation dialog can offer an option to apply changes to other cells with the same settings. If it appears and matches what you want, it can help update matching cells; do not rely on it as a substitute for checking the affected range. Afterward, test a few cells, especially at the top and bottom of the intended range. When copying a correctly configured cell, Paste Special can also copy validation to other cells, but ordinary pasting may copy other cell properties or content as well.
To remove the dropdown rule rather than edit the choices, select the affected cell or range, open Data > Data Validation, and choose Clear All in the dialog. This removes the validation settings; it does not delete the cell’s existing value. Check the selected range before clearing, because the action applies to the selection.
Change messages and invalid-entry behavior
The Settings tab controls the list source and options such as Ignore blank and In-cell dropdown. The Input Message tab can show guidance when a user selects the cell. The Error Alert tab controls the message shown for entries that do not meet the rule, and its style affects whether Excel blocks, warns about, or simply informs the user about an invalid entry.
If a new choice is available but people still see an old warning, inspect the Error Alert text and settings as well as the Source. A message may mention choices that are no longer valid. Conversely, removing the error alert or changing its style does not change the list itself. Treat these as separate parts of the rule and update each one that needs revision.
Common problems and practical fixes
- The new choice is missing. Check whether you edited the actual source cells or the validation rule used by the selected cell. For a range source, confirm the new cell falls inside the referenced range.
- The source is on another worksheet. Use a defined name that refers to the source range, then enter that name in the validation Source box. This is generally clearer than trying to maintain a cross-sheet reference directly in the rule.
- The dropdown contains a blank choice. Look for empty cells inside the source range and either fill or remove them, or adjust the range to exclude them.
- Some cells changed but others did not. Select and inspect the remaining cells. They may have separate validation rules or may lie outside the range you selected.
- Users can still enter an unlisted value. Review the Error Alert tab and its style. Also remember that validation is a data-entry aid, not a security barrier: pasted or otherwise transferred data may not be reliably constrained by the dropdown.
- Choices look similar but do not match. Check for leading or trailing spaces, spelling differences, and duplicate entries in the source. Clean source text helps prevent inconsistent records.
Excel for Mac and Excel for the web
In current desktop versions of Excel for Mac, select the cells and use Data > Data Validation to open the rule settings. The dialog and available controls can differ in appearance from Windows, but the same basic distinction applies: edit the typed Source or change the cells or defined name that supply it. Mac keyboard shortcuts differ from Windows, so using the ribbon path avoids relying on a platform-specific shortcut.
Excel for the web supports common data validation tasks, but its interface and editing options can vary as Microsoft updates the service. If a workbook’s rule uses a named range, more complex source, or settings that are not exposed in the browser, open it in the desktop app and edit it there. After making a change in either environment, check the dropdown in the workbook where it will be used.
Frequently Asked Questions
How do I edit a dropdown list in Excel? Select a cell with the dropdown, choose Data > Data Validation, and change the Source on the Settings tab. If Source refers to cells or a name, edit the referenced list instead.
How do I add a new item to an Excel dropdown? Add it to the comma-separated Source for a short typed list, or add it to the source range and ensure the range or defined name includes the new cell.
Why can’t I edit the Source box? First confirm that Allow is set to List and that the worksheet is not protected in a way that prevents editing. If the cell uses a named range or a source maintained elsewhere, locate and update that source rather than expecting the dropdown itself to be editable.
Can I edit a dropdown list without changing existing cell values? Yes. Changing the validation choices does not automatically replace or delete values already in worksheet cells. Review existing entries separately if you need them to match the revised list.
How do I remove a dropdown but keep its current value? Select the relevant cells, open Data > Data Validation, and choose Clear All. The validation rule is removed while the cell contents remain.
Can I use a list from another worksheet? Yes. A common method is to define a name for the source range and use that name in the validation Source box. This also makes the rule easier to understand and maintain.
Keep the source easy to maintain
For a list that changes only occasionally and has a few short choices, a typed source is quick to edit. For a longer list, choices that may contain commas, or a list maintained by several people, a source range with a clear name is easier to review and expand. Keep the source organized, test the resulting dropdown, and check existing values after removing options. Those simple checks prevent most surprises when updating validation in a shared workbook.
Related
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
- What Column Number Is V in Excel? Find It in Seconds
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Redo Shortcut: How to Reapply an ActionIn Excel for Microsoft 365 on Windows, press Ctrl+Y to redo the last action you undid. On a…
- How to Double Space in Word on a Laptop: Quick StepsTo double-space text in Word on a laptop, select the text, then choose Home > Line and Paragraph…
- How to Insert a Checkbox in Word With a TickTo put a tick in a Word checkbox, insert a checked-box symbol for a fixed mark, or add…
- Superscript in Excel: Make Numbers Small and RaisedTo make a number small and raised in Excel, first make the cell’s contents text, select only the…
- Excel Data Validation Lists From Tables, ExplainedYes. In Excel for Microsoft 365 on Windows, turn the source range into a table, create a workbook-level…
- How to Insert a Table of Contents in a Word DocumentTo insert a table of contents in Word, apply built-in heading styles to your section titles, click where…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.