How to Remove Data Validation in Excel

Remove from selected cells

1. Select the cells.
2. Data › Data Validation.
3. Click Clear All › OK.

The rule (and any dropdown arrow) goes; the values already typed stay.

Remove from the whole sheet

Ctrl+A, then the same steps. If Excel says the selection has several types of validation, click Yes to erase them all.

Find which cells have validation

Home › Find & Select › Data Validation. Every validated cell gets selected. Or F5 › Special › Data validation.

Paste over it

Pasting a normal cell over a validated one replaces the rule too — handy, but also how rules get lost by accident.

Button greyed out

• The sheet is protected: unprotect it.
• Several sheets are grouped: click one tab.
• The file is shared in legacy mode: turn sharing off.

Only want to remove a dropdown?

Remove a drop-down list. Setting rules up: data validation.