Yes. In Excel for Microsoft 365 on Windows, select the cells, go to Data > Data Validation, and set Allow to Whole number or Decimal. You can then require values to meet conditions such as being between two limits, greater than a minimum, or equal to a specified number.
What number validation does
Data validation sets rules for what people can enter in selected cells. For numbers, it can accept only whole numbers or decimals and can place boundaries on those values. For example, a warehouse sheet can require a nonnegative whole-number quantity, while a survey sheet can limit a rating to a decimal from 0 to 10.
Validation is a data-entry aid, not a formatting option. Applying a number format such as Currency changes how a value looks; it does not prevent someone from entering text or an out-of-range number. Conversely, validation can restrict the value while leaving its display format unchanged. You can use both features together: validate a price as a nonnegative decimal, then format the cell as currency.
Excel’s number-related choices are Whole number and Decimal. Other choices, including Date and Time, also work with values Excel stores numerically, but they are intended for those kinds of entries and have their own criteria.
Choose whole numbers or decimals
Choose Whole number when fractions are not meaningful, such as a count of people, boxes, or items. A value such as 4.5 will fail a whole-number rule even if it falls within the allowed range. Choose Decimal when entries can include a fractional part, such as a weight, measurement, or percentage.
After choosing the type, select a condition in the Data list. Common choices include between, not between, equal to, not equal to, greater than, less than, and their inclusive versions. “Greater than or equal to” includes the boundary itself; “greater than” does not.
- Inventory count: Whole number, greater than or equal to 0. This accepts zero and positive integers but not negative values or fractions.
- Test score: Whole number, between 0 and 100. Both 0 and 100 are accepted.
- Temperature reading: Decimal, between -40 and 50. Negative values and fractional readings are permitted within that interval.
- Fixed rating: Whole number, equal to 1, if the cell must contain only that value.
Set up a number rule in Excel
For a typical rule in Excel for Microsoft 365 on Windows, use these steps:
- Select the cell or range where entries should be restricted. You can select a range first, or create the rule for one cell and copy it as appropriate.
- Choose Data > Data Validation on the ribbon. In the dialog, stay on the Settings tab.
- Open the Allow list and select Whole number or Decimal.
- Choose a condition from Data, then enter the required limit in Minimum, Maximum, or the available value fields. For a range, choose between and fill in both boundaries.
- Decide whether to leave Ignore blank selected. When selected, a blank entry can be allowed; it does not mean that every nonblank value is valid.
- Optionally add an input message or configure the error alert, then choose OK.
For example, to restrict cells B2:B50 to whole-number quantities from 0 through 500, select B2:B50, choose Whole number, set Data to between, and enter 0 and 500 as the two limits. The rule accepts 0, 37, and 500; it rejects -1, 501, and 37.5 when entered normally.
Excel for Mac has a Data Validation command on the Data tab, though the dialog layout can differ slightly. Excel for the web also provides data-validation controls, but available options and the interface may vary; use the desktop app if a particular setting is missing from the web interface.
Use messages to guide people entering data
The Input Message tab can show a short instruction when a user selects a validated cell. A useful message explains the expected value, not just that there is a rule. For example: “Enter a whole-number quantity from 0 to 500.” This can reduce mistakes in shared templates and forms.
On the Error Alert tab, you can set the message Excel displays when someone enters a value that violates the rule. The alert style matters:
- Stop blocks an invalid value during ordinary typing or entry and prompts the user to correct it.
- Warning alerts the user but offers a way to continue with the invalid value.
- Information displays a notice and also allows the user to proceed.
For a rule that must be followed in normal data entry, use Stop and write a clear message naming the accepted range. Warnings and information alerts are useful when the rule is guidance rather than an absolute requirement.
When built-in number rules are not enough
Built-in Whole number and Decimal rules cover ordinary minimums, maximums, and ranges. A Custom validation rule can handle additional logic, such as accepting numbers only when they meet a condition that is awkward to express with the built-in criteria. Custom rules use an Excel formula that returns TRUE for an allowed entry and FALSE for a disallowed one.
For a custom rule applied to A2:A100 that allows numeric values from 0 through 100, use this formula. The reference A2 is the top-left cell in the selected range; Excel adjusts it for the other rows.
=AND(ISNUMBER(A2),A2>=0,A2<=100)
This allows 0, 45.5, and 100, and rejects text, -1, and 100.1. It does not require a whole number, so a fractional value such as 45.5 is valid. If fractions should be rejected, choose the built-in Whole number rule instead, or add an integer test to a custom rule. Custom rules are more flexible, but they can be harder for another person to understand and maintain.
What validation does not guarantee
A common misconception is that data validation makes a cell permanently incapable of holding an invalid value. It is best understood as a check on ordinary data entry, not as a security control or an unbreakable database constraint. Users can sometimes introduce values that do not meet the rule by pasting, filling, or copying cells. Pasting can also replace the validation rule itself, depending on what is copied.
Existing values are not necessarily cleaned up just because a rule is added. If a range already contains invalid entries, review those values separately. Excel’s Data Validation tools include Circle Invalid Data for identifying entries that fail the current rule in applicable desktop versions. You can also use filters, conditional formatting, or a separate review column to flag questionable records.
For workbooks where correctness is important, protect the sheet as appropriate, limit who can edit the workbook, and check data again before using it in reports or calculations. Sheet protection can make accidental changes harder, but it is not a substitute for access controls or independent verification. Validation improves consistency; it does not make a workbook tamper-proof.
Common problems and how to fix them
- Decimals are being rejected: Check whether Allow is set to Whole number. Change it to Decimal if fractions are valid.
- Negative numbers are blocked: Review the condition and minimum. A minimum of 0 excludes negative values; set a lower minimum if they should be allowed.
- The endpoints behave unexpectedly: Use between when both boundary values should count. A strict greater-than or less-than condition excludes its boundary.
- A blank is accepted: Check Ignore blank and whether the entry is actually empty. If a value is required, communicate that requirement and consider a separate completeness check; validation alone may not cover every way data can be pasted or changed.
- An invalid value appears despite the rule: It may have been pasted or filled into the cell, or the rule may have been overwritten. Reapply the rule and inspect existing entries rather than assuming the alert prevented every route.
- The rule seems to affect the wrong cells: Check the selected range and, for a Custom rule, confirm that its formula refers to the top-left cell of that range.
Frequently Asked Questions
Can Excel data validation allow only numbers?
Yes. Choose Whole number to accept integers only, or Decimal to accept numbers with fractional parts. Set the condition and limits that fit the cells.
Can I allow numbers only within a range?
Yes. Choose Whole number or Decimal, set Data to between, and enter the minimum and maximum. The between condition includes both endpoints.
Can I allow negative numbers?
Yes. Use a negative minimum or choose a condition that permits negative values. For example, a Decimal rule between -10 and 10 accepts negative and positive values within those limits.
Does data validation stop users from pasting invalid numbers?
Not reliably. Pasting or filling can bypass the entry check or replace the rule. Treat validation as a helpful input prompt, then review data when accuracy matters.
Can I change a rule after creating it?
Yes. Select a cell in the validated range, open Data > Data Validation, and edit the settings. If the range has inconsistent rules, inspect the whole range and reapply the intended rule consistently.
Should I use number formatting instead?
Use formatting to control appearance, such as showing a value as currency or with a fixed number of decimal places. Use validation to guide or restrict what can be entered. They address different needs and can be combined.
Related
- Copilot Not Responding in Excel? Fixes to Try
- Consolidate Values in Excel: Steps and Options
- Convert a Corrupted Excel File Online: What to Know
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Rank in Excel From Top to BottomTo rank values from highest to lowest in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0) beside the first value, then fill the…
- Color Certain Words in Excel: A Step-by-Step GuideTo color cells that contain a word, select the cells and use Home > Conditional Formatting > Highlight…
- Google Sheets ARRAYFORMULA: How It Works in PracticeIn Google Sheets, ARRAYFORMULA applies a formula to a range of cells so one formula can return results…
- How to Present PowerPoint and See Your Speaker NotesTo present PowerPoint while keeping your notes visible, open the deck in the desktop app, select Slide Show…
- Excel Row Groups: Grouping, Labels, and Naming LimitsYes—you can group rows in Excel with Data > Group, then expand or collapse the group using the…
- Excel Columns: How Many Are There in a Full Worksheet?A standard modern Excel worksheet has 16,384 columns, labeled A through XFD. That limit applies to Excel for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.