Clear / Remove Data Validation in Excel & Google Sheets

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

 

datavalidation remove cleared

 

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. Select the range of cells where the data validation is set.
    2. In the Ribbon, select Data > Data Tools > Data Validation > Data Validation.

 

Datavalidationchange ribbon

 

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

 

datavalidation remove clearall

 

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

 

datavalidation remove select all

 

  1. In the Ribbon, select Data > Data Tools > Data Validation > Data Validation.
  2. 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.

 

datavalidation remove erase

 

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

 

datavalidation remove OK

 

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.

 

datavalidation remove gs

 

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