To change an Excel data validation list, select a cell that uses the list, go to Data > Data Validation > Data Validation, and edit the entry in the Source box on the Settings tab. The box may contain typed choices, a cell range, or a named range; change the source itself when you need to add, remove, or replace choices.
What a data validation list controls
A data validation list adds a drop-down menu to a cell so someone can choose from approved entries, such as a project status, department, or product category. It can also limit what users type into the cell. The list is a rule attached to the cell, separate from the list of source values. Changing the rule changes where Excel gets the choices; changing the source values changes the choices while leaving the rule in place.
This distinction matters when the same choices appear in many cells. If the rule points to a range on a worksheet, editing the values in that range can be easier than opening Data Validation for every cell. If the choices were typed directly into the Source box, you edit them there instead.
Change the list in Excel for Microsoft 365 on Windows
- Select one cell with the drop-down. To change several cells at once, select them together only if they use the same validation rule.
- Choose Data > Data Validation in the ribbon. In the dialog box, open the Settings tab.
- Check that Allow is set to List, then edit the Source box. Keep the equals sign when the source is a range or a defined name.
- Choose OK, then open the cell’s drop-down to confirm that the intended choices appear.
If you select cells with different validation rules, the dialog may not show a single usable source. Edit one cell at a time, or group only cells that share the same rule. If you need to apply one rule to a range, configure it on one cell and use Paste Special > Validation to copy only the validation settings to the others; this avoids replacing their values or formatting.
To remove a choice from a range-based list, delete or replace that value in the source range. To add a choice, enter it in the range and check that the rule includes the new cell. For a typed list, edit the items directly in Source and separate them with the list separator used by your Excel settings, usually a comma.
Identify how the Source box is set up
Before editing, inspect Source. The format tells you where the actual choices live and what kind of change is appropriate:
- Typed choices: A short sequence such as
Open,In progress,Completeis entered directly in the box. Replace or edit the text there. The separator depends on regional settings, so Excel may use a semicolon in some configurations. - Cell range: A reference such as
=$A$2:$A$6points to cells that contain the choices. Edit those cells, or change the reference to include a different range. - Defined name: A source such as
=StatusChoicespoints to a named range or formula. Find and edit the name through Formulas > Name Manager, or change the cells it refers to. - Formula or spill range: Some rules use a formula to calculate the list. For example, if a dynamic array list starts in A2, a source may use
=A2#to refer to its spill range. Change the formula that produces the choices, rather than treating the displayed results as the source.
A comma-separated list is convenient for a few stable choices, but it is awkward to maintain when choices change often. Excel limits a directly typed list to 255 characters, so a longer set should be stored in worksheet cells. A range or defined name also makes the source values visible and easier to review.
Edit a list stored on a worksheet
When the Source box points to cells, go to that location and edit the entries. For example, if the source is =$A$2:$A$6, the list consists of the five cells A2 through A6. Changing the text in A4 updates what the drop-down offers, without changing the validation rule. To add a sixth choice, expand the source to include A7, or use a setup that expands automatically.
Excel does not allow a direct reference to a range on another worksheet in the Data Validation Source box in the usual way. A common solution is to define a name for the source range:
- Select the choices on the other sheet, such as the cells containing the category names.
- Choose Formulas > Define Name, enter a name without spaces, and confirm that the reference points to the intended cells.
- In the validation rule, enter the name in Source with an equals sign, for example
=CategoryList.
When the choices change later, edit the cells or adjust the name’s reference in Formulas > Name Manager. Keeping a source list on a separate worksheet can make an entry form less cluttered. You can hide that worksheet if appropriate, but hiding it does not secure the list or prevent a workbook editor from changing it.
Make the list easier to expand
A fixed source such as A2:A6 will not automatically include a new value entered in A7. That is a frequent reason an added choice fails to appear. You can update the reference manually, leave extra blank cells in the source range, or set up a source designed to grow.
One maintainable option is to store the choices in an Excel Table and use a defined name for the table’s choice column as the validation source. A table expands when a new row is added directly below it, and a defined name can provide a convenient reference to that column. Direct structured references are not accepted in every Data Validation Source box, which is why the defined-name step is useful. After setting it up, test the rule by adding a value at the bottom of the table and opening the drop-down.
In Microsoft 365, another option is a dynamic array formula that returns the desired choices and a spill reference to its results. If the formula is in A2, =A2# refers to the cells it spills into. The formula must have room to return its results, and its source data must be maintained correctly. If a spill reference is rejected in a particular workbook or Excel build, use a defined name or a regular range instead.
Check settings that affect how the list behaves
The Data Validation dialog includes more than the list source. On Settings, the In-cell dropdown option controls whether the arrow is displayed when the cell is selected. If it is cleared, the rule can still restrict entries, but users may not see the drop-down arrow. The Ignore blank setting affects how blank values are handled; it does not add a blank choice to the source list.
The Error Alert tab controls what happens when a user enters something outside the list. A Stop alert blocks an invalid entry, while Warning and Information alerts allow the user to continue after a prompt. If you want to guide users, the Input Message tab can show instructions when they select a cell. These settings are separate from the list contents, so changing Source does not automatically change the message or alert style.
To inspect invalid entries that were already present before a rule was added or changed, select a cell with the rule and choose Data > Data Validation > Circle Invalid Data. Excel can mark entries that do not match the current validation criteria. The rule does not automatically erase or repair existing cell values when its source changes.
Common problems and practical fixes
- A new item does not appear: Check whether the item is inside the source range. If the range is fixed, expand it or change the named range. Also check for an accidental blank row or a spelling difference.
- The drop-down arrow is missing: Select the cell and reopen Data Validation. Confirm Allow is set to List and In-cell dropdown is checked. The arrow normally appears when the cell is selected.
- Excel rejects the source: Verify that a range reference begins with an equals sign and that the referenced cells exist. For another worksheet, use a defined name rather than entering a direct cross-sheet reference.
- A typed list behaves like one item: Check the separator. Excel’s list separator can depend on regional settings, so use the separator recognized by your installation or put choices in cells instead.
- Some cells still use the old choices: The rule may have been changed on only one cell, or the selected cells may have had different rules. Inspect each relevant cell and apply the intended validation consistently.
- Old entries remain after a choice is removed: Validation controls future input; it does not automatically replace values already entered. Review existing data separately and correct any values that should no longer be allowed.
Copying and pasting can also affect validation. A normal paste may bring other cell properties along with the content. If your goal is to copy a rule without replacing data, use Paste Special > Validation where available, then verify the destination cells.
Excel for Mac and Excel for the web
In Excel for Mac, select the cell and use the Data tab’s Data Validation command to open the rule settings; the dialog’s labels are similar, though ribbon layout can differ by release. On a Mac, the Windows Ctrl key is generally replaced by Command for many shortcuts, but the menu route is the clearest way to find the command. Excel for the web also provides data validation controls, but some advanced editing options and dialog details can differ from the desktop app. If a rule is difficult to inspect or change in the browser, open the workbook in the desktop version of Excel.
These instructions assume a workbook you can edit. If a sheet is protected, Excel may prevent changes to validation or source cells. You may need permission to unprotect the sheet or edit the workbook. Do not remove protection unless you are authorized to do so; in shared workbooks, coordinate changes with the owner so existing entry rules are not unintentionally altered.
Frequently Asked Questions
How do I change a drop-down list in Excel?
Select a cell that uses it, choose Data > Data Validation, and edit Source on the Settings tab. If Source contains a range or name, update that source instead of replacing the rule unnecessarily.
How do I add an item to an Excel drop-down list?
Add the item to the cells used as the source and ensure the source range includes it. For a typed list, add the item in the Source box using your Excel list separator.
Can I use a list from another worksheet?
Yes. Define a name for the source cells on the other worksheet, then use that name with an equals sign in the validation Source box, such as =CategoryList.
Why can I still type a value that is not in the list?
Check that an Error Alert is enabled and set to Stop. Warning and Information alerts can allow users to continue, and a rule may have been applied to only some cells.
How do I remove data validation without deleting the cell value?
Select the cell, open Data > Data Validation, and choose Clear All in the dialog, then confirm. This removes the validation settings; it does not clear the cell’s existing contents.
Will changing the list fix values already entered?
No. Changing the source changes the choices offered and affects later entries. Review existing values separately, using Circle Invalid Data or a manual check, and correct any that no longer meet the rule.
Related
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
- Excel Dependent Drop-Down List: 3 Levels
- Excel Dependent Drop-Down Lists with Dynamic Ranges
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- PowerPoint Slideshow Loop: How to Repeat It AutomaticallyTo keep a PowerPoint slideshow looping, open the presentation in the desktop app and choose Slide Show >…
- Auto Forwarding in Outlook: Setup and Key OptionsTo set up auto forwarding in Outlook, open Settings, choose Mail > Forwarding, turn on forwarding, enter the…
- SUMIF Negative Values in Google Sheets: Formula & TipsTo add only negative numbers in Google Sheets, use =SUMIF(B2:B100,"<0"), replacing B2:B100 with the range containing your numbers.…
- Data Validation List Based on Another List in ExcelYes. In Excel for Microsoft 365, you can make a dependent data validation list by filtering the options…
- Currency Formula in Google Sheets: Convert and FormatFor a live exchange rate in Google Sheets, enter =GOOGLEFINANCE("CURRENCY:USDEUR") in an empty cell to request the US…
- Convert Text to Number in Excel Cells: Quick FixesTo convert text to numbers in Excel, select the affected cells and click the warning icon, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.