To edit a data validation restriction in Excel for Microsoft 365 on Windows, select the cell or range, choose Data > Data Validation, change the rule on the Settings tab, and select OK. The same dialog lets you update dropdown sources, input messages, and error alerts—or remove the restriction with Clear All.
What Excel data validation controls
Data validation sets rules for what a cell should accept. It can limit entries to whole numbers, decimals, dates, times, text lengths, a list of choices, or values that meet a custom formula. A rule might allow dates only within a reporting period, require a quantity between 1 and 100, or provide a dropdown of approved departments.
A restriction belongs to a cell, not to the entire workbook by default. You can apply one rule to a single cell, a rectangular range, or selected nonadjacent cells. A cell may also have an input message that appears when someone selects it and an error alert that appears when someone enters a value Excel considers invalid. Those messages are related to the rule but can be edited separately from its criteria.
Validation is useful for cleaner data entry, but it is not a substitute for checking data. Existing entries can remain after a rule changes, and pasting or filling values can sometimes put entries into cells without the usual validation warning. Treat validation as a helpful guardrail, not a security feature or a guarantee that every value in the workbook is correct.
Change an existing restriction step by step
- Select a cell that has the rule. If you intend to change several cells, select the full target range before opening the dialog. If you select cells with different rules, the dialog may not show one consistent set of settings.
- On the ribbon, choose Data > Data Validation. If the command is unavailable, check whether the worksheet is protected or whether the workbook is open in a mode that limits editing.
- On the Settings tab, change the Allow choice or its criteria. For example, change Whole number to Decimal, or change a permitted range from 1–10 to 1–20.
- Review the Input Message tab and the Error Alert tab if the instructions or warning should change too. Choose OK to save the edit.
- Try a permitted value and an intentionally invalid value in a test cell. Confirm that the rule and its messages behave as intended before distributing the workbook.
Changing a rule does not automatically repair old entries that fail the new criteria. For instance, if a cell already contains 25 and you change its allowed whole-number range to 1–20, Excel does not necessarily erase the 25. You may need to find and correct existing entries separately.
Edit a dropdown list
For a dropdown, select a cell that uses the list and open Data > Data Validation. On Settings, confirm that Allow is set to List. Then edit the Source field. The source may contain typed choices or refer to cells that hold the choices. Make sure In-cell dropdown is selected if users should see the arrow in the cell.
If the source is a cell range, edit the entries in that range to change the displayed choices. For example, a list sourced from a range on a separate worksheet will reflect changes to the source cells. If the source range must grow as new choices are added, consider using an Excel table for the list values and checking that the validation source continues to include the intended entries. A direct cell reference to a range on another worksheet may require a named range; Excel’s validation source box does not accept every kind of cross-sheet reference in the same way as a normal worksheet formula.
Typed list items are convenient for a short, fixed set of choices. A source range is easier to maintain when the list changes often, because you can update the values in one place rather than reopen the validation dialog each time. Avoid putting long lists directly into the Source box; a worksheet range is clearer and easier to audit.
Adjust criteria, input messages, and alerts
The Settings tab determines what Excel accepts. Available criteria depend on the selected type. A date rule can specify a start and end date; a text-length rule can limit the number of characters; and a list rule can offer selectable entries. The Ignore blank option affects whether an empty cell is allowed under the rule. Review it rather than assuming that every validated cell must contain something.
An input message is guidance, not a restriction. For example, it can tell a user, “Enter a whole number from 1 to 20.” To edit it, open the Input Message tab and change the title or message, or clear the option to stop displaying it. An error alert controls the response when Excel detects an invalid entry. Its styles are Stop, Warning, and Information: Stop blocks a normal invalid entry, while Warning and Information allow the user to continue after a prompt.
Keep the alert specific. “Enter a date from January 1 through March 31” is more useful than “Invalid entry,” especially in a workbook used by other people. If the rule is meant to be advisory rather than strict, choose the alert style deliberately and explain what the user should do next.
Use a custom formula when built-in rules are not enough
The Custom option on the Settings tab can test a condition that does not fit the standard number, date, or list choices. For example, it can require an entry to meet two conditions at once. With the first cell in the selected range at A2 and a whole number entered in A2, this custom validation formula allows values from 1 through 100:
=AND(A2>=1,A2<=100)
In a formula stored in HTML, the greater-than and less-than characters are represented as > and <; Excel interprets them as ordinary comparison operators when the formula is entered in the dialog. The formula returns TRUE for 50 and FALSE for 0, so 50 meets the rule and 0 does not. When applying a custom rule to a range such as A2:A100, write the references relative to the first selected cell so the test adjusts for each row. A misplaced dollar sign can make every row test the same cell, so verify the reference behavior on more than one row.
Change the cells affected or remove validation
The selection you make before opening the dialog determines which cells you are editing. To change a complete column section, select the intended cells first rather than selecting the whole worksheet by habit. Applying a new rule to a broad range can replace carefully designed restrictions in cells that should have kept their original settings.
To remove a rule, select the relevant cells, open Data > Data Validation, and choose Clear All in the dialog, then select OK. This removes the validation settings for the selected cells; it does not delete their contents. If you only want to remove the dropdown or stop showing an input message, adjust the appropriate setting instead of clearing the entire rule.
When you need to identify affected cells, select a known cell and use Data > Data Validation to inspect its settings. Excel also provides commands for circling invalid data from the Data Validation menu in desktop versions. Those circles help locate entries that violate the current rule, but correcting the values remains a separate task.
Why editing may not work
- The sheet is protected. A protected worksheet can prevent changes to validation settings. If you have permission, use Review > Unprotect Sheet. If a password is required, you need the password or help from the workbook owner; do not assume the restriction can be removed by editing the cell.
- You selected cells with different rules. Select one representative cell first to inspect its rule, then decide which cells should receive the revised settings. Applying one rule to a mixed selection can unintentionally overwrite other rules.
- The dropdown source is wrong or incomplete. Check that the source range contains the expected choices, that it has not moved, and that the in-cell dropdown option is enabled. If the source uses a named range, confirm the name still refers to the intended cells.
- Invalid values are already present. Changing a rule does not reliably clean up earlier values. Use the invalid-data checking tools or review the cells directly, then correct entries that do not meet the new rule.
- A pasted value appears to bypass the warning. Validation prompts are primarily an entry aid, not a robust enforcement boundary. Copying, pasting, filling, or formulas can leave unexpected values, so validate important imported data with a separate review process.
- The command looks different in another edition. Excel for Mac has a Data Validation dialog with similar settings, though the ribbon and dialog layout can vary. Excel for the web may expose a different subset or layout of validation controls; open the workbook in the desktop app if an option you need is unavailable.
When to edit, replace, or use another approach
Edit the existing rule when its basic purpose is still right and only the range, list, or message has changed. Replace it when the allowed type or logic is fundamentally different—for example, changing a free-form text rule into a list of approved codes. Remove it only when unrestricted entry is intentional. Before a major change, save a copy of the workbook or note the old criteria so you can restore them if the new rule affects more cells than expected.
For a process that must reject bad data even when users paste or import it, validation alone is not enough. Add a review step, use formulas or other checks to flag questionable records, and control who can change the workbook. If users need a guided form with more structured behavior, a dedicated form or database may be a better fit than a worksheet dropdown. The right choice depends on how serious an incorrect entry would be and how many people maintain the file.
Frequently Asked Questions
How do I change the options in an Excel dropdown?
Select a cell with the dropdown, choose Data > Data Validation, and edit the Source field on the Settings tab. If the source points to worksheet cells, change those cells instead. Check that In-cell dropdown is selected if the arrow is missing.
Can I edit data validation on a protected sheet?
Usually, protection prevents changing validation settings. If you are authorized, choose Review > Unprotect Sheet and provide the password if prompted. Ask the workbook owner for access if you do not have permission or the password.
Will changing a rule fix existing invalid values?
No. Editing a rule changes what Excel checks for future direct entries, but existing values may remain even if they fail the new criteria. Review the affected cells and correct invalid entries separately.
How do I remove a validation rule without deleting cell contents?
Select the cells, open Data > Data Validation, choose Clear All, and select OK. The validation settings are removed; the values already in the cells stay in place.
Why can someone paste a value that the rule should block?
Data validation is not an absolute barrier against every way of changing a cell. Pasting or filling can bypass the usual entry prompt, and formulas can produce values that do not match a rule. Check data after imports or bulk edits when accuracy matters.
Related
- Corrupt Excel File Online? Repair and Recover Data
- Corrupted Excel File Repair: What to Try First
- Convert Text to Columns in Excel: Steps, Options & Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Green Checkbox in ExcelTo add a clickable checkbox in Microsoft Excel for Microsoft 365 on Windows, select the target cell or…
- Excel Workbook Is Read Only? Causes, Fixes, and OptionsAn Excel workbook is read-only when Excel lets you view it but prevents you from saving changes to…
- Excel Power Query: Extract Text from a StringTo extract text in Excel Power Query, select the relevant column and use Transform > Extract to choose…
- How to Half Shade a Cell in Excel: Practical MethodsTo half shade a cell in Excel, place two colored triangle shapes over the cell and align them…
- Proper Excel Formatting: Clear, Reliable Spreadsheet RulesProper Excel formatting makes a spreadsheet easier to scan, understand, update, and print. In Microsoft Excel for Microsoft…
- How to Get Rid of Mail Merge in Word Without Losing TextTo get rid of mail merge in Word, first save a copy, then choose what should happen to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.