Remove Data Validation Restrictions in Excel

How-to basics · Updated November 18, 2026

To remove data validation restrictions in Excel for Microsoft 365 on Windows, select the affected cell or range, choose Data > Data Validation, then select Clear All and click OK. This removes the rules and related dropdowns or prompts, but it does not delete values already entered in the cells.

What removing data validation actually does

Data validation controls the kinds of entries Excel accepts or encourages in a cell. A rule might limit a cell to whole numbers between 1 and 10, require a date within a specified period, or show a dropdown list of approved choices. Removing validation takes away that rule from the selected cells, so users can enter values without that particular restriction.

Clearing validation is different from clearing cell contents. If a cell contains “Approved” and has a dropdown list, clearing the rule leaves “Approved” in the cell. The list arrow disappears, but the existing entry remains. Likewise, formulas, formatting, comments, and other cell properties are not removed just because you cleared a validation rule.

The Data Validation dialog’s Clear All button clears the validation settings for the selected cells. This can include the permitted-entry rule, an input message shown when a cell is selected, and an error alert shown after an invalid entry. If you want to preserve some of those settings, edit the rule instead of clearing everything.

Remove a rule from one cell or a selected range

Use this method when you know which cells have the restriction. It works for a single cell or for a contiguous range, such as D2:D50.

  1. Select the cell or range. To select separate ranges, hold Ctrl while selecting each one on Windows.
  2. Open the Data tab on the ribbon and choose Data Validation. In Excel for Microsoft 365 on Windows, the command is in the Data Tools group.
  3. On the Settings tab of the dialog, choose Clear All.
  4. Select OK to close the dialog and apply the change.

Check a cell afterward by selecting it. If it had a dropdown list, its arrow should no longer appear. You can also try entering a value that the former rule would have rejected. Do not use Delete or Backspace as a substitute: those keys clear cell contents, not the validation rule.

In Excel for Mac, select the cells and use Data > Data Validation, then choose Clear All in the dialog and confirm. Ribbon arrangement and dialog appearance can vary by release, but the validation settings are in the Data tab. If you are using Excel for the web and do not see the same Clear All control, open the workbook in the desktop app to use the full dialog.

Remove validation from cells you have not identified

If you do not know where rules are applied, use Go To Special to locate validated cells on the current worksheet. This is useful in a workbook inherited from someone else, where dropdowns or restrictions may be scattered across a form or table.

  1. Choose Home > Find & Select > Go To Special.
  2. Select Data validation. Choose All to find cells with any validation, or Same to find cells with the same validation settings as the active cell.
  3. Click OK. Excel selects matching cells on the active sheet.
  4. Open Data > Data Validation, choose Clear All, and confirm with OK.

“Same” is helpful when you want to find cells using a particular rule: first select a cell that has that rule, then use Go To Special and choose Same. “All” is broader and can select cells with different validation types. Review the selection before clearing anything, especially on a worksheet that contains both forms and ordinary data-entry areas.

Go To Special works on the current worksheet; it does not automatically search every sheet in a workbook. To remove rules from multiple sheets, repeat the process on each one. You can select an entire worksheet with the Select All button at the intersection of the row and column headings, but that is a broad change. Finding validated cells first is generally safer because it avoids changing cells that never had a rule.

Remove only a dropdown arrow or a message

Sometimes the restriction itself is useful, but a particular part of the experience is not. For example, a list may be intended to limit entries to “Open,” “In progress,” or “Closed,” while its dropdown arrow is distracting. In that case, do not choose Clear All. Open Data > Data Validation and adjust only the relevant option.

  • Hide a list arrow: Select a cell with list validation, open the Settings tab, and clear In-cell dropdown. The list rule remains, but the arrow is hidden. Users may still be required to enter an allowed value.
  • Remove an input message: Open the Input Message tab and turn off the option to show the message when the cell is selected, or revise its text.
  • Change an error alert: Open the Error Alert tab to adjust the alert or turn it off. Depending on the alert style, turning off or changing the alert affects how Excel responds to invalid typed entries; it does not necessarily remove the underlying validation rule.
  • Change the allowed entries: Edit the Settings tab, such as changing the list source or number limits, then confirm the change.

Hiding the arrow is not the same as removing a dropdown restriction. If the goal is to let people type any value, clear the validation rule. If the goal is simply to make a valid choice without using the arrow, retain the rule and hide the arrow only if that suits the worksheet’s design.

Common reasons removal seems not to work

If Clear All is unavailable or the rule remains, check the selection and the worksheet’s protection status. A protected worksheet can prevent changes to cells or their settings. If you have permission, use Review > Unprotect Sheet, make the validation change, and protect the sheet again if needed. Excel may ask for a password; you need authorization and the correct password to unprotect a sheet that is password-protected.

Also make sure the selected range includes the cell you are testing. A nearby cell may look identical but have a different rule—or no rule at all. Use Go To Special with Data validation and Same to check cells that share a rule with the active cell. If the workbook has multiple sheets, remember that a selection and validation change generally apply to the active sheet, not every sheet with a similar layout.

Another source of confusion is pasted data. Validation is a useful entry control, but it should not be treated as a security barrier: pasting or importing data can introduce values that do not comply with a rule. After removing a restriction, the reverse issue can occur: new entries are no longer checked by that rule, while old entries remain. If data quality matters, review the values separately before relying on the worksheet.

Check the data after changing the rule

Before making a broad change, consider saving a copy of the workbook or recording which cells currently have rules. This is especially worthwhile in a shared workbook, a template, or a sheet where validation guides users through a process. Excel’s Undo can often reverse a recent validation change, but it is safer not to depend on Undo as the only recovery plan.

After clearing a rule, verify both the behavior and the data:

  • Select a formerly restricted cell and check whether its list arrow or message is gone.
  • Try a harmless test entry that would previously have been rejected, then undo or remove the test value.
  • Review existing entries for blanks, spelling differences, out-of-range numbers, or dates outside the intended period.
  • Check formulas or summaries that depend on consistent labels. Removing validation does not alter those formulas, but newly inconsistent entries can affect their results.

For example, removing a list rule from a Status column may allow someone to type “In progress,” “In-progress,” or “Working” for the same state. The rule removal itself does not change existing records, but inconsistent new labels can make filters, counts, and reports harder to interpret. If the rule was removed only temporarily, decide whether to restore a revised rule after cleaning up the values.

Choose between clearing, editing, and replacing a rule

Clear All is appropriate when the restriction is no longer wanted. Edit the rule when the intended control still matters but its settings are wrong—for example, when a valid date range has changed or a dropdown list needs another option. If a rule is based on a source range, update that source rather than deleting the validation if the list remains useful.

A replacement rule can be a better solution than leaving cells unrestricted. A worksheet may need to accept a new category without accepting arbitrary spellings. In that case, update the allowed list and keep the validation. For numeric or date entries, revise the allowed limits to match the current requirements. These choices preserve more consistency than removing every rule simply because one setting needs adjustment.

One common misconception is that removing a validation rule cleans up values that were already invalid. It does not. Another is that a dropdown arrow is the restriction itself. The arrow is only a way to choose a list item; the validation settings determine what Excel allows or warns about. These distinctions help avoid deleting useful controls—or expecting a change to repair existing data.

Frequently Asked Questions

Does removing data validation delete the values in the cells?
No. Clear All in the Data Validation dialog removes validation settings from the selected cells, not their contents. Values and formulas remain unless you separately clear or replace them.

How do I remove a dropdown list but keep the selected entries?
Select the cells, choose Data > Data Validation, select Clear All, and confirm. The selected text already in the cells stays there; only the list rule and associated validation settings are removed.

Can I remove validation from an entire worksheet?
Yes. You can select the whole sheet and clear validation, but that may affect areas you did not intend to change. A more targeted approach is Home > Find & Select > Go To Special > Data validation, then clear the rule from the selected cells.

Why can I not clear data validation?
The sheet may be protected, the selected cells may not be the ones with the rule, or you may be using an interface that does not show the full dialog. Check Review > Unprotect Sheet if you are authorized, verify the selection, and try the desktop app if needed.

Does removing validation fix invalid entries already in the worksheet?
No. Removing validation stops that rule from controlling future entries in those cells, but it does not find or correct old values. Review the data separately, particularly if reports rely on consistent categories or numeric limits.

Related

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.