See all How-To Articles

Data Validation Not Working in Excel & Google Sheets

This tutorial will explain why data validation might not be working in Excel and Google Sheets.

 

data validation not working

 

Data validation is used to restrict the values that are allowed to be entered into specific cells in Excel. It is not perfect, however, and there are instances when the user can inadvertently enter data that is not allowed.

Data Pasted Over Validation

If you have a validation rule on a range of cells but you then paste data into those cells, you will override the data validation set for those cells.

For example, in the graphic below, there’s a data validation rule set for the range D15:D23. The cells should not allow an entry greater than 40 into the cell.

 

data-validation error cell validation

 

If you copy the data from range H13:H21 and paste into range D15:D23, any data validation rules from Column H now apply to the Ordinary Hours worked column, not the original rules from D15:D23.

 

data validation error copy

 

The validation rule doesn’t kick in unless the data is entered manually.

 

data validation error copied

Preventing the data validation rule from being removed

There are a couple of ways to avoid losing the data validation rule when copying and pasting data.   One of these would be to write some VBA code.   Another way would be to use Paste Special to paste values only.   The data validation rule would not be removed yet the data would be pasted.

Once you have done this, you can view the invalid data as the data validation rule will remain in place for those cells.

In the Ribbon, select Data > Data Validation > Circle Invalid Data.

data validation circle invalid data

Existing Data in Cells

If you set the data validation rule after you have entered the data, then the rule will not apply.

 

data validation error set rule

 

In the above graphic, setting the rule after the data is already entered will not cause a data validation error. Make sure your cells are empty, and then set the rule before typing in any data.

Incorrect Drop-Down List

If your list is amended – i.e., if data is added to the list or removed from the list, then the drop-down list that you need to select the data from may be incorrect.

 

data-validation error drop down

 

In the above drop-down list, there are two blank rows. Make sure you data for the drop-down list is correct and does not contain any blank cells.

 

data validation error dropdown data

 

Data Validation is Grayed Out

Is the Data Validation button on the Ribbon gray and unclickable? There are three reasons this could happen. The worksheet could be protected, shared, or grouped.

If your worksheet is protected, then the Data Validation option will not be available.

In the Ribbon, select Review, Protect > Unprotect Sheet to remove the Protection. This will enable Data Validation.

 

data validation error unprotect sheet

 

It will also not be available if your file is shared.

 

data validation error shared

 

In the Ribbon, select Review, Protect > Unshare Workbook to stop sharing the file. This will enable Data Validation.

 

data-validation error stop sharing

 

It will also not be available if you are in Group Mode.

 

data-validation error groupmode

 

Click on one of the individual sheet tabs to remove Group mode and enable Data Validation.

Data Validation Not Working in Google Sheets

Pasting data in a Google sheet can also bypass data validation and amend the data controlling drop-down lists. Protecting a Google sheet will not effect the data validation rules.