Clear / Remove Data Validation in Excel & Google Sheets

This tutorial demonstrates how to clear data validation in Excel and Google Sheets.

 

 

If you already have data validation set up in your worksheet, you can easily edit or remove the rules should your requirements change.

Remove Data Validation

Selected Range

1. 1. Select the range of cells where the data validation is set.
   2. In the Ribbon, select Data > Data Tools > Data Validation > Data Validation.

 

 

3. Click the Clear All button, and then click OK.

 

 

There are now no data validation rules in the selected cells.

Entire Worksheet

If you have more than one data validation in your worksheet, you can remove them all at once.

1. Select the entire worksheet by clicking in the select all

 

 

2. In the Ribbon, select Data > Data Tools > Data Validation > Data Validation.
3. A warning message – that there is more than one type of data validation in the selected cell – pops up. Click OK to erase the validation and continue.

 

 

4. You can then just click OK to clear the validation without setting new validation.

 

 

Entire Workbook

There isn’t a built-in function to remove data validation from a whole sheet at once; you have to click on each tab and follow Steps 1–4 above. However, it is possible to write a VBA loop that cycles through your sheets and changes the data validation settings in each.

Remove Data Validation in Google Sheets

1. Highlight the cells that contains the validation in your Google sheet, and then, in the Menu, select Data > Data Validation.
2. Click Remove Validation.

 

 

To remove data validation from the entire Google sheet, follow the Excel steps shown above.