How to Update a Data Validation List in Excel Step by Step

To update an Excel drop-down list, select a cell that uses it, go to Data > Data Validation, edit the Source on the Settings tab, and select OK. If the list comes from worksheet cells, extend or change the source range as well. Before editing, identify where the choices are stored and whether the validation applies to one cell or a range.

What You’ll Need

An editable copy of the workbook. If the file is important, save a copy first.
A cell with the existing drop-down list, so you can inspect its validation settings.
The updated choices, either typed directly into the Source box or stored in worksheet cells.
Permission to edit the sheet if it is protected. You may need the password to change validation settings.

The steps below are for Excel for Microsoft 365 on Windows. Excel for Mac has a similar Data Validation dialog, though ribbon layout and key tips can differ. In Excel for the web, validation editing is available for many workbooks, but some options or controls may vary; open the workbook in desktop Excel if the setting you need is unavailable.

Step-by-Step

Find a cell that has the drop-down. Click a cell where you can see the drop-down arrow, or select a cell you know is governed by the list. If you are unsure which cells use it, click likely cells and check whether the arrow appears. The arrow may only show while a validated cell is selected. If the worksheet is protected, first check whether Excel allows you to select the cell; changing validation may require unprotecting the sheet.
Open its validation settings. On the ribbon, choose Data > Data Validation. In the dialog, open the Settings tab and check that Allow is set to List. The Source box tells you how the current options are supplied. If the selected cells have different validation rules, Excel may not show one clear shared source; inspect a single cell at a time before making changes.
Choose the right way to update the source. If the Source box contains typed choices separated by commas, edit that text to add, remove, or rename an option. If it contains a cell reference, such as =$A$2:$A$6, the list is stored in worksheet cells and you will usually update those cells or expand the reference. If the source is a name such as =CategoryList, edit the cells or named range behind that name rather than replacing it blindly. Keep the existing method if you can; it is less likely to break other cells that use the same list.
Edit a list typed directly into the Source box, if that is how yours is set up. Click in Source and revise the items, keeping the comma separators. For example, changing Open,In progress,Closed to Open,In progress,Waiting,Closed adds one choice. Do not add spaces around commas unless you want those spaces to be part of the displayed item. Direct-entry lists are convenient for a few stable choices, but the Source box has a character limit, so use a cell range for longer lists. Click OK and test the cell.
Update a list stored in cells. Go to the worksheet that contains the choices and change the relevant cells. For example, with choices in A2:A6, replace an item in that range or enter a new choice in A7. Return to a validated cell and inspect the Source box. If it still shows a reference ending at A6, the new item will not appear until you extend the range. For the layout with choices in A2:A7, the source should be =$A$2:$A$7. Enter or select the expanded range in the Source box, then click OK. The dollar signs keep the reference fixed when the rule is applied to other cells.
Make a cell-based list easier to maintain. If you expect to add choices regularly, place the choices in a single column with a heading, select the list, and choose Insert > Table (or press Ctrl+T on Windows). Confirm whether the table has headers. Give the table and its list column clear names using the Table Design tab. Then create a workbook-level defined name for the table column: choose Formulas > Name Manager > New, enter a name such as CategoryList, and set Refers to to the table column, for example =Table1[Category]. Click OK and close Name Manager. In Data Validation, use =CategoryList as the Source. A table expands as you add rows, and the defined name gives the validation rule a reusable source. Check the name and table reference if your table has a different name.
Apply the rule to the intended cells. Updating one cell’s validation does not always update every other cell that uses a similar-looking drop-down. To apply the revised rule to a range, select the target cells first, open Data > Data Validation, and set the source. If Excel asks whether to apply the changes to other cells with the same settings, review the affected cells before confirming. You can also copy a correctly validated cell and use Paste Special > Validation on the target cells; this copies the rule without copying the original cell’s value or formatting.
Test the changed list and existing entries. Click the drop-down and confirm the new options appear in the expected order. Select a new option, then check a few other cells that should share the rule. Also look at cells that already contained a choice you removed or renamed: changing the list source does not automatically replace existing cell contents. Those cells may keep an old value even though it is no longer available in the drop-down. Update them manually if needed, and consider using Data > Data Validation > Circle Invalid Data to identify values that do not meet the current rule.

When using a direct cell reference, the example above assumes that the list is on the same worksheet as the validated cell. Excel does not accept a plain reference to a range on another worksheet in the Source box. For a list on another sheet, use a defined name that refers to the source range, then enter that name in the Source box with a leading equals sign. A workbook-level name is often the most convenient choice when multiple sheets use the same list.

Common Mistakes

Adding an item below the referenced range. If the source is A2:A6, an item in A7 is outside the list until the source is extended or made dynamic.
Editing the wrong rule. A workbook can contain several drop-downs that look alike but have different sources. Check the Source field in the specific cell you need to change.
Replacing a named source with a hard-coded range. Other cells may depend on the name, and a hard-coded reference can make later maintenance harder. Find out what the name refers to before changing it.
Assuming old selections update themselves. Excel does not rewrite existing cell values when you remove or rename a choice in the source list.
Using a direct reference to a different worksheet. Use a defined name for a list held on another sheet rather than entering a cross-sheet range directly in the validation Source box.
Copying and pasting over validation accidentally. A normal paste can bring the copied cell’s validation rule with it. Use Paste Special options deliberately when you want to preserve the destination’s rule.

Tips

Store source choices in a clearly labeled column and keep the list free of blank rows. A blank within the range can create an empty-looking choice in the drop-down.
Use a table and a defined name when the list changes often. This makes additions easier to maintain than revising a fixed range each time.
Keep the source list on a dedicated worksheet if that helps keep the workbook tidy. You can hide the sheet after confirming that users can still use the drop-down.
Use consistent spelling and spacing in the source values. Excel treats entries such as In progress and In progress as different text, even if the difference is hard to notice.
Before applying a revised rule to many cells, test it in one cell and confirm the result. Then extend it to the full intended range.
If the worksheet is shared with colleagues, explain where the source list lives and who should update it. That helps prevent someone from changing the validation rule when they only meant to add a choice.

Troubleshooting

The new choice does not appear: Reopen the validation dialog and check the Source. If it is a range, confirm that the new cell falls inside the reference and that it is not blank. If the Source is a name, open Formulas > Name Manager and verify that the name points to the updated cells. If the list is a table, make sure you added the new item to the table column rather than just to a nearby cell.

The Data Validation command is unavailable or changes are blocked: The worksheet may be protected, the workbook may be opened with restricted editing, or you may not have permission to edit it. Check Review > Unprotect Sheet if you are authorized and have the password. If the workbook is in a shared or read-only location, make an editable copy or ask its owner for access. Do not remove protection without permission.

The rule changes in one cell but not the others: The cells may have separate validation rules. Inspect a representative cell from each area, then apply the intended rule to the correct range. If the cells are supposed to share the rule, select them together and edit the validation settings, or copy a cell with the correct rule using Paste Special > Validation.

A choice appears, but Excel rejects it when typed: The cell may have an error alert enabled that allows only listed values. Use the drop-down to select the exact item, or check for extra spaces and spelling differences in the source. If free typing should be permitted, open Data > Data Validation and review the Error Alert tab. Disabling the alert changes how users are warned; it does not add missing choices to the list.

The source name is not recognized: Check its spelling in Name Manager and confirm that it is available to the sheet. A name scoped only to a different worksheet may not work as expected elsewhere. For a shared drop-down, use a workbook-level name and enter it in the Source box with the equals sign, such as =CategoryList.

Frequently Asked Questions

Can I add a drop-down choice without rebuilding the validation rule?
Yes. If the Source is a cell range, add the choice inside the referenced range or expand the range. If the source is a table-backed defined name, add a row to the table. For a short list typed directly into Source, edit the text in the validation dialog.

How do I find where a drop-down list gets its values?
Select a cell with the list and open Data > Data Validation. Read the Source box. A cell reference points to worksheet cells, a name points to a defined name, and comma-separated text means the choices were typed directly into the rule.

Can a validation list use values from another worksheet?
Yes. Create a defined name that refers to the cells on the other sheet, then use that name as the list source. A direct cross-sheet cell reference entered in the Source box is not the usual supported method.

Why can someone still see an old value that I removed?
Changing the source list affects future selections, not existing cell contents. Replace old values manually. To locate entries that are no longer valid, use Data > Data Validation > Circle Invalid Data, where available.

Does the update work in Excel for Mac or Excel for the web?
Excel for Mac includes Data Validation with a similar workflow, though ribbon placement and shortcuts differ from Windows. Excel for the web can handle many validation changes, but if a particular source type or dialog option is missing, open the workbook in desktop Excel and make the change there.

Will a table automatically add new choices to a drop-down?
A table can grow when you add entries, but the validation rule still needs a source that refers to the table column. A defined name pointing to that column is a practical way to connect the list to the drop-down. Test the new row after adding it to confirm that the name and validation source are working together.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.