Relative and absolute cell references in Excel conditional formatting

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 $.
Absolute column and relative row cell references in conditional formatting rules

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
Relative column and absolute row cell references in Excel conditional formatting

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.

Absolute column and absolute row cell references in Excel conditional formatting

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:
Relative column and relative row in Excel conditional formatting rules

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.

Useful resources

Table of contents

97 comments

  1. Hi Svetlana,
    I need help please
    I have a basic list of codes with their details (price-quantity bla bla bla) like:
    FHQ110 400 300 15
    FHQ220 500 100 20
    FHQ221 140 60 4
    Now when I make invoice (in other page of same worksheet) I will write the code manually, I need of Excel to find same code from the basic list and bring the details of it to write them beside the code in the invoice
    automatically.
    Thanks in Advance dear

  2. opy formula then paste same cell no not change cell no

    exp. =a5*c6+2
    paste =a5*c7+2 but paste =A6*C7+2

    how paste =a5*c7+2

  3. copy formula then paste same cell no not change cell no

    exp. =a5*c6+2
    paste =a5*c7+2 but paste =A6*C7+2

    how paste =a5*c7+2

  4. I´ve read lots of info about this in different websites and yours was the only one that helped me solve my problem !
    Thank you !

  5. Hi Svetlana,

    I have tables in a worksheet with multiple conditional formats (Yellow fill, bold type, strike through font and double underline). These conditional formats use both relative and fixed position references in the four formulas. Some cells will display no formating, others display 1, 2, 3 or even all four formats, based on the formulas. This part I have working correctly. What I want to do is count the total number of cells that have the each of the four formats separately. So a cell that is both yellow filled and bold will be counted for each conditional formatting rule. It would return a number in a separate cell for each conditional format counted. I found several VBA formulas, but none work so far.

  6. Svetlana,

    I am not sure if you have seen this issue yet.

    I have an Excel 2010 worksheet that sets the color of each cell based on the text value contained in another.

    Surplus is Green
    =COUNTIFS('New Entry'!$A$11:$A$174,J2:J19,'New Entry'!$B$11:$B$174,"Surplus")

    Turn In is Yellow
    =COUNTIFS('New Entry'!$A$11:$A$174,J2:J19,'New Entry'!$B$11:$B$174,"Turn In")

    Checked Out is Brown
    =COUNTIFS('New Entry'!$A$11:$A$174,J2:J19,'New Entry'!$B$11:$B$174,"Checked Out")

    Somehow the cell reference to $A$174 and $B$174 in my formulas are being automatically degraded to a lesser cell # For example, today my conditional formatting formula read:

    =COUNTIFS('New Entry'!$A$11:$A$144,J2:J19,'New Entry'!$B$11:$B$144,"Checked Out")

    Why is Excel changing my conditional formatting formulas?

  7. hi, I would like to conditional format a column so that cells will red if they contain values greater than the cell directly above them.

    Thanks

    • Hi Katie,

      Assuming that row 2 is your first row with data, you can create a conditional formatting rule with a formula similar to this:
      =$A3>$A2

  8. HI,

    Because there is duplicate Names in Column "Name" and I want to use SUMIF formula for each cell in col "mon" to "Fri"
    where
    Range is from A2 to A6,
    criteria is "name in each row of Name column",
    sum range is each column "Mon" and then "Tue" ..."Fri"
    How do I do the formula with SUMIF in conditional format to set the color of each cells in Col "Mon" thru "Fri"?
    Thanks,

  9. Look Like it was missing some information:
    If total <= 4, set color to orange
    If total < 8, set color to yellow
    If total = 8, set color to blue
    If total is greater than 8, set color to green

    • Hi!

      Select the rows you want to color, not including the header row and create the following rules:

      Orange =SUM($C2:$G2)<=4

      Yellow =AND(SUM($C2:$G2)>4, SUM($C2:$G2)<8)

      Blue =SUM($C2:$G2)=8

      Green =SUM($C2:$G2)>8

  10. Hi,
    I have a question:
    If I have a table as follows:

    ROW1: Name Tasks Mon Tue Wed Thu Fri
    Row2: A clean 4.5 3.5 8 6 9
    Row3 B Clerk 9 4 5.5 7 0
    Row4: C Sale 5.5 7 6 8 1
    Row5: A Sale 3.5 0.5 0 1 0
    Row6: B Clean 0 0 0.5 0 0

    How do I use conditional format to set the color to the cells in each column Mon to Fri with the condition as follows?

    if total hours of each person in a day <= 4 set cell color to orange to each day's column
    If total hours of each person in a day 8 set color to green
    In this example, The person A has 8 hours in Mon and Row2 and Row5 of the COl "Mon" will be set to "Blue". The person B has total 9 hours in Mon, therefore, row2 and row6 set to Green. The person C has total 5.5, row4 should set to yellow. The condition format should be used for all rows and columns from Mon to Fri

  11. Just so you know... the scrolling icons at the bottom of the page looks really swish but I find it so distracting that I can't read the page.

  12. Hi Svetlana Cheusheva,

    How we can apply condition formatting to the consolidate cell

  13. hi Svetlana Cheusheva!
    I need your expert help and also of other members as well.
    I am unable to solve a problem regarding below situation;
    I wanna know how to arrange multiple columns with huge data in such a way that first column's value would set in front of the next column if the values can find equal match in front of each other in a row otherwise the value would remain blank if it doesn't find its required same data .
    suppose i have numeric data and also other,if A:100 has a value 42001234 and column B has 42001234 in B386 then it could be arranged via sorting or other method but can we place or arrange them via technique or formula ?

  14. Please answer this question (I have to learn VBA, but for now walk me through baby steps without coding).

    I have a list in Column A with about 2500 different names. 1k are highlighted green, 500 red, 500 orange, the remaining blank.

    I recieve a different list with some names that match my original column A list. I want to see if the name in the cell in column b, matches with column A, and if it is highlighted(I highlighted manually as I progressed, not using any conditional) I want the new column, column C to show that name but now highlighted.

    Currently I use a VLOOKUP(B2,$A:$A,1,FALSE) and that returns the name of column B if it is in A however no color value is shown. I need to know which are not highlighted so I know which ones do not match or need to be "colored in"

    Can anyone help with this?

    E.G.

    Currently:

    A B C
    BYD HILIT G* BYD*Plain* BYD *Plain*
    GYD HILIT R* LUH *Plain LUH *Plain
    HIJ *BLANK* DOL *Plain* #N/A
    LUH HILIT G* GYD *Plain GYD *Plain*

    WANT:

    A B C
    BYD HILIT G* BYD*Plain* BYD HILIT G*
    GYD HILIT R* LUH *Plain LUH HILIT G*
    HIJ *BLANK* DOL *Plain* #N/A
    LUH HILIT G* GYD *Plain GYD HILIT R*

  15. Hi,

    My question is how to do a conditional formatting where I have to select multiple cells in a row and if those conditions are not met then the cells below either row or column need to be changed in color code.

    I understand excel does not allow multiple absolute referencing. Is there any formula through conditional formatting for this.

    Ex: The cells I selected are say E5 to I5 ( E5, F5, G5, H5, I5). These cells have the letter H to L.

    Now I want to conditional format to a color red when the cells below ($E$7:$I$20) are blank and say orange if the value is either more or less than the referenced cell, green if it matches

    I want the formula for this or an alternate method only through conditional formatting. The excel I am working is quite monstrous.

    Thanks in advance.

    • Hi!

      Since you need to lock a row, you should use a relative column (without $) and absolute row (with $) references. Try creating the rules with the following formula:

      Red (it should come 1st in the list of rules with the "Stop if true" option checked): =E$7=""

      Green: =E$7=E$5

      Orange: =E$7<>E$5

      You write the formula for the left-most cell but apply the rules to all cells you want to highlight (e.g. E5:I5).

      • Thanks will give that a try.

        Have loads of questions though will pose them as I come across.

        • thank you so-much you are great.

  16. I need to figure out how to get a conditional format of duplicate values in excel. So for example I have column A and column B. Each column has numbers in them, but I ONLY want duplicates that are in B to highlight in A. Right now if I do the basic conditional format of duplicates it shows all duplicates that are in A and B (A has duplicates within it's own and I don't want those highlighted)

    Example

    Column A
    32
    45
    65
    32
    76
    43
    45
    78

    Column B
    100
    98
    43
    54
    96
    37

    I only want 43 to be highlighted, and it can be in either column, I just don't want the duplicate ones within the same column highlighted. Is that possible?

  17. How to create a condition like this: I want to count the number of cells of a range (let's say B1:B20) whose values are 10% of another cell, let's say B32.

    • Hi Ahmad,

      You can use the following array formula (remember to press Ctrl+Shift+Enter to get it to work correctly):

      =SUM(--(B1:B20=B32*0.1)*(B1:B20<>0))

      Please note that the formula counts cells in B1:B20 that are exactly 10% of B32. If you want, say 10% or less, then change the condition to B1:B20<=B32*0.1

      • 1What is the code used to reference the cell in the eleventh column and fortieth row?

  18. Hi Svetlana, quick question for you if you have a moment.

    I have a worksheet with conditional formatting down an entire column, delineated simply as $K:$K. However, as part of my team's process, we are constantly inserting new rows into the top of the range, and it's causing my Conditional Formatting rules to duplicate, with one set continuing the $K:$K applies, but the duplicates to apply only to the inserted range.

    Is there a way I can prevent that, either at setup or with a process change to how we insert our rows?

    Thanks so much!

    • Did you ever find a solution to this problem?

  19. Dear Svetlana,
    I managed to do my conditional formatting on 4 columns next to each other in the same row but it only works with absolute row and column or with relative row and absolute column. If I have a relative column and absolute row it colors only 2 (first and last) of the 4 columns and if I change both to relative then it only colors the first column.
    Of course it would be handy to have both relative so that I can easily copy the cells. Also the copying works perfectly vertically (since the row is relative) but I want to make it work horizontally too.
    It would be perfect if you could help me :) Thanks

  20. Hai

    Pls give solution for the following question

    Pipe Bend
    200 15
    200 30
    250 15
    300 60
    400 45
    500 15

    How many bends in 200 category? I tried COUNTIFS(Pipe,200,Bend)

    Regards
    Joseph

    • Hello Joseph,

      Since you want to sum bends by categories, you need a function that sums based on a condition(s), i.e. the SUMIF function:
      =SUMIF(A2:A100,"200",B2:B100)

      Where A2:A100 is the Pipe column and B2:B100 – the Bend column.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)