by Recently we have published a few articles covering different aspects of Excel conditional formatting. Unexpectedly, it's turned out that it's not creating a rule and even not making a proper formula that represents the greatest challenge. Using proper cell references in Excel formulas appear to be the most complex part and a common source of problems.
"I had my conditional rule correct, except for the mixed references." This is what our blog readers have often reported in comments. So, why don't we invest a few minutes to figure this thing out? This will certainly save you far more time in the long run!
How relative and absolute cell references work in conditional formatting rules
In all Excel formulas, including conditional formatting rules, cell references can be of the following types:
- Absolute cell references (with the $ sign, e.g. $A$1) always remain constant, no matter where they are copied.
- Relative cells references (without the $ sign, e.g. A1) change based on the relative position of rows and columns, when copied across multiple cells.
- Mixed cells references (absolute column and relative row (e.g. $A1), or relative column and absolute row (e.g. A$1). In Excel conditional formatting rules, mixed cell references are used most often, indicating that a column letter or row number is to remain fixed when the rule is applied to all other cells in the selected range.
In Excel conditional formatting, cell references are relative to the top-left cell in the applied range . So, when making a new rule, you can simply pretend as if you are writing a formula for the upper-left cell only, and Excel will "copy" your formula to all other cells in the selected range. If your formula refers to a wrong cell, a mismatch between the active cell and the formula will occur, which will result in conditional formatting highlighting wrong cells.
Now, let me show you a few examples that demonstrate how seemingly identical formulas produce different results depending on what cell references types are used.
Example 1. Absolute column and relative row
This pattern is most typical for conditional formatting rules and in 90% of cases cell references in your Excel conditional formatting rules will be of this type.
Let's make a very simple rule that compares values in columns A and B and highlights a value in column A if it is greater than a value in column B in the same row.
If you need the detailed instructions on how to create conditional formatting rules with formulas, here you go - Creating an Excel conditional formatting rule using a formula. In this case, the formula is obvious:
=$A1>$B1
Because you always compare values in columns A and B, you "fix" these column by using absolute column references, notice the $ sign before the column letters in the above formula. And, since you are comparing the values in each row individually, you use relative row references, without $.
Example 2. Relative column and absolute row
This cell reference type is the opposite of the previous one. In this case, the row number is always constant while the column changes. You use such references when you want to check values in a given row against a certain value or against values in another row.
For example, the below formula compares values in row 1 and 2 and the rule highlights a value in row 1 if it is greater than a value in row 2 in the same column:
=A$1>A$2
Because you want the row numbers to be fixed, you use the absolute row references, with the $ sign. And, because you want to compare values in each column individually, you create the rule for the left-most column (A) and use relative column references, without the $ sign.
Example 3. Absolute column and absolute row
You use absolute row and absolute column references if you want to compare all values in the selected range with some other value.
For example, let's create a rule that highlights all values in column A that are greater than a value in cell B1. The formula is as follows:
=$A1>$B$1
Please pay attention to the use of the following references:
- $A1 - you use an absolute column and relative row references because we want to check values in all cells of column A against the value in cell B1.
- $B$1 - you use absolute column & absolute row because cell B1 contains the value you want to compare all other values against and you want this cell reference to be constant.
Example 4. Relative column and relative row
This reference type is used in Excel conditional formatting rules least of all. You use relative column & relative row references when you want to check all cells of the selected range against a certain value.
Suppose, you want to highlight all cells in columns A and B that are greater than a value in cell B1. You can simply copy the formula from the previous example and replace $A1 with A1 since you do not want to fix either row or column:
=$A1>$B$1
Remember, you write the formula for the top-left cell in your range, A1 in our case. When you create a rule with the above formula and apply it to some range, say A1:B10, the result will look similar to this:
Tip. To quickly toggle between absolute and relative references, select the cell reference in the formula bar and press the F4 function key. The reference will rotate between the four types from relative to absolute, like this: A1 > $A$1 > A$1 > $A1, and then back to the relative reference A1.
Hopefully, these simples examples have helped you fathom out the essence of relative and absolute cell references in Excel. Now that you know how to determine the appropriate reference type for your rules, go ahead and harvest the power of Excel conditional formatting for your projects. The following resources may prove helpful.
97 comments
Hey
I have the follow sheet 1, with ID and city/country names:
1234 Sydney
5678 Kimberly
9191 Milan
1213 Denmark
1415 England
1617 France
1819 Sudan
2021 Greece
2023 Ghana
2425 China
And i have the following sheet 2:
1234
2425
5678
2023
9191
2021
1213
1819
1415
1617
2777
2344
I would like to copy the city/country names from sheet 1, according to their associated ID sheet 1 - wich would make sheet 2 look as following:
1234 Sydney
2425 China
5678 Kimberly
2023 Ghana
9191 Milan
2021 Greece
1213 Denmark
1819 Sudan
1415 England
1617 France
2777
2344
Hello!
The formula below will do the trick for you:
=IFERROR(VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0),"")
You can learn more about VLOOKUP function in Excel in this article on our blog.
Thank you so much for this page!!! I have searched multiple times and spent hours trying to figure out how to do this - apply a conditional formatting rule comparing values of two columns on each row. Yours is the only explanation I found that was so written so clearly and with such good examples, that I was able to make the change I needed within a few seconds of finishing reading! I changed my rule from Cell Value = $A$2 to Cell Value = $A2 and left the Applies to =$C$2:$C$411 and every matching columns had the formatting (color green) on each row and every non-matching columns on the row were not colored green. I can't thank you enough!!!
I have two lists of numbers in adjacent rows, and want to apply conditional formatting to the bottom row such that it colours the cell green if that number is within a bound of 100 either side of the relevant number in the top column, yellow if a further 100 above or below the previous bound, and red if any further out than that.
I want this conditional formatting to hold for all cells within these two rows, and format each cell in the bottom row depending solely on the cell directly above it.
I'm afraid I'm at a bit of a loss at this point, and without any support, I will have to input this manually for every cell in the range - any help would be much appreciated.
Hello!
If I understand your task correctly, the following formula conditional formatting should work for you:
=ABS(A1-A2) =< 100
the formula for the second condition is
=ABS(A1-A2) =< 200
the formula for the third condition is
=ABS(A1-A2) > 200
Create conditional formatting with these rules for cell A2. Then copy the format to other cells.
Thank you for all your wonderful blogs! They have helped me a lot! Thanks!
Hai,
Please give me your suggestion in excel on below mentioned condition
Source cell value is 35 (A1)
I will enter 0 to 25 (number) in cell A2 then this cell color will change red automatically and entered 26 to 35 then change green color automatically based on A1 cell value. It is possible.
Hello Ramkrish!
I recommend studying this article on conditional formatting on our blog.
I have searched extensively and cannot find the answer to my HOW-To COnditional Formatting question/need.
I have two sheets in the spreADSHEET, each contains the identical BUDGET spreadsheet. (12 MOnths across top and 30 budget categories along the left side.)
They are identical now when I create the budget, but one will be used to record aCTUAL expenses as the year progresses. I want to use COnditional Formatting on each cell, to highlight when that cells amount exceeds (>) then Budget Sheet's value for that same cell.
I use formula =D15>(BUDGET!$D15*1.05) and then highlight red.
When I use Format Painter across a row or column, EXCEL fixes that original D15 reference.
I want the test cell reference to change relatively too.
Does anyone follow this and have a solution for me?
How do you round the sum of a column of numbers in the same box that you used the relative formula for? I keep getting errors.
Hi! Attempting to apply a 3-color scale to a column of values where the formatting only applies to the previous n cells. New data is added to the column daily and I want the formatting to follow where it only works on the previous n cells. I think of it like a "running average" format. The problem seems to lie with the fact that Excel will only allow absolute references when determining to which cells the rule applies. If they were relative references, it would be perfect.
It was really helpful.
Thank you Svetlan Cheusheva
Hello Svetlana,
WOULD YOU TELL ME ABOUT THE SOLUTION OF FOLLOWING PROBLEM:
COLUMN NO D G W X
ROW NO. 33 33 33 33
REF. CELL NO. D33 G33 W33 X33
HOW I'LL USE THESE CELL NO. ie. D33, G33, W33, X33 IN THE FORMULA WHICH VALUE WILL BE DIFFERENT ON BASIS OF ROW NO. 33 (IT MAY BE OTHER LIKE 42, 12 ETC)
THANKS FOR YOUR PATIENCE.
I am trying to use conditional formatting to change the color of the text in a cell if the date is less than the date in one cell or greater than the date in another cell.
Cell E3 is 24Oct18
Cell F3 is 27Oct18
I would like Cell G3 to turn red when 23Oct18 (for example) is entered.
I have entered two conditional formatting formulas
=$G$3>$F$4 and =$G$3$F3) but then the date in G3 does not change color anymore.
Do I have to retype the formula for each cell each time? I will have at least 40 columns of dates to sort this way and multiple rows that I would like the formatting to apply to for each cell.
I want to have one cell change to yes or no based on 2 sources.
source one is column H4 (data is "YES" or "NO"), Selected as a manual input
Source two is a date in I4 based on being greater than today, =IF(I4>TODAY(),"Yes","No")
( this is generated from another formula)
I have tried multiple IF's or AND, I can get the right formula
If H4 = NO and I4 = Yes (greater than today) format N4 as Yes
If H4 = YES and I4 = No (less than today) format N4 as No
If H4 = YES C and I4 = Yes (greater than today) format N4 as Yes
thanks
I want relative references for each cell in a block. I set for 1 cell & copied to the rest. However, it's treating the relative reference as tho it applies to the whole range, rather than each cell individually, so the value used is only the cell relative to the first cell. How do I get excel to treat each cell individually?
i want a formula to compare two cell in different column.
column A is variable and column B is constant,when value in column A is +/- 1 of a value in column B then cell A should change its color.
hi,
In my worksheet,i have different conditional formatting in the rows, of the same column. But when i sort, it all mixing up. i hope u understand. Please help me to resolve this.
Excel 2016. My relative formatting won't stick in the conditional formatting rules manager box. When I edit any of it to relative, then hit either Apply or Close, it goes right back to full absolute, adding the $ back in wherever I deleted them. Then, when I copy down or paste formatting, it doesn't work right.
I have 4 numbers across, and want red font on the smallest number.
A B C D
85 83 75 60
70 85 90 82
So in each case, the lowest of the numbers should be red. I can do them individually, but I need to be able to copy the conditional formatting to all rows.
We all know that using a cell reference we can get data from a cell being referred.
Is it possible to get the formatting as well, using a cell refernce.
Hello, Chin,
unfortunately, no cell reference will copy the formatting.
Feel free to read our great tutorial about other easy ways to copy formatting in Excel.
THANK YOU THANK YOU THANK YOU - conditional formatting for a column has just taken me far too long to resolve!!
my spreadsheet contains a column of dates (column C) that are conditionally formatted. Some of them - if over 3 yrs turn yellow, others - if over 5 yrs old turn red (mutually exclusive). I can get that part to work however if I sort or filter the table (on column A or B)for other reasons, the conditional formatting will NOT follow the cell. I have attempted absolute/relative reference adjustments but the formula always reverts back to original setting! HELP
how to write a format for:"if the name in column 2 same with name in column 4, they will become blue colour background"??