Data Validation Not Working in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on October 21, 2022

This tutorial explains 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 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.

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

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.

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.

data validation error set rule

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.

data-validation error drop down

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 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.

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.

data validation error unprotect sheet

Unshare Workbook

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

data validation error shared

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

data-validation error stop sharing

Ungroup Tabs

It’s also not 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 affect the data validation rules.