Excel: Formula-Based Conditional Formatting Across Rows Explanation

Question

I am trying to understand the logic behind how conditional formatting with formulas works.

I just created a formula (by trial and error) that highlights rows whose cell in column B contains a whole number. But I don't really understand how it works.

For this example, the formula is applied to A2 through D60 (omitting a header row):

=INT($B2)=$B2

How does this work exactly? And why, for instance, do I specifically need to use absolute cell references ($) for it to work?

Answer 1

Part 1 - The Formula
Let's say you have the following two cells (B2 and B3, respecitvely):

3
4.3

INT() will round a number down to the nearest integer.

When you run =INT(B2)=B2, it evaluates the first part, INT(B2) which will return simply 3. Then it checks is 3=3? Yes it does, so it returns TRUE for the whole formula.

When you run =INT(B3)=B3, it evaluates INT(4.3) which returns the next whole number when rounding down, in this case 4.

Then it checks 4=4.3 which returns FALSE for the overall formula.

Part 2 - The Referencing
When you have the formula applied to the range A2:D60 (I'm assuming this is the "Applies To" range), you will be running the formula on ALL cells within that range.

If you removed the absolute references in the formula, the formula would evaluate on every cell in your range, regardless of column. in C23 the formula would be =INT(C23)=C23. See below - the formula actually being run in that cell is =INT(C2)=C2. Since 6.5 is not an integer, it returns FALSE and therefore no formatting is applied.

Since you want to check only column B for the integer, you need the absolute reference. That means on cell C23, the formula being evaluated is =INT($B23)=$B23.

The "Applies To" range will then apply whatever formatting you have - so if B23 is 10, then A23:D23 should have your formatting.