Understanding the Ignore Blank checkbox

There is a checkbox on the right hand side of the Data Validation dialog box called Ignore blank.
Regardless of which restriction you select in the "Allow" drop-down box, this checkbox is always available (except when you select "Any value").
This checkbox decides if blank cells can be used to bypass the data validation.
Selecting this option allows the data validation to be ignored when it encounters blank cells.

alt text

This checkbox has nothing to do with removing blanks when you are using a drop-down list.
For details on how to remove blanks from a list, refer to Cells & Ranges > Removing Blanks - Vertical List.


Restricting a Whole Number

To try and illustrate how this checkbox works lets consider restricting the data to be any whole number in a particular range.
Lets first enter the minimum whole number and the maximum whole number onto the worksheet.
Lets enter the minimum whole number in cell "C2" and lets make this value 1.
Lets enter the maximum whole number in cell "C3" and the lets make this value 10.
Then lets highlight the cell which will contain our data validation. In this case "E2".

alt text

Enter the Criteria

Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box select "Whole number" and in the "Data" box select "between".
In the "Minimum" box select the cell "C2" and in the "Maximum" box select the cell "C3".
Now the "Ignore blank" checkbox is always ticked by default but for this example we are going to untick it.

alt text

Enter Some Numbers (Ignore Blank = False)

Select cell "E2" and try and enter some whole numbers.
As you might expect only whole numbers between 1 and 10 are accepted. Anything else and you will receive the familiar error message.

alt text

Enter Some Numbers (Ignore Blank = True)

Now lets repeat this but with the "Ignore blank checkbox ticked this time.
Select cell "E2" and select (Data > Validation)(Settings tab) and tick the "Ignore blank" checkbox. Press OK.

alt text

Even though the Ignore blank checkbox has been ticked there is no impact on the data validation.
You can still only enter whole number between 1 and 10.


Enter Some Numbers (C3 is blank)

However if we introduce a blank cell into the validation criteria then we can get a different result.
Remove the number 10 from "C3" to make the cell blank.
Select "E2" and enter the number 25. Notice that there is no error message.

alt text

This option allows the data validation to be ignored if it encounters any blank cells in the validation criteria.
Most of the time you don't want this which is why the checkbox is always ticked by default.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext