Google Sheets Number Data Validation Explained

Yes. Google Sheets data validation can require a cell to contain a number that meets a rule, such as being greater than zero, equal to a set value, or between two limits. Choose the Number criterion in a validation rule, then decide whether Sheets should reject entries that do not qualify or merely warn about them.

What number validation does

Data validation sets conditions for what people can enter in selected cells. A number rule is useful when a spreadsheet expects measurable or countable values: quantities, scores, prices, percentages, ages, or measurements. For example, a quantity column might allow values from 1 to 500, while a rating column might allow values from 1 to 5.

The rule checks the value, not just how the cell looks. Applying a currency format does not turn text into a numeric amount, and formatting a number as a percentage does not change its underlying value. For example, a displayed value of 25% is stored as 0.25, so a rule that permits values from 0 to 1 is appropriate for percentages from 0% to 100%.

Number validation is different from choosing a number format. Formatting changes how a value appears; validation controls which entries are accepted or flagged. A cell can have both a number format and a validation rule. Dates have their own date criteria in Sheets, so use those when you want to validate calendar dates rather than general numeric values.

How to add a number rule

In Google Sheets in a web browser, select the cells you want to control and open Data > Data validation. Choose Add rule if the sidebar does not already show a rule editor. In the criteria options, choose Number, select the condition, and enter any required limit or limits.

Select the target cells, such as B2:B100. You can select a whole column if the rule should apply to future entries there too.
Open Data > Data validation, then choose Add rule.
Under the criteria, choose Number and a condition such as greater than, equal to, or between.
Enter the limit values. For a between condition, enter both the lower and upper bounds.
Choose what happens when a value fails the rule: Reject input or Show a warning. You can also add help text to explain what users should enter.
Choose Done to save the rule.

Menu labels can change slightly as Google updates Sheets, but the rule is managed from Data > Data validation. To change or remove an existing rule, select an affected cell and reopen that panel; review the range shown so you do not accidentally edit a different rule.

Number conditions and practical examples

The Number criterion offers comparisons that cover common range checks. Depending on the condition you choose, Sheets asks for one value or two. For a stock count that must be at least zero, use greater than or equal to 0. For a test score capped at 100, use between 0 and 100. For a cell that must equal a fixed code such as 7, use equal to 7.

Positive quantities: greater than 0 prevents zero and negative amounts, but still allows decimals. That may be right for weight or length, but not for a count of people.
Bounded ratings: between 1 and 5 keeps entries within the scale. If fractional ratings are acceptable, a value such as 3.5 still meets that rule.
Nonnegative balances: greater than or equal to 0 allows zero while blocking negative values.
Exclude a value: not equal to 0 permits other numbers but excludes zero. It does not, by itself, impose an upper or lower limit.
Values outside a band: not between can be useful for exceptions, but check the boundary behavior carefully and test representative values before sharing the sheet.

Choose a condition that matches the real requirement rather than stacking a vague rule onto the cells. If a value must be both positive and no more than 500, a between rule with endpoints 0 and 500 may be clearer. Decide whether zero should be allowed and choose the corresponding inclusive or exclusive comparison.

How to choose limits and handle whole numbers

Start by writing down what a valid entry means. Consider the smallest and largest permitted values, whether endpoints count, whether negative numbers are meaningful, and whether decimals should be allowed. A rule for temperatures might allow negatives and decimals; a rule for the number of tickets sold might require positive whole numbers. Testing an ordinary valid value, each boundary, and one invalid value is a simple way to catch a mistaken comparison.

A basic Number condition does not provide a separate whole-number-only choice. A between rule from 1 to 100 still allows 1.5. If you need integers, use a custom formula criterion instead. Select the range A2:A100 and set the custom formula to =AND(ISNUMBER(A2),A2=INT(A2)). The reference starts with the first cell of the selected range, so Sheets can adjust it for the other rows. In this example, 7 passes because it is numeric and has no fractional part; 7.5 fails. Add further bounds to the rule if the integers must also stay within a particular range.

If users may leave a cell empty, test that case as well. A number condition is not the same as a required-field rule. If every row must have a value, explain that requirement in help text and check for blanks separately, for example during review or with a suitable formula or conditional formatting rule.

Reject input or show a warning?

Reject input is appropriate when an invalid number should not be entered through normal editing. Sheets prevents an entry that fails the rule and prompts the editor to correct it. This is useful for controlled fields such as a rating scale or a quantity with a strict permitted range.

Show a warning allows the entry but marks it as outside the rule. Choose this when unusual values might be legitimate, such as a measurement that is usually within a range but occasionally falls outside it. A warning is a prompt to review, not a correction or a guarantee that the value is valid. Help text can make either setting more useful: state the expected unit, range, or reason for the restriction.

Validation is an entry aid, not a security control or a substitute for checking important data. Existing values are not automatically cleaned just because a rule is added. Pasting, importing, or other changes may also leave values that need review. For important records, inspect the data after changes and restrict sheet editing permissions appropriately.

Common problems and how to troubleshoot them

If an entry that looks like a number is rejected, first check whether it is actually stored as text. A leading apostrophe, spaces, or text characters can make a value fail a numeric condition. Remove unwanted characters or convert the source data to numbers, then try again. Formatting a text value to look like a number does not necessarily convert it.

Next, check the rule’s comparison and boundaries. A rule for greater than 0 rejects 0; a rule for greater than or equal to 0 accepts it. A between condition may include its endpoints, so confirm the expected behavior with test values. Also verify that you selected the intended cells and that the rule applies to the row being edited.

Locale settings can affect how people enter decimal values and separate thousands. One editor may type a decimal point while another expects a decimal comma, depending on the spreadsheet’s locale. Check File > Settings if number interpretation seems inconsistent, and ask collaborators to use a consistent format. Do not assume a displayed currency symbol or percentage sign means the underlying entry is text; inspect the cell value and formatting separately.

If a rule is set to Show a warning, an out-of-range value may remain in the cell by design. If the rule was added after data was entered, check older entries rather than assuming they have been corrected. For a quick visual audit, use conditional formatting or another review method to highlight values outside the intended range.

Google Sheets versus Excel

Both Google Sheets and Excel provide data validation, but the interfaces and some criteria differ. In Sheets, use Data > Data validation and select Number for numeric comparisons. In Excel for Microsoft 365 on Windows, the corresponding feature is on Data > Data Validation; Excel presents numeric choices such as whole number and decimal. Excel therefore has a built-in whole-number option that is not a basic Number condition in Sheets.

Do not assume that copying a workbook’s validation setup between programs will preserve every detail. Rules, warning behavior, custom formulas, and formatting can work differently after conversion. If a file is shared across both platforms, test entries at the limits and confirm that each program interprets the rule as intended. For a sheet used only in Google Sheets, configure and verify the rule there rather than relying on an Excel-specific menu or option.

Frequently Asked Questions

Can Google Sheets data validation allow only numbers?
Yes. Choose Number as the criterion and use a condition that covers the allowed values. Select Reject input if ordinary editing should block values that do not meet the condition.

Can I allow decimals but set a minimum and maximum?
Yes. Choose a between condition and enter the lower and upper limits. Decimal values within those limits are allowed unless you add a separate restriction, such as a custom formula for integers.

Does number validation make a cell required?
No. A numeric rule controls values that are entered; it is not, by itself, a required-field setting. If blanks are not acceptable, add a separate check for missing values.

Can I validate a percentage or currency amount?
Yes. Both are numeric values in Sheets. Set limits that match the underlying value: for example, 0 to 1 represents 0% to 100%. Currency formatting changes display, not the numeric basis of the rule.

Why does a number-looking entry fail validation?
It may be stored as text, contain an extra character, or fall outside the selected limits. Check the value, the comparison, and the spreadsheet locale; then test a known valid number in the same cell.

Can I use validation to fix entries already in the sheet?
No. Adding a rule does not automatically repair old values. Review existing data separately and correct or flag any entries that do not meet the requirement.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.