Ignore Blanks in Data Validation – Excel & Google Sheets

This article will demonstrate how to ignore blanks in Data Validation in Excel & Google Sheets

Data validation is created to ensure that a user enters the correct value into a cell in their worksheet.  It restricts the user into entering data that is only allowed by the data validation rule.   When creating a rule, there is an option available called Ignore Blanks.

Ignore Blanks When Editing a Cell

The ignore blanks option is only available if you are actually editing a cell that has that option switched off in the data validation applied to that cell.

Say for example we have the following worksheet:

A data validation rule has been applied to column E (Qty) where, when the user fills in the cell, the number filled in needs to be greater than 1.

If the user starts to edit the cell by typing into the cell, and does not enter a valid number (ie any number smaller than 1 or text), then an error will occur.

If the user then backspaces to remove the incorrect number (and thereby leaving the cell blank), the error will still occur.

Take note however that if you are editing data in a cell and get an error message, you can press the Esc key on the keyboard to exit the cell.   If you do this to exit your cell, data validation will not be triggered and no error message will be given.

To view any data validation errors in the data, in the Ribbon, select Data> Data Tools > Circle Invalid Data.

The cells that have data validation applied where blank cells are not allowed will be circled.

To prevent this from happening, we can make sure that the data validation rule has the “Ignore Blank” option checked.

Select the cells where the data  validation is set, and then in the Ribbon, select Data > Data Tools > Data Validation.

This option is available with any validation criteria be it whole number, decimal, list, date, time, text length or a custom format.   Make sure Ignore Blank is checked and then click OK.

If you now were to use the Circle Invalid Data option in the ribbon, only invalid data would actually be circled, blank cells would be ignored.

 

Ignore Blanks in Data Validation in Google Sheets

Data validation in Google Sheets automatically ignores blank cells.