Restrict Input to Whole Numbers (or a Range)

Excel Formulas › Data Validation

All versionsData Validation

To keep a column clean — only positive whole numbers, only values in a range, no text — use Data Validation. It rejects anything that breaks the rule as it’s typed, so bad data never lands in the first place.

Quick formula: select the cells, then Data → Data Validation → Allow:
Whole number → between → 1 and 100
Pick Whole number, set the operator (between, ≥, etc.) and the limits; Excel blocks decimals, text, and out-of-range values.

How it works

The rule “whole number, 1–100” accepts or rejects each entry.

AB
1EntryResult
242OK
33.5Rejected (not whole)
4150Rejected (out of range)

The formula

This is set in the Data Validation dialog (no cell formula), or as a Custom formula:

Allow: Whole number · between · 1 and 100 // 42 OK; 3.5 and 150 rejected

How it works

The dialog does the work; you just pick the rule:

  1. Select the cells to protect, then open Data → Data Validation.
  2. Set Allow to Whole number (this alone blocks decimals and text), then choose an operator like between and enter the limits (1 and 100).
  3. As someone types, Excel checks the value against the rule and rejects anything that fails — a decimal, text, or a number outside the range.
  4. Add a friendly prompt (Input Message tab) and a clear rejection note (Error Alert tab).

Need custom logic? Use Allow: Custom with a formula — e.g. force uppercase with =EXACT(A1, UPPER(A1)), or require a value to be a multiple of 5 with =MOD(A1, 5)=0.

Try it: interactive demo

Live demo

Rule: whole number between 1 and 100. Type a value.

Result:

Variations

Decimals in a range

Switch Allow to Decimal for, say, 0 to 1 percentages.

Text length limit

Allow: Text length → less than or equal to 10 for short codes.

Custom: multiple of 5

Allow: Custom with a formula:

=MOD(A1, 5) = 0

Pitfalls & errors

Validation only checks typed input. Pasting can bypass it. Use Circle Invalid Data to catch values pasted around the rule.

“Whole number” still allows negatives unless you set the range. Use between 1 and… or greater than 0 to require positives.

Custom formula references the active cell. Write the formula for the top-left cell of your selection (e.g. A1); Excel applies it relatively to the rest.

Practice workbook

📊
Download the free Restrict Input to Whole Numbers (or a Range) practice workbook
A column with a live whole-number 1-100 validation rule, plus decimal, text-length, and custom multiple-of-5 examples and setup steps. No sign-up required.

Frequently asked questions

How do I restrict a cell to whole numbers in Excel?
Select the cells, then Data > Data Validation > Allow: Whole number, and set an operator and limits (e.g. between 1 and 100). Excel then rejects decimals, text, and out-of-range values.
How do I limit input to a number range?
In Data Validation, choose Whole number or Decimal, set the operator to 'between', and enter the minimum and maximum. Values outside the range are rejected.
How do I enforce custom rules like multiples of 5?
Use Allow: Custom with a formula, e.g. =MOD(A1,5)=0 requires multiples of 5. Write the formula for the active cell of the selection.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Prevent duplicate entries · Create a drop-down list · Dependent drop-down list