Excel: Formula-Based Conditional Formatting Across Rows Explanation

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?

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.

• Thanks for the explanation. The last part still confuses me a bit -- if I have an absolute reference, then for instance in row 23, are you saying every single cell in that row is running the same formula? =INT(\$B23)=\$B23. Also, why wouldn't I just reference \$B the column? i.e. Why is it important to put in the first row number (\$B2), if that is already being specified by the "Applies To" range? Nov 24, 2017 at 8:31