Home

Conditional Formating

%3CLINGO-SUB%20id%3D%22lingo-sub-576803%22%20slang%3D%22en-US%22%3EConditional%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576803%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20know%20how%20to%20conditionally%20format%20cells%20in%20the%20attached%20excel%20sheet%20based%20off%20a%20%25%20variance%20of%20the%20%22Goal%20values%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20in%20column%20D%2C%20E%2C%20F%20are%20the%20prescribed%20goals%20and%20Cells%20in%20column%20J%2C%20K%2C%20L%20are%20the%20%22actual%22%20values%2C%20Based%20off%20the%20goal%20values%20how%20would%20I%20conditionally%20format%20values%20in%20column%20J%2C%20K%2C%20L%20to%20have%20a%20red%20filling%20if%20values%20had%20a%205%25%20or%20more%20discrepancy%20smaller%20or%20greater%2C%20and%20a%20green%20fill%20if%20within%20the%205%25%20discrepancy%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-576803%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-576844%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343469%22%20target%3D%22_blank%22%3E%40EJ_wholesupps%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20rule%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%3E%3DABS(J3%2FD3-1)%26gt%3B%3D0.05%3C%2FPRE%3E%0A%3CP%3Efor%20the%20red%2C%20and%3C%2FP%3E%0A%3CPRE%3E%3DABS(J3%2FD3-1)%26lt%3B0.05%3C%2FPRE%3E%0A%3CP%3Efor%20the%20green%2C%20apply%20to%20you%20range%20in%20J%3AK%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577152%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577152%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%26nbsp%3BThank%20you%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577153%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343469%22%20target%3D%22_blank%22%3E%40EJ_wholesupps%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
EJ_wholesupps
New Contributor

I would like to know how to conditionally format cells in the attached excel sheet based off a % variance of the "Goal values"

 

Cells in column D, E, F are the prescribed goals and Cells in column J, K, L are the "actual" values, Based off the goal values how would I conditionally format values in column J, K, L to have a red filling if values had a 5% or more discrepancy smaller or greater, and a green fill if within the 5% discrepancy?   

3 Replies

@EJ_wholesupps ,

You may use rule with the formula

=ABS(J3/D3-1)>=0.05

for the red, and

=ABS(J3/D3-1)<0.05

for the green, apply to you range in J:K

@Sergei Baklan Thank you so much!!

@EJ_wholesupps , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies