Restrict Excel Cells to 0 or 1 and Check for Blanks
Question details
The user needs selected Excel columns to contain only the numeric values 0 or 1, with no blank cells.

- 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.
Identify the input range and exclude any header cells. Decide whether existing blank cells should be filled with 0 before applying validation.
Apply whole-number validation for 0 and 1
Use this rule to restrict normal typed entries to the two permitted numeric values.
In Excel, select the cells that should contain 0 or 1, such as A2:A100.
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.
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.
Type 0 and 1 into a selected cell, then try entering 2 or text. The Stop alert should reject the invalid entries.

Fill and check the required cells for blanks
Use this check to find missing entries, including cells left blank or cleared after validation was applied.
Protect the worksheet's validation settings
Use worksheet protection to limit changes to validation rules while leaving input cells editable.
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. Open the workbook: Open your Excel workbook in WPS Spreadsheets and select the required input cells.
- 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. 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. 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.

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.




