# How to check if a number is a whole number in Excel

Test whether anything is left after dividing by 1: `=MOD(B2,1)=0` returns TRUE for whole numbers.

**Excel:** `=MOD(B2,1)=0` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Line | Quantity entered | Whole units? |
| L-1 | 7.5 |  |
| L-2 | 12 |  |
| L-3 | 3.25 |  |
| L-4 | 40 |  |
| L-5 | 18 |  |

Formula in C2, filled to C6: `=MOD(B2,1)=0`

Result: FALSE; TRUE; FALSE; TRUE; TRUE

## How it works

`MOD(B2,1)` is the fraction after the decimal point: 0.5 for 7.5, 0 for 12. Comparing it with 0 gives TRUE for whole numbers. Use it to catch quantities that should be whole units before an order goes out.

## Other ways

- `=B2=INT(B2)`: A number equals its whole part only if it has no decimals.
- `=B2=ROUND(B2,0)`: The same test with ROUND.

## Common mistakes

- Results of calculations can carry tiny binary errors, like 2.9999999999, and test as not whole. Round first: `=MOD(ROUND(B2,9),1)=0`.
- Formatting a cell to show no decimals doesn't make the value whole; the test looks at the stored value.

## Questions

**How do I stop people entering decimals?**

Use Data > Data Validation > Allow: Whole number.

**How do I show a label instead of TRUE or FALSE?**

Wrap it in IF: `=IF(MOD(B2,1)=0,"Whole","Has decimals")`.

**How do I check for a multiple of 6?**

`=MOD(B2,6)=0`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-a-number-is-a-whole-number