Excel formula: Highlight integers only

Generic formula

=MOD(A1,1)=0

Explanation

To highlight numbers that are integers, you can use a simple formula based on the MOD function. In the example shown, conditional formatting has been applied to the range B4:G11 using this formula:

=MOD(B4,1)=0

Note: it's important that CF formulas be entered relative to the "active cell" in the selection, which is assumed to be B4 in this case.

Once you save the rule, you'll see only values that are integers (whole numbers) highlighted.

How this formula works

The MOD function returns the remainder after division. With a divisor of 1, MOD will return zero for any whole number.

We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE, triggering the conditional formatting.

When the result is not zero (i.e. the number has a decimal component, and dividing by 1 leaves remainder) the formula returns FALSE.

See 40 examples of conditional formatting with formulas

Author

Dave Bruns

Related formulas

Highlight unique values

Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight unique values. However, if you want more flexibility, you can highlight unique values with your own formula, as explained in...

Related functions

Excel MOD Function

The Excel MOD function returns the remainder of two numbers after division. For example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.

Related videos

How to apply conditional formatting with a formula

In this video, we'll look at how to use a simple formula to apply conditional formatting. Formulas allow you to make more powerful conditional formatting rules...

How to make conditional formatting variable

In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use. ...

How to highlight rows with conditional formatting

In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested in without having to apply a filter. We also show you how to...

How to build a search box to highlight data

In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.