Data Validation Not Working in Excel & Google Sheets

This tutorial explains why Data Validation might not be working in Excel and Google Sheets.

 

 

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 then paste data into those cells, you override the data validation set for those cells.

For example, in the picture below, there’s a data validation rule set for the range D15:D23. Excel will not allow an entry greater than 40 into any of those cells.

 

 

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.

 

 

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

 

 

Prevent 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 remains in place for those cells.

In the Ribbon, select 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 doesn’t apply.

 

 

In the example above, 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 or removed from the list – then the drop-down list you’re using may be incorrect.

 

 

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

 

 

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.

Unprotect Sheet

If your worksheet is protected, then the Data Validation option isn’t available.

In the Ribbon, select Review, Protect > Unprotect Sheet to remove the protection. This enables Data Validation.

 

 

Unshare Workbook

It’s also not available if your file is shared.

 

 

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

 

 

Ungroup Tabs

It’s also not available if you are in group mode.

 

 

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 affect the data validation rules.