Home

Conditional Formatting Relative References Not Working

%3CLINGO-SUB%20id%3D%22lingo-sub-457104%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457104%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20currently%20working%20on%20writing%20a%20conditional%20formatting%20rule%20that%20will%20highlight%20a%20cell%20if%20the%20value%20of%20a%20cell%20in%20column%20B%20(B1)%20is%20within%205%25%20of%20another%20cell%20(A1)%20in%20column%20A.%26nbsp%3B%20I've%20been%20able%20to%20figure%20out%20this%20formula%3A%26nbsp%3B%3DOR(%24A1*1.05%26lt%3B%24B1%2C%20%24A1*0.95%26gt%3B%24B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20conditioning%20formatting%20rule%20works%20perfectly%20when%20I%20compare%20A1%20to%20B1%20but%20when%20I%20try%20to%20copy%20the%20formula%20to%20subsequent%20cells%20to%20compare%20A2%20to%20B2%2C%20A3%20to%20B3%2C%20the%20formula%20does%20not%20work%20as%20it%20continues%20to%20reference%20the%20A1%20cell%20to%20compare%20itself%20to%20cells%20in%20the%20B%20column.%26nbsp%3B%20Could%20anyone%20help%20me%20figure%20out%20what%20I%20am%20doing%20incorrectly%20to%20get%20this%20formatting%20rule%20to%20work%3F%20I%20do%20not%20want%20to%20have%20to%20recreate%20this%20formula%20for%20each%20cell%20in%20order%20to%20get%20the%20conditional%20formatting%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-457104%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457200%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457200%22%20slang%3D%22en-US%22%3ERemove%20the%20%24%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457497%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321593%22%20target%3D%22_blank%22%3E%40Laura_B%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20formula%20shows%20if%20A%20is%20on%20more%20than%205%25%20more%20or%20less%20than%20B%2C%20and%20you%20have%20correct%20highlighting%20for%20that.%20Perhaps%20you%20need%20another%20formula%2C%20like%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108981iC0BF9139E280F163%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-457498%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Relative%20References%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-457498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321591%22%20target%3D%22_blank%22%3E%40stv1vy%3C%2FA%3E%20%2C%20if%20use%20relative%20reference%20when%20values%20in%20B%20will%20be%20compared%20with%20ones%20in%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Laura_B
Occasional Visitor

Hi - I am currently working on writing a conditional formatting rule that will highlight a cell if the value of a cell in column B (B1) is within 5% of another cell (A1) in column A.  I've been able to figure out this formula: =OR($A1*1.05<$B1, $A1*0.95>$B1)

 

The above conditioning formatting rule works perfectly when I compare A1 to B1 but when I try to copy the formula to subsequent cells to compare A2 to B2, A3 to B3, the formula does not work as it continues to reference the A1 cell to compare itself to cells in the B column.  Could anyone help me figure out what I am doing incorrectly to get this formatting rule to work? I do not want to have to recreate this formula for each cell in order to get the conditional formatting to work.

 

Thanks!

3 Replies

@Laura_B ,

 

Your formula shows if A is on more than 5% more or less than B, and you have correct highlighting for that. Perhaps you need another formula, like here

image.png

 

@stv1vy , if use relative reference when values in B will be compared with ones in C

Related Conversations
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies