Others

Restrict Excel Cells to 0 or 1 and Check for Blanks

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

The user needs selected Excel columns to contain only the numeric values 0 or 1, with no blank cells.

How to Restrict Excel Cells to 0 or 1 and Check for Blanks
Product
Microsoft Excel
Device & OS
not provided
Scenario
Configure data validation for worksheet input cells and prevent users from changing the validation settings.
Observed behavior
Data validation has not been configured successfully, and the user is unsure how to prevent blanks or protect the rules.
Before you start

Identify the input range and exclude any header cells. Decide whether existing blank cells should be filled with 0 before applying validation.

Solution 1Recommended

Apply whole-number validation for 0 and 1

Use this rule to restrict normal typed entries to the two permitted numeric values.

1
Select the input cells

In Excel, select the cells that should contain 0 or 1, such as A2:A100.

2
Configure validation

Go to Data > Data Validation. On the Settings tab, choose Whole number under Allow, choose between under Data, and enter 0 as the minimum and 1 as the maximum.

3
Set the error alert

Clear the Ignore blank checkbox. On the Error Alert tab, enable the alert, choose Stop, and enter a message asking users to enter 0 or 1. Click OK.

4
Test the rule

Type 0 and 1 into a selected cell, then try entering 2 or text. The Stop alert should reject the invalid entries.

Apply whole-number validation for 0 and 1
Blank cells need a separate check: Clearing Ignore blank does not guarantee that every cell is filled. Existing blanks can remain, and users can clear cells or paste over validation. Check the range before relying on the data.
Work with spreadsheets in WPS Office

Restrict Spreadsheet Entries to 0 or 1 in WPS Office

Use data validation in WPS Spreadsheets to limit normal entries to whole numbers between 0 and 1, then use COUNTBLANK to check for missing values.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the required input cells.
  2. 2. Set the allowed values: Go to Data > Data Validation, choose a whole-number rule, and set the allowed range from 0 to 1.
  3. 3. Configure the alert: Clear Ignore blank if available, set a Stop error alert, and apply the rule. Test it by entering 0, 1, and an invalid value.
  4. 4. Check for missing entries: In an empty cell, enter =COUNTBLANK(A2:A100), replacing the example range with your input cells. Review any blanks before using the data.
Set a whole-number validation rule for 0 and 1Check required ranges for blanks with COUNTBLANKOpen and edit Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why can users still leave a validated Excel cell blank?

Data validation does not force users to fill every cell, and clearing a cell can bypass the rule. Use =COUNTBLANK(A2:A100) to check the required range for missing entries.

Will the validation rule fix existing invalid values?

No. Applying Data Validation does not automatically correct existing entries. Review the range and use Data > Data Validation > Circle Invalid Data if available to identify invalid values.

Should I protect the workbook or the worksheet?

Use Review > Protect Sheet to restrict changes to cells and validation settings on that worksheet. Workbook protection primarily protects the workbook structure, such as adding or deleting sheets.

Can users paste invalid values into validated cells?

Some paste operations can bypass or replace validation. Check pasted data and reapply the rule through Data > Data Validation if necessary.