# Relative and absolute cell references in Excel conditional formatting rules

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 conditional formatting rules, cell references are relative to the top-left most cell in the applied range. So, when making a new rule, you can simply pretend as if you are writing a formula for the top-left cell only, and Excel will "copy" your formula to all other cells in the selected range.

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.

## Useful resources

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:

Category: Excel Tips

### 77 responses to "Relative and absolute cell references in Excel conditional formatting rules"

1. Joseph says:

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.

2. Britta says:

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

3. Asher says:

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!

• steve says:

Did you ever find a solution to this problem?

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.

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

• Mujgan says:

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

5. Jennifer says:

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?

6. MV says:

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.

• 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).

• MV says:

Thanks will give that a try.

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

• hemant rohilla says:

thank you so-much you are great.

7. Charles says:

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*

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 ?

9. Ahmed Nasser says:

Hi Svetlana Cheusheva,

How we can apply condition formatting to the consolidate cell

10. Karen says:

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.

11. ngha says:

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

12. ngha says:

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

13. Anonymous says:

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,

14. katie says:

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

15. Dave In Oly says:

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?

16. Eugene Herron says:

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.

17. ANA ELISA says:

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 !

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

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

20. Harout says:

Hi Svetlana,
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.

21. Robert says:

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?

• Alexander says:

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.

22. Lois says:

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!!!!!!

23. chris says:

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

24. Rajendra Gupta says:

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.

25. johnpaul says:

hi
what is the formula in this problem
prelimid100term100semifinal100 final90 if 100 is plot 1 in 1 column

26. Pavan says:

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

• 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.

27. Pavan says:

Thank you Svetlana, its working.

28. Chris S. says:

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.

29. Nigel Lofting says:

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.

30. Jerry says:

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?
Best regards.

31. Dalibor says:

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

32. Dalibor says:

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

33. Tayyab says:

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

34. SOL says:

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.

35. Spence says:

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?

36. Rico says:

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?

37. Symon says:

HI

I get all the conditional, absolute and mixed formatting formulae using \$. However, whatever I do, excel always puts it all back to absolute!

38. CharlieB says:

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

39. josh says:

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?

• Rob says:

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.

40. Robert Griffin says:

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

41. Johnny.T says:

how to write a format for:"if the name in column 2 same with name in column 4, they will become blue colour background"??

42. Glenn says:

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

43. Anonymous says:

THANK YOU THANK YOU THANK YOU - conditional formatting for a column has just taken me far too long to resolve!!

44. Chin says:

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.

45. Art says:

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.

46. ugeesh says:

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.

47. sagar says:

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.

48. TomTrottier says:

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?

49. Dale says:

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

50. Andrea says:

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.

51. Md. Shahidul Islam says:

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)

52. Bilal Mustafa Siddiqui says:

Thank you Svetlan Cheusheva

53. Scott says:

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.

54. Haley Brown says:

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.

55. Jim Sutton says:

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?

56. Ramkrish says:

Hai,
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.

57. Hugo de Groot says:

Thank you for all your wonderful blogs! They have helped me a lot! Thanks!

58. Eoin Funnell says:

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.

59. Reveuse says:

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!!!

60. Syd99 says:

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),"")