To test if a number is a whole number, you can use the MOD function. In the example shown, the formula in C5, copied down, is:


This formula returns TRUE if a value is a whole number, and FALSE if not.

Generic formula



In this example, the goal is to test if a numeric value is a whole number. There are several ways to go about this. One of the easiest ways is to use the MOD function with a divisor of 1. Any whole number divided by 1 will result in a remainder of zero:

=MOD(5,1)=0 // whole numbers return zero

Any decimal number will have a remainder equal to the decimal portion of the number:


Therefore, we can simply compare the result to zero with a logical expression that returns TRUE or FALSE:

=MOD(5,1)=0 // returns TRUE
=MOD(5.5,1)=0 // returns FALSE

This is the approach taken in the worksheet as shown, where the formula in C5 is:


At each row in the data, the formula returns TRUE for whole numbers only.


Another way to solve the problem is with the INT function or the TRUNC function. In this approach, we run the value through one of these functions and compare the result to the original value. If the values match, we know we have a whole number. The formulas look like this:


Both of these formulas compare the original value in A1 to the same value after removing the decimal portion of the number (if any). Both formulas work fine, but note they behave differently with negative decimal values. For example, if A1 contains -5.5:

=-5.5=-6 // returns FALSE


=-5.5=-5 // returns FALSE

In short, the TRUNC function actually removes the decimal portion of a number, while the INT function always rounds the number down to the next whole value. This matters for negative values, because they are rounded away from zero (i.e. they become more negative). That said, it doesn't make a difference in this example. INT still returns the correct result for negative decimal numbers because the integer changes and the result of the comparison is always FALSE.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.