How to Remove Data Validation in Excel

To remove data validation in Excel, select the cells, go to Data > Data Validation, select Clear All on the Settings tab, and select OK. The rule, its drop-down arrow, input message and error alert all go away, but the values already typed in the cells stay.

This guide covers Excel for Microsoft 365, Excel 2024 and 2021 on Windows, with notes for Mac and Excel for the web. It shows how to clear rules from selected cells, find and clear every rule on a sheet, remove rules by pasting, use a macro for a whole workbook, and fix a grayed-out Data Validation button. Steps follow Microsoft Support, checked in October 2026.

Remove Data Validation From Selected Cells

These steps follow Microsoft’s Remove a drop-down list page. The same Clear All button removes every kind of rule, not just lists.

1. Select the cell or cells with the rule. Hold Ctrl and click to add cells that aren’t next to each other. Result: the cells are highlighted.
2. On the Data tab, in the Data Tools group, select Data Validation. Result: the Data Validation box opens on the Settings tab.
3. If Excel says the selection contains more than one type of validation and asks whether to erase the current settings, select OK. Result: the box opens with blank settings.
4. Select Clear All. Result: Allow resets to Any value. If you used a Date rule and want a calendar instead, see how to add a pop-up calendar to a cell in Excel.
5. Select OK. Result: the rule is gone. Drop-down arrows disappear and you can type anything in the cells.

Find and Remove Every Rule on a Sheet

If you don’t know which cells have rules, let Excel find them first. This avoids missing rules hidden in cells you can’t see.

1. Press Ctrl + G and select Special. You can also go to Home > Find & Select > Go To Special. Result: the Go To Special box opens.
2. Choose Data validation, then All, and select OK. Result: Excel selects every cell on the sheet with a rule.
3. Go to Data > Data Validation, confirm the multiple-types prompt if it appears, then select Clear All > OK. Result: every rule on the sheet is removed.

Choose Same instead of All to select only the cells that share the rule of the cell you started in. That’s useful when you want to remove one rule but keep others. For a quick look, Home > Find & Select > Data Validation also selects every cell with a rule, as Microsoft’s More on data validation page notes.

To clear a whole sheet without searching, you can also select every cell with the Select All triangle at the top-left corner of the grid, then use Clear All. Rules on other sheets aren’t affected, so repeat this on each sheet.

Remove Rules With Paste Special

Paste Special can copy a cell’s validation rule without touching values or formatting. Microsoft’s paste options list describes Validation as pasting only the data validation rules. Copying a cell that has no rule pastes “no rule,” which removes the existing one.

1. Select a blank cell with no validation and press Ctrl + C. Result: the cell is copied.
2. Select the cells you want to clear. Result: the range is highlighted.
3. Press Ctrl + Alt + V to open Paste Special. Result: the Paste Special box opens.
4. Choose Validation and select OK. Result: the rules are removed and the values stay.

Remove Validation From a Whole Workbook With VBA

For rules spread across many sheets, a short macro is faster. It uses the Validation.Delete method from Microsoft’s VBA reference. Save a copy of the workbook first, because you can’t undo a macro.

1. Press Alt + F11 to open the Visual Basic Editor. Result: the editor opens.
2. Select Insert > Module and paste the code below. Result: the macro appears in the module.
3. Press F5 to run it. Result: every rule on every sheet in the workbook is deleted.

Sub RemoveAllValidation()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Validation.Delete
    Next ws
End Sub

To clear only the current sheet, use ActiveSheet.Cells.Validation.Delete instead. To keep the macro in the file, save it as a macro-enabled .xlsm workbook.

Change a Rule Instead of Removing It

To keep a drop-down but change its choices, open Data > Data Validation and edit the Source box instead of clearing it. Microsoft notes that if you check Apply these changes to all other cells with the same settings on the Settings tab, the change applies everywhere that rule is used. To build a new list, see how to create a drop-down list in Excel.

Excel for Mac and the Web

• Excel for the web: Microsoft’s steps are the same. Select the cells, then Data > Data Validation > Clear All > OK.
• Excel for Mac: use Data > Data Validation and select Clear All on the Settings tab. Use Cmd rather than Ctrl to select cells that aren’t next to each other.

If Data Validation Is Grayed Out

Microsoft lists these causes on its More on data validation page:

• You’re typing in a cell. Press Enter or Esc to finish editing.
• The sheet or workbook is protected or shared. Unprotect it with Review > Unprotect Sheet, or stop sharing. If you don’t know the password, Microsoft says Excel can’t recover it; ask the owner, or copy the data to another sheet and remove the rules there.
• Several sheets are grouped. Right-click a sheet tab and select Ungroup Sheets.

If a drop-down arrow is still there after Clear All, check whether it’s a filter button from an Excel table or AutoFilter, or a form control. Those aren’t data validation and are removed separately.

Frequently Asked Questions

Does removing data validation delete my data?

No. Clear All in the Data Validation box removes only the rule. Values, formulas and formatting stay. Microsoft also notes that validation doesn’t check values that were pasted or filled in, so existing entries may not meet the old rule.

Does Clear Contents remove data validation?

No. Clear Contents (or the Delete key) empties the cell but leaves the rule. Home > Clear > Clear All removes everything, including values and formatting, so use the Data Validation box when you want to keep the data.

Can I remove the input message or error alert but keep the rule?

Yes. In the Data Validation box, turn off the message on the Input Message tab, or turn off the alert on the Error Alert tab. The rule on the Settings tab stays.

Can I remove validation by pasting normal cells over it?

A regular paste replaces the rule, but it also replaces the values and formatting. Clear All or Paste Special > Validation is safer. For other cell cleanup, see how to split a cell in Excel.