Ignore Blanks in Data Validation in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on July 11, 2023

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

datavalidation ignoreblanks 1

 

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.

datavalidation ignoreblanks 2

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.

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

datavalidation ignoreblank 6

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.

datavalidation ignoreblanks 2

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.

datavalidation ignoreblanks 3

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.

datavalidation ignoreblank 4

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

datavalidation ignoreblanks 5

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

datavalidation ignoreblanks 7

Circle Invalid Data

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

ignoreblanks circle

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.

datavalidation ignoreblanks 8

Ignore Blanks in Google Sheets Data Validation

Data validation in Google Sheets automatically ignores blank cells.

ignoreblanks gs

AI Formula Generator

Try for Free

See all How-To Articles