Home

Copying Conditional formatting with relative cell referencces in the formula desn't work

%3CLINGO-SUB%20id%3D%22lingo-sub-94262%22%20slang%3D%22en-US%22%3ECopying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-94262%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20cell%20H19%20conditionally%20formatted%20%22Format%20values%20where%20this%20formula%20is%20true%22%20with%3A%20%3D%22h19%26gt%3B%24C%2419%22%20and%20then%20the%20colour%20green%3C%2FP%3E%3CP%3EWhen%20I%20copying%20the%20cell%20with%20Format%20Painter%20to%20cells%20H20%20to%20H90%2C%20the%20formula%20doesn't%20change%20the%20relative%20reference%20to%20h20%2C%20h21..%20h90%20etc..%20It%20stays%20as%20h19.%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-94262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-265862%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-265862%22%20slang%3D%22en-US%22%3EI'm%20copying%20this%20type%20of%20formula%20to%20other%20cells%2C%20but%20still%20Conditional%20formatting%20is%20not%20referring%20to%20Relative%20reference%20.%20Can%20anyone%20help%20me%20to%20copy%20this%20type%20of%20color%20format%20to%20any%20number%20of%20cells%20by%20using%20format%20painter%20or%20any%20other.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224369%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224369%22%20slang%3D%22en-US%22%3E%3CP%3EI%20got%20it%20to%20work.%20I%20wasn't%20as%20clear%20as%20I%20thought%20I%20was%20on%20relative%20vs%20absolute.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224367%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224367%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rob%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EQuote%20or%20not%20quote%20matter%20-%20in%20first%20case%20you%20compare%20two%20strings%20which%20are%20always%20the%20same%2C%20thus%20you%20always%20have%20same%20TRUE%20or%20same%20FALSE%2C%20depends%20on%20which%20texts%20to%20compare.%20In%20second%20case%20you%20compare%20values%20of%20the%20cells%2C%20result%20depends%20on%20how%20you%20use%20absolute%20and%20relative%20references.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20rule%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20532px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F38987i2F0F5F17920A9218%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20the%20rule%20not%20to%20entire%20range%20but%20for%20the%20first%20cell%20in%20it%20and%20after%20that%20copy%20formatting%20by%20format%20painter%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20477px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F38988iFD4CF192EC3E5490%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20you%20generate%20as%20many%20similar%20rules%20as%20many%20cells%20in%20your%20range.%3C%2FP%3E%0A%3CP%3ESample%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224356%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224356%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20having%20the%20same%20problem%2C%20and%20not%20having%20quotes%20around%20my%20references%20doesn't%20make%20any%20difference.%20As%20near%20as%20I%20can%20tell%2C%20it's%20impossible%20to%20copy%20conditional%20formatting%20from%20one%20cell%20to%20another%20%E2%80%94%20by%20copy-paste%2C%20or%20paste%20special%2C%20or%20format%20painter%20%E2%80%94%20and%20have%20the%26nbsp%3Bpasted%20conditional%20formatting%20references%20the%20relative%20cells%20rather%20than%20the%20original%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-94279%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-94279%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Roger%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20shall%20be%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3Dh19%26gt%3B%24C%2419%20%20%20%20%20%20%20(not%20%3D%22h19%26gt%3B%24C%2419%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742716%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742716%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20this%20is%20an%20old%20thread%2C%20but%20for%20anyone%20reading%20-%20I%20found%20that%20I%20was%20unable%20to%20copy%20the%20formatting%20to%20more%20than%20one%20cell%20at%20a%20time.%20The%20Format%20Painter%20will%20work%20copying%20the%20conditional%20formatting%20with%20relative%20cell%20references%20from%2C%20for%20example%2C%20cell%20T3%20to%20cell%20T4%2C%20however%2C%20if%20I%20drag%20the%20Format%20Painter%20down%20cells%20T4%20through%20T14%2C%20it%20will%20only%20use%20relative%20cell%20reference%20for%20T4%20but%20not%20the%20other%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20is%20a%20feature%20that%20could%20use%20some%20work%2C%20if%20anyone%20else%20knows%20a%20way%20around%20this%20please%20let%20me%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744426%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372769%22%20target%3D%22_blank%22%3E%40abigail_nottingham%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20a%20normal%20behaviour.%20Dragging%20Format%20Painter%20from%20T4%20to%20T14%20you%20apply%20conditional%20formatting%20to%20entire%20range%20T4%3AT14.%20Excel%20take%20formula%20for%20the%20first%20cell%20in%20the%20range%20as%20the%20basis%20and%20apply%20it%20internally%20to%20each%20cell%20in%20the%20range.%20Exactly%20the%20same%20will%20be%20if%20you%20first%20select%20T4%3AT14%20range%2C%20add%20rule%20formula%20for%20the%20first%20cell%2C%20e.g.%20T4%26gt%3BA4%20and%20apply%20to%20entire%20range%20-%20formula%20itself%20isn't%20changed%20if%20you%20check%20CF%20for%20any%20other%20cell%20within%20the%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756635%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20it%20may%20be%20normal%20behavior%2C%20but%20it%20seems%20that%20if%20the%20feature%20allowed%20for%20relative%20references%20in%20the%20range%20field%20that%20you%20would%20be%20able%20to%20paint%20a%20much%20larger%20range%20at%20a%20time%20and%20preserve%20the%20proper%20reference.%26nbsp%3B%20Without%20that%2C%20I'm%20having%20to%20paint%20one%20row%20at%20a%20time%2C%20500%20times.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766389%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376990%22%20target%3D%22_blank%22%3E%40Jim_R%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Jim%2C%3C%2FP%3E%3CP%3EMaybe%20you%20already%20solved%20what%20you%20were%20looking%20for.%20If%20not%2C%20please%20visit%20this%20website.%3CBR%20%2F%3EWith%20just%20a%20few%20clicks%20it%20is%20possible%20to%20set%20the%20conditional%20format%20for%20thousands%20of%20lines.%20The%20first%20example%20in%20this%20website%20is%20%22crazy%22%20at%20first%20glance%20but%20it%20works%20smoothly.%20It%20solved%20my%20problem!%20and%20there%2C%20the%20author%20offers%20several%20good%20examples%20of%20conditional%20formatting.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fconditional-formatting-with-formulas%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fconditional-formatting-with-formulas%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766915%22%20slang%3D%22en-US%22%3ERe%3A%20Copying%20Conditional%20formatting%20with%20relative%20cell%20referencces%20in%20the%20formula%20desn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379709%22%20target%3D%22_blank%22%3E%40mrkuramoto%3C%2FA%3Ethanks%20for%20the%20link%2C%20I'll%20give%20it%20a%20try%20later%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Roger Dance
Occasional Visitor

I have a cell H19 conditionally formatted "Format values where this formula is true" with: ="h19>$C$19" and then the colour green

When I copying the cell with Format Painter to cells H20 to H90, the formula doesn't change the relative reference to h20, h21.. h90 etc.. It stays as h19.

What am I doing wrong?

 

10 Replies

Hi Roger,

 

It shall be 

=h19>$C$19       (not ="h19>$C$19")

I'm having the same problem, and not having quotes around my references doesn't make any difference. As near as I can tell, it's impossible to copy conditional formatting from one cell to another — by copy-paste, or paste special, or format painter — and have the pasted conditional formatting references the relative cells rather than the original cells.

Hi Rob,

 

Quote or not quote matter - in first case you compare two strings which are always the same, thus you always have same TRUE or same FALSE, depends on which texts to compare. In second case you compare values of the cells, result depends on how you use absolute and relative references.

 

You may use rule like this

image.png

 

You may use the rule not to entire range but for the first cell in it and after that copy formatting by format painter

image.png

In this case you generate as many similar rules as many cells in your range.

Sample attached.

I got it to work. I wasn't as clear as I thought I was on relative vs absolute. Thanks.

I'm copying this type of formula to other cells, but still Conditional formatting is not referring to Relative reference . Can anyone help me to copy this type of color format to any number of cells by using format painter or any other.

I know this is an old thread, but for anyone reading - I found that I was unable to copy the formatting to more than one cell at a time. The Format Painter will work copying the conditional formatting with relative cell references from, for example, cell T3 to cell T4, however, if I drag the Format Painter down cells T4 through T14, it will only use relative cell reference for T4 but not the other cells.

 

I think this is a feature that could use some work, if anyone else knows a way around this please let me know.

@abigail_nottingham 

 

That's a normal behaviour. Dragging Format Painter from T4 to T14 you apply conditional formatting to entire range T4:T14. Excel take formula for the first cell in the range as the basis and apply it internally to each cell in the range. Exactly the same will be if you first select T4:T14 range, add rule formula for the first cell, e.g. T4>A4 and apply to entire range - formula itself isn't changed if you check CF for any other cell within the range.

@Sergei Baklan, it may be normal behavior, but it seems that if the feature allowed for relative references in the range field that you would be able to paint a much larger range at a time and preserve the proper reference.  Without that, I'm having to paint one row at a time, 500 times.

@Jim_R 

 

Hello Jim,

Maybe you already solved what you were looking for. If not, please visit this website.
With just a few clicks it is possible to set the conditional format for thousands of lines. The first example in this website is "crazy" at first glance but it works smoothly. It solved my problem! and there, the author offers several good examples of conditional formatting. 

https://exceljet.net/conditional-formatting-with-formulas

@mrkuramotothanks for the link, I'll give it a try later :)

Related Conversations