Delete Data Validation in Excel: Steps and Options

To delete data validation in Excel for Microsoft 365 on Windows, select the cell or range, choose Data > Data Validation, click Clear All, then click OK. This removes the validation rule, such as a dropdown list, but leaves the cells’ existing values and formatting in place.

What data validation does in Excel

Data validation sets rules for what people can enter into selected cells. A rule can restrict entries to whole numbers within a range, dates after a particular day, text of a certain length, or values from a list. It can also display an input message or an error alert when someone enters a value that does not meet the rule.

A common example is a status column with a dropdown containing choices such as Open, In progress, and Done. The dropdown is only one way to interact with the rule: a validation rule can also accept typed entries, restrict numeric values without showing a list, or apply an error message. Removing validation means Excel stops enforcing that rule for the selected cells. It does not erase the choices already stored in those cells.

Validation is attached to cells, not to the workbook as a whole. One worksheet can contain many unrelated rules, and neighboring cells can have different rules. That is why selecting the right cells before clearing anything is important.

Remove validation from selected cells

Select the cell or range that has the rule. To select a range, drag across it, or select the first cell and Shift-click the last cell.
Open the Data tab on the ribbon and choose Data Validation.
In the Data Validation dialog box, select Clear All.
Choose OK to apply the change.

For example, if a dropdown is in D2:D50, select D2:D50 before clearing validation. The rule is removed from those cells, while text already entered in D2:D50 stays there. If the rule should be removed from just one cell, select only that cell. Avoid selecting an entire column unless you intend to remove validation from every cell in that column.

If you are not sure where the rules are, use Excel’s selection tools before opening the dialog. On Windows, choose Home > Find & Select > Go To Special, select Data Validation, and choose All to select cells that have validation. Choose Same instead to select cells with the same validation settings as the active cell. Then use Data > Data Validation > Clear All. Review the highlighted selection before proceeding, especially if the worksheet has several kinds of rules.

Choose the right cells before clearing a rule

There are several sensible ways to limit the change, depending on what you know about the worksheet:

One cell: Select the cell if only one dropdown or restriction is unwanted.
A known block: Select the exact range, such as D2:D50, if you know where the rule applies.
Cells with the same rule: Use Go To Special and the Same option when the active cell has the rule you want to remove and you want to find matching validation elsewhere in the selection.
Every validated cell on the sheet: Use Go To Special and All, but check the selected cells carefully. This can include rules for dates, numbers, and other inputs, not just visible dropdowns.

When a selection includes different validation rules, Excel may not present one set of settings that describes every selected cell. Clear All is intended to remove validation from the selected cells, but checking the selection first helps prevent a broad, unintended change. If rules need different treatment, work on one range at a time.

What Clear All removes, and what it leaves behind

Clear All in the Data Validation dialog removes the validation settings from the selected cells. It does not delete the cells’ contents, formulas, or number formats. A cell containing Done will still contain Done after its dropdown rule is removed. This is usually the best choice when the goal is to allow unrestricted entry while preserving the existing worksheet data.

Do not confuse this dialog’s Clear All button with Home > Clear > Clear All. The latter is a broader worksheet command: it can clear contents and formatting as well as validation and other cell features. Use it only when you really intend to clear much more than the validation rule. If you want to remove a value but retain the validation, select the cell and press Delete on Windows; clearing the contents does not itself remove the rule.

Likewise, deleting a row or column is not a good substitute for removing validation when you want to keep the cells. It removes worksheet structure and may affect formulas, references, and adjacent data. Use the validation dialog to change the rule without disturbing the rest of the sheet.

Remove a dropdown without deleting its existing entries

People often ask whether removing a dropdown also removes the words selected from it. It does not. The list is a restriction or prompt for future entries; the selected text is ordinary cell content. After clearing the rule, the cell still contains its last value, and users can edit that value freely.

For example, suppose E2 contains Approved and has a list rule allowing Approved, Pending, or Rejected. Clearing validation from E2 leaves Approved in the cell. You can then keep that value, replace it with another value, or clear the contents separately. If you remove the rule from a range containing many entries, those entries remain, including any that may not match the former list.

This distinction matters when a workbook is being prepared for wider data entry. Removing validation may allow inconsistent spellings or unexpected values, even though it preserves current data. If reports, formulas, or filters depend on a fixed set of choices, consider whether users should instead receive a revised list or a wider rule.

Check existing entries and validation sources

Excel does not automatically clean up values that were entered before a rule was removed. Also, a cell can contain a value that violates its current rule. This can happen when data is pasted into the cell, when a rule is changed after entries were made, or when validation settings allowed certain actions. A dropdown that looks correct is not proof that every existing entry follows the rule.

If your aim is to find questionable entries rather than remove the rule, keep validation in place and use Data > Data Validation > Circle Invalid Data, when that command is available in the Data Validation menu. Excel marks entries that do not meet their current rules. After correcting the entries, you can remove the circles using the related Clear Validation Circles command. These circles are review indicators, not cell contents, and they do not replace a careful check of the data.

For a list rule, the allowed choices may come from a typed list or from a range elsewhere in the workbook. Clearing validation from the input cells does not delete that source list. If the source range is used by other dropdowns or formulas, changing or deleting it could affect those other parts of the workbook. Remove the rule from the input cells first, and only change the source list if you have confirmed it is no longer needed.

Excel for Mac and Excel for the web

In Excel for Mac, select the cells and use Data > Data Validation, then choose Clear All in the validation dialog and confirm. The ribbon and dialog can look different from Windows, but the key distinction remains: Clear All in the validation dialog removes the rule, while a general clear command can remove other cell properties too. On Mac, Command+Z undoes a recent change; on Windows, use Ctrl+Z.

Excel for the web also provides data-validation controls from the Data tab, but its interface and available dialog options may differ from the Windows desktop app. If you cannot find a control for removing a rule, open the workbook in the desktop version and follow the steps above. When working in a shared workbook, make sure the change has synced before closing the browser or app.

Common problems when deleting validation

The dropdown arrow is gone, but the old value remains: That is expected. Clearing validation removes the rule, not the cell’s contents.
The cell still rejects an entry: Check that the intended cell was selected and the rule was cleared. A different rule may apply to the cell, or the worksheet may be protected and prevent changes.
Some cells still show dropdowns: The selected range may not have included every cell with a rule. Use Go To Special > Data Validation to locate validated cells, and inspect the selection before clearing.
The rule returns after pasting: Copying cells that contain validation and pasting them over the target can transfer the copied validation. Review the pasted range and clear the rule again if needed. Pasting values alone is different from copying the full cell.
You cannot change the rule: A protected worksheet can restrict editing. If you are authorized to edit it, use Review > Unprotect Sheet and provide the password if one is required. Do not try to bypass protection you are not authorized to remove.

If you make an unintended change, use Undo immediately: Ctrl+Z on Windows or Command+Z on Mac. Saving, closing, or making further changes can make recovery less straightforward. For an important workbook, save a copy before removing rules across a large or unfamiliar range.

Frequently Asked Questions

How do I remove data validation but keep the cell value?
Select the cell or range, choose Data > Data Validation, click Clear All, and click OK. This removes the rule and leaves existing values in place.

Does pressing Delete remove data validation in Excel?
No. Pressing Delete on a selected cell clears its contents, not its validation rule. Use Data > Data Validation > Clear All to remove the rule.

How do I delete a dropdown list from one cell?
Select that cell, open Data > Data Validation, choose Clear All, and confirm. The list rule disappears, but the cell’s current selection remains until you edit or clear its contents.

Can I remove validation from every cell in a worksheet?
Use Home > Find & Select > Go To Special > Data Validation > All to select validated cells, then clear validation. Check the highlighted cells first because this can remove different kinds of rules, not only dropdowns.

Why can a cell contain a value that its dropdown does not allow?
Validation may have been changed after entry, or the value may have been pasted in. Existing entries are not automatically corrected when a rule is added or changed; use Circle Invalid Data to review entries against the current rules.

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.