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
Hi,
I have a spreadsheet with two columns D and I. Column I has values ($1 to $26) filled in from rows 5 to 30 i.e (I5 - I30). Now in column D the values ($1 to $26) will be filled in on a bi-weekly basis (every 2 weeks).Its for a challenge were all the values from $1 to $26 are cut out in small pieces of paper and placed in a lucky dip container with one picked on a bi-weekly basis until the container empties.
I want to create a conditional format to strikethrough the values in column I every time column D is populated with values duplicated to that in column I after each dip.
Hope that makes sense to you.
Thanks,
Rob
I have the formula =a1 * b1 in cell c1 where a1 = 2 and b1 = 10, and the resulting value is 24? why is it not 20?
It could be that the cell a1 has the actual decimal value 2.4, cell formatted to display rounded values which in this case is 2. So the formula applies to the actual decimal value a1=2.4 and not the round value displayed.
Hi,
I am having a problem with comparing two cells using conditional format and if there is a variance of >0.5 the cells turn orange.
I have T4 comparing T5, U4 comparing U5 etc and I thought I had it working but it as I go down the spreadsheet for other rows to check the formula does not work
I Used the following
1) =ABS(T4-T3)>0.5 applied to T3:AG4
2) =ABS(T4-T3)>0.5 applied to T4:AG4
Any clues, appreciated
Thanks
HI
I get all the conditional, absolute and mixed formatting formulae using $. However, whatever I do, excel always puts it all back to absolute!
I have the same problem.
Did you ever find a solution??
My email for a reply if you ever get this
I'd like to copy and past (or Insert) a line with conditional formattings on certain columns. E.g. "duplicate values", to be sure that new data edited on this new line has unique data in.
BUT: paste does only work, if each cell is copied and paste individually. If you paste (or insert) a full line, the ranges in the conditional formats will be splitted. E.g. a range $N:$M will be $N$1:$N$215;$M217:$N65532 on the old (conditional formatted) cells and the new cell has $M$216:$N$216 in.
This is not acceptable.
Is there a solution, where the conditional format range is still $N:$M?
I have the impression, that with old Excel versions, this was working. Perhaps there is an option for copy and paste?
I want a cell to turn yellow if the date is approaching. When I try to duplicate my conditional format the cell referenced in my formula will not go to the next row.
i.e.
Formula =$D6<Today()
I will try to use the same conditional formatting for cell D7 but because the formula says D6 it will not intuitively add a row. It continues to reference D6.
How can I make my conditional formatting useful on all rows and not have to make 1,000 rules for each row?
I don't have time to closely peruse the whole article but you don't seem to have mentioned that by clicking in the cell reference in the formula bar and pressing the F4 function button the formula will rotate between the 4 options from completely relative to completely absolute.
Friends please help me..
I have the records of 100,000 in column A in excel..i want to split this into many columns like col A 1-1000, col B 1001-2000, col C 2001-300 like that upto 100,000..please tell me as soon as possible
column A Col B col C Col D
1 1-1000 1001 -2000 2001-3000
2
3
100,000
I was solve the problem. The problem was in difference in 12 decimal, now I use round(x;2) and solve problem and everything is work perfect
Dear,
I work wit excel 2010 and I have workbook with several worksheets, and I need to compare two cell from two different worksheets. I was try to do it with conditional formating with use a formula to determine which cell to format and when I put the rule, it was not work. These two cells in two different sheets are result of several addition and substraction and I would like to highlight my cell k20 if value is different from Prih_knjiga!$F$740. My rule was had the following syntax:
=$K$20=Prih_knjiga!$F$740. Can you help, and can you tell me where I was have mistake? I can send you worksheet so you can try if you want. On the other test workbook i was try with CF and everything work perfectly.
Please for help, king regards from Croatia
Hello,
please, I need to find out right rule of a conditional formating for more than one cell value in a whole table columns.
It is possible to write a formula like this:
=AND(50, 30, 20, ... )? - for more values e.g.
Or more sophisticated. How?
Thank you for your help.
Best regards.
Hello Svetlana
I am producing an excel table that has details of people with expiry dates of their qualifications in four columns. I have managed to conditionally format the cells containing the dates to change colour with less then 6 months to run to expiry and when expired ["cell less than"=NOW()]. I have the surnames of the people in column A. What I would like to do is change the colour of the cell containing their surname if they are expired (depends on data in 4 different columns) and also for the colour change to move correctly with the rows should I sort one of the columns other than surname.
I am trying to compare two cells with text info in two columns. Any that don't match I would like to be highlghted
The original value is in column C, the comparison value is in column F.
The formula I tried is =$C2$F2
this seems to apply the conditioning randomly, whether the cells match or not.
Have also tried Cell Value: $C2 (almost all cells get formatted, whether they match or not except blank cells)
Not sure what I'm doing wrong here.
Thank you Svetlana, its working.
Hi, I have query that
In a excel cell (say A1), i am having values like
1
2
and I am giving that same cell as reference in another cell (Say B1) but I am getting the value as "12" instead of
1
2
Thank you in advance.
Hi Pavan,
The point is that Excel formulas deal with cell values, not cell formatting. To fix the issue, you can select the cell with your formula, and click the 'Wrap Text' button on the Home tab, in the Alignment group.
hi
what is the formula in this problem
prelimid100term100semifinal100 final90 if 100 is plot 1 in 1 column
Hi Svetlana,
If a column contains a list of numbers such as:
5
8
8
4
6
6
6
7
How can set a conditional format such that the first occurrence appears as bold and the rest as normal. In the above list, the first occurrence of 5, 8, 4, 6, and 7 should appear as bold, while the rest can appear as normal.
Hi Rajendra,
You can create a conditional formatting rule based on the following formula, where A1 is the top-most cell with data:
=COUNTIF($A$1:$A1,$A1)=1
For the detailed steps to create the rule, please see the following tutorial: Excel formulas for conditional formatting.
I have a spreadsheet where I am running a date in column B and I have set it up so that if an item isn't imputed into column H within two days, it turns Red. I have a formula =(TODAY()-B3)>2 and I wondered how I could copy it down each row below and have the cell reference change for each ie. B4, B5, B6, etc
Hello,
I have conditional formatting relying on a date where the cell is merged because I had to add another row in order to have the sub contractors listed separately. For example, I have rows 2 and 3 for a task and columns A, B, C, D, E, F, G, H, I, J, K, L, M, N, O and P are all merged however once you get to column Q, R, S, T, U they are no longer merged to show the distinction between the 2 different sub contractors on the task. Column V is merged again. My problem is Column I which is the expiration date of the task is the condition to turn all the cells to the color requested. Because column I is merged the first subcontractor in Row 2, Column Q, R, S, T, U will turn the color but the second sub contractor in Row 3, Column Q, R, S, T and U will not turn the color requested. It will only happen if I don't merge the Column I which has the expiration date and I put the date in both cells (2 and 3). PLEASE HELP IVE BEEN WORKING ON THIS FOR 3 DAYS!!!!!!
I get so frustrated with using conditional formatting to highlight cells. The way I work with spreadsheet data often involves cutting and pasting data from one cell to another and the conditional formatting quickly becomes garbled. If I have the formatting set to an entire column (ex: A:A), why on earth would it assume that if I cut and paste something from one cell to another within the column that I'd want the origin (now empty) cell to be unformatted (ex: cutting and pasting the contents of A6 to A7 leaves the conditional formatting with a new range of A1:A5,A7:1048576). Is there any way around this that I'm not realizing?
Hello Robert,
The only working way that I am aware of is to convert a range into an Excel table (Ctrl+T), and then use the columns' names rather than their addresses in the conditional formatting rules.