Exceljet

Quick, clean, and to the point

Highlight integers only

Excel formula: Highlight integers only
Generic formula 
=MOD(A1,1)=0
Summary 

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.

Explanation 

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.

Author 
Dave Bruns
Related formulas 
Excel formula: Highlight unique values
Highlight unique values
COUNTIF simply counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the rule...
Related functions 
Excel MOD function
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 create more flexible and powerful conditional formats.
Conditional formatting based on a different cell
In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula.
How to highlight rows with conditional formatting
In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list.
How to build a search box with conditional formatting
In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once.
See also 
Conditional formatting with formulas (10 examples)
Test conditional formatting with dummy formulas
Cool things you can do with conditional formatting

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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.