relative vs absolute in conditional formatting

[lezawang]

Hi
I am try to understand conditional formatting. I could not understand why the author used this formula

=$B2>75

why $B2

She explained here (below) but I did not understand. I would appreciate if you can explained easier for me. Thanks a lot.

+++++++++++++++++++++++++++++++++++++
we'll refer to cell B2, because it's in the active row.
=$B2>75
We use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B.
If we used a relative reference (B), the formula will be adjusted in each column, and won't work properly. Each cell would refer to the cell to its right, instead of refering to the cell in column B.

 

[Joe4]

If you put a "$" in front of a row or column reference, it "locks" it, so if the formula is copied to other cells, that value will not change (this is called an "ABSOLUTE Reference").
Row/column references without it are called RELATIVE references, as they change when copied.

So, where you might use something like:
=$B2 > 75
is when you want to format the WHOLE row based on the value in column B. Since you want every cell in that row to look specifically at B, you want to lock the B down.
But you do not want to lock down the row reference, if you want that to change (i.e. row 3 should look at the value in column B on row 3, etc).

Read head for a more detailed description of these references: http://www.excel-easy.com/functions/cell-references.html

 

[lezawang]

Thank you so much for your reply. I appreciate it. Let me explain how I understand that formula
$B2 >75

I am comparing each cell in column B with the value of 75. If that is True then color the row corresponding to that cell. Here what I do not understand
If I am comparing each cell in column B to 75 then why I need to lock the column? the column already locked when i move down, for example B1,B2, B3, B4 etc.

Another thing I really do not get, what tells excel to color that row? it must be somewhere to tell excel if that formula is true then go and color the row corresponding to that B cell. I just want to understand the logic behind it. Thank you so much once again

 

[Joe4]

It is important to understand how Conditional Formatting works.
Whenever you enter a Conditional Formatting formula, that formula is applied to the very first cell in your range selection, and all other cells are adjusted accordingly.

So, let's say that you want the WHOLE row to be highlighted based on the value in column B.
If you select rows 2 through 100, then the first cell in your selected ranges is cell A2.
So, if you enter the the Conditional Formatting formula:
=B2 > 75
that means that cell A2 is going to look at the value of cell B2 to determine whether or not to format cell A2.

And just like with copying Excel formulas from one range to another, Excel will automatically adjust the formula for all the other cells in your range.
So, when you move over one cell to the right to cell B2, without Absolute Referencing, the formula also moves over one cell, so the formula would actually be:
=C2 > 75
So, cell A2 looks at B2, cell B2, looks at C2, cell C2 looks at D2, etc.
This is clearly NOT what you want. You want ALL the cells in row 2 to look at B2. So you need to "lock" the column B reference in your formula down, i.e.
=$B2 > 75

There is a really easy way to see how this works.
In cell A2, enter the formula:
=B2 > 75
and copy the formula across row 2 to a bunch of other columns. Go to cell B2 and look at the formula in there.

Now do the same thing with:
=$B2 > 75
and notice what happens to those formulas.

Another thing I really do not get, what tells excel to color that row?

You do. That is what Conditional Formatting does.
When setting up Conditional Formatting, you first choose your condition (often a formula, like in this case).
Then the last step of Conditional Formatting is to choose the formatting option you want (color cell, etc).

Perhaps you may want to Google "Excel Conditional Formatting" if you are not familiar with how to use it.

 

[lezawang]

Thank you so much for your excellent explanation. I understand the absolute vs relative. But I think I did not understand how conditional formatting works. So I am still not sure but would appreciate if you can tell me the explanation below is right or wrong.

When I highlight the whole range from A2:C100, the first selected and active cell is A2. Now If I create new CF rule and type $B2>75
that means, excel is going to compare cells A2 with that formula then B2 with that formula then C2 with that formula
then go to next row and compare A3 with that formula then B3 with that formula then C3 with that formula
then go to next row and do the same thing until row 100
So the movement is from left to right, that is why we need to fix the column and that is why the formula is $B2>75
Am I right with this explanation.

About changing the color, Yes it is true i click and selected the color but my question why the row gets highlight, why not for example only the cell, what tells excel to highlight that row.

Thank you so much.

 

[Joe4]

Think of it this way:

When you select a multi-cell range to apply Conditional Formatting to, the formula that you enter in applies to the very first cell (upper left hand corner) of your selected range.
The Conditional Formatting formula for all the other cells in your selected range is "adjusted", just like when you copy a formula in Excel across a column or down a row.
So, if you "lock" a range reference with Absolute Range Referencing, that will be frozen and will not adjust for the other cells.

Try typing the formula:
=B2
in cell A2, and drag across to cell C2.
Then take a look at the formula in cells A2, B2, and C2.

Now try typing the formula:
=$B2
in cell A2, and drag across to cell C2.
Then take a look at the formula in cells A2, B2, and C2.
Notice the difference.
Conditional Formatting is going to follow that exact same pattern in the formulas in applies to each cells.

About changing the color, Yes it is true i click and selected the color but my question why the row gets highlight, why not for example only the cell, what tells excel to highlight that row.

Conditional Formatting is applied to EVERY cell that you have selected. If you are selecting whole rows, you are actually selecting EVERY cell in that row.
Basically, Conditional Formatting is applied to whatever range you have selected when you applied the CF conditions. That range could be a single cell, but doesn't have to be. It could be a whole column, a whole row, or some other range.

 

[lezawang]

Thanks once again. Yes I see your point about Absolute and Relative but I was not sure in which direction excel compare the formula with other cells. So now I think I got it, excel will compare the formula with each cell in the range starting with A2 and going to the righthand side until the end of the row then go to next row and start the comparing the formula with each cell in that row. So let say the formula again is $B2>75 and I have 2 rows in my table and they are like this

A2= 10 B2=20 C2=60
A3 = 11 B2=75 C2=55

So I highlight the range then go to CF and type that formula. Excel will start doing this, compare A2 with the formula if not true go to next cell B2 again compare it with the formula the go to next cell C2 and compare it with the formula. all comparison are false then go to next Row. excel will compare A3 with the formula if false then go to next one and compare B2 with the formula, and this time it is TRUE then excel will color that row.

Do I understand it correctly?

Now to the color part. Why excel changed the color of the whole row (Row 3). That part I still do not get it. Why not change the color of Cell B3. Which part of this process tell Excel if the comparison is true then Go and color the row. Thanks again.

 

[RoryA]

Excel will change the colour of any cell in the range you applied the CF to, based on the result of the formula. As Joe mentioned, using =$B2 when applied to A2:C100, A2:C2 all look at B2, then A3:C3 all look at B3 and so on down the rows. The reference to B is fixed regardless of which column is evaluating the formula, whereas the row number alters for each row that is evaluating it.

 

[lezawang]

Excel will change the colour of any cell in the range you applied the CF to, based on the result of the formula.

Yes I agree but why the whole row gets highlighted? why not only that cell B2. Thank you.

 

[Joe4]

Yes I agree but why the whole row gets highlighted? why not only that cell B2. Thank you.

Because that is what you are selecting to be Conditional Formatted!
Note that the formula being entered has NOTHING to do with what cells are getting formatted, that is simply the formula to determine whether or not to format the selected range.

Whatever range that you have selected when you apply the Conditional Formatting is what is going to be formatted.
If you only wanted cell B2 to be Conditional Formatting, you would only select cell B2 before bringing up Conditional Formatting and entering the formula.
If you select entire rows, then the Conditional Formatting is applied to every cell in those rows.

It really boils down to this simple point:
Whatever cells you have selected at the time that you start to enter your Conditional Formatting rules, those are the cells the Conditional Formatting will be applied to.

It is Step 1 in the process for setting Conditional Formatting below:

Step 1: Select the range you want to apply the Conditional Formatting to
Step 2: Bring up Conditional Formatting, and enter your Conditional Formatting rules
Step 3: Choose your Conditional Formatting format options