Ignore Blanks in Data Validation in Excel & Google Sheets

This tutorial demonstrates how to ignore blanks within a data validation rule in Excel and Google Sheets.

 

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

Ignore Blanks When Editing a Cell

Say you 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. In this rule, the option to Ignore blank has been unticked and therefore switched off.

The Input Message has been set to ask the user to enter a number between 1 and 20, while the Error Alert also informs the user that they may not leave the cell blank.

If the user starts to edit the cell by typing into the cell and does not enter a valid number (i.e., any number less than 1 or text), an error occurs.

If the user then clicks Retry, and then removes the number leaving a blank cell, the same error occurs; the Ignore blank option is switched off in the rule.

However, if you press ESC on the keyboard, the data validation is not triggered. Therefore, even though there’s a blank cell in the data, there’s no error message.

Turn on Ignore Blank

To leave cells blank, use the Ignore blank option within Data Validation.

1. To edit the data validation rule, select the cells that contain the rule, and then, in the Ribbon, go to Data > Data Tools > Data Validation > Data Validation.
2. Tick the Ignore Blank checkbox.

3. Click on the Error Alert tab, modify the error alert to reflect the rule change, and then click OK.

Now when you delete information from a cell, leaving the cell blank, the validation rule (here, number between 1 and 20) is ignored.

Circle Invalid Data

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

If a data validation rule does not have the option to ignore blanks checked, then the blank cells are circled. However, once you have changed your data validation rule to Ignore Blanks, then these blank cells would be ignored.

Ignore Blanks in Google Sheets Data Validation

Data validation in Google Sheets automatically ignores blank cells.