Home

Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-886760%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-886760%22%20slang%3D%22en-US%22%3E%3CP%3ESome%20of%20my%20cells%20on%20my%20spreadsheet%20need%20to%20be%20the%20same%20color%20as%20the%20previous%20cell%20but%20the%20cell%20I%20need%20to%20be%20the%20same%20color%20will%20always%20be%20changing%20value%20but%20it%20will%20always%20be%20the%20same%20color%20as%20the%20previous%20cell.%20For%20instance%2C%20any%20cells%20that%20contain%20the%20text%20%22Above%20Average%22%20are%20orange%20and%20the%20following%20cell%2C%20regardless%20of%20the%20value%20needs%20to%20be%20the%20same%20color%20as%20the%20%22Above%20Average%22%20cell%20because%20the%20two%20will%20always%20be%20the%20same%20value.%20If%20it%20says%20%22Average%22%20and%20is%20yellow%20then%20the%20following%20cell%20needs%20to%20be%20yellow%20also.%20How%20do%20I%20form%20and%20apply%20that%20rule%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-886760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENew%20Rule%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-886854%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-886854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418197%22%20target%3D%22_blank%22%3E%40jeriinfo4u%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20data%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%20472px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134972i61BAB98AC70E6D03%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%3Eyou%20may%20apply%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DOR(A2%3D%22Average%22%2COFFSET(A2%2C-1%2C0)%3D%22Average%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20your%20column%20starting%20from%20A2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887121%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887121%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%20for%20your%20response.%20So%20my%20data%20looks%20like%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3EOverall%20Quadrant%3C%2FTD%3E%3CTD%3EOverall%20Score%3C%2FTD%3E%3CTD%3EIncentive%20Quadrant%3C%2FTD%3E%3CTD%3EIncentive%20Score%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9.27.2019%3C%2FTD%3E%3CTD%3EAbove%20LCL%3C%2FTD%3E%3CTD%3E5.07%3C%2FTD%3E%3CTD%3EAbove%20Average%3C%2FTD%3E%3CTD%3E7.25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ESo%20Row%202%20Column%202%20and%20Column%203%20need%20to%20be%20the%20same%20color%20just%20as%20Row%202%20Column%204%20and%20Column%205%20need%20to%20be%20the%20same%20color.%20The%20values%20in%20Column%203%20and%20Column%205%20will%20always%20be%20the%20same%20color%20as%20Column%202%20and%20Column%204%20regardless%20of%20the%20number.%20So%20Above%20LCL%20is%20orange%20and%20so%20is%205.07%20and%20Above%20Average%20is%20yellow%20and%20so%20is%207.25.%20If%20it%20were%20to%20drop%20down%20to%20below%20average%20or%20any%20other%20description%20then%20the%20colors%20set%20for%20those%20will%20either%20be%20red%20or%20green%20and%20the%20following%20column's%20value%20would%20match%20the%20values%20set%20for%20below%20average%20and%20would%20also%20be%20the%20same%20color%20as%20the%20color%20for%20below%20average.%20I%20guess%20the%20simpler%20way%20to%20say%20this%20is%20the%20Overall%20Quadrant%20and%20Overall%20Score%20will%20always%20be%20the%20same%20color%20as%20will%20the%20Incentive%20Quadrant%20and%20the%20Incentive%20Score.%20Sorry%2C%20I%20didn't%20mean%20to%20make%20that%20confusing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887325%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418197%22%20target%3D%22_blank%22%3E%40jeriinfo4u%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20the%20starting%20point%20-%20why%20your%20cell%2C%20let%20say%26nbsp%3B%3CSPAN%3E%3CSTRONG%3EAbove%20LCL%3C%2FSTRONG%3E%20as%20in%20example%2C%20is%20orange%20or%20green%20or%20red%3F%20Do%20you%20apply%20conditional%20formatting%20to%20it%20use%20some%20formula%20or%20color%20manually%3F%20In%20first%20case%20you%20may%20expand%20the%20logic%20on%20other%20cells%2C%20if%20the%20latest%20then%20only%20with%20VBA%20programming.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888564%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888564%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%3BSo%20this%20spreadsheet%20is%20the%20daily%20scorecard%20for%20all%20agents%20at%20my%20job.%20We%20have%20been%20provided%20a%20Mac%20for%20employment%20purposes%20only%20and%20are%20not%20allowed%20to%20email%20anything%20from%20our%20work%20computer%20to%20our%20personal%20email%20or%20anything%20like%20that.%20So%20I%20have%20pulled%20my%20row%20out%20of%20the%20spreadsheet%20and%20am%20manually%20entering%20the%20data%20daily%20based%20on%20the%20scorecard%20and%20the%20color%20key%20is%20as%20follows%3A%3C%2FP%3E%3CP%3ERed%20is%20Below%20LCL%2C%20orange%20is%20Above%20LCL%2C%20yellow%20is%20Above%20Average%2C%20and%20green%20is%20Above%20LCL.%20The%20first%20four%20columns%20of%20colored%20cells%20are%20labeled%20as%20follows%3A%20Overall%20Quadrant%20and%20Overall%20Score%20which%20will%20always%20be%20the%20same%20color%20based%20on%20what%20the%20scores%20are.%20If%20the%20scores%20are%20Below%20LCL%20then%20both%20cells%20for%20that%20day%20in%20those%20two%20columns%20will%20be%20red.%20If%20the%20scores%20go%20up%20to%20Above%20LCL%20then%20those%20two%20cells%20for%20that%20day%20in%20those%20two%20columns%20will%20be%20orange.%20Then%20the%20next%20two%20columns%20of%20the%20four%20columns%20are%20labeled%20as%20follows%3A%20Incentive%20Quadrant%20and%20Incentive%20Score%20and%20the%20same%20rules%20apply%20for%20the%20color%20code%20depending%20on%20the%20score.%20The%20remaining%20columns%20I%20am%20able%20to%20apply%20conditional%20formatting%20based%20on%20the%20cut-off%20point%20for%20those%20percentages.%20I%20am%20just%20recreating%20an%20already%20created%20spreadsheet%20but%20am%20unable%20to%20copy%20and%20paste%20due%20to%20the%20policies%20in%20place%20which%20doesn't%20allow%20the%20copying%20or%20emailing%20of%20data.%20So%20I%20just%20need%20to%20extend%20the%20conditional%20formatting%20for%20the%20Overall%20Score%20to%20match%20the%20color%20of%20the%20Overall%20Quadrant%2C%20which%20is%20determined%20on%20a%20scale%20of%201-10%20with%201%20being%20the%20lowest%20and%2010%20being%20the%20highest.%20Anything%20that%20is%201%20standard%20deviation%20below%20the%20average%20score%20is%20Above%20LCL%20(orange)%2C%20anything%20that%20is%201%20standard%20deviation%20above%20the%20average%20score%20is%20Above%20Average%20(yellow)%2C%20Anything%20that%20is%201%20standard%20deviation%20above%20Above%20Average%20is%20Above%20UCL%20(green)%20and%20anything%20that%20is%201%20standard%20deviation%20below%20Above%20LCL%20is%20Below%20LCL%20(red).%20The%20same%20rule%20applies%20to%20the%20Incentive%20Quadrant%20and%20the%20Incentive%20Score.%20So%20I%20just%20need%20to%20know%20how%20to%20make%20a%20rule%20for%20the%20Overall%20Quadrant%20which%20will%20always%20produce%20a%20result%20that%20is%20text%20and%20the%20Overall%20Score%20which%20will%20always%20produce%20a%20result%20that%20is%20numeric%20to%20make%20both%20columns%20be%20the%20same%20color%20based%20on%20the%20scores%20which%20will%20show%20whether%20it's%20Below%20LCL%2C%20Above%20LCL%2C%20Above%20Average%2C%20or%20Above%20UCL.%20Hopefully%2C%20this%20answers%20your%20question.%20If%20not%20please%20let%20me%20know%20what%20other%20information%20I%20can%20provide.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
jeriinfo4u
New Contributor

Some of my cells on my spreadsheet need to be the same color as the previous cell but the cell I need to be the same color will always be changing value but it will always be the same color as the previous cell. For instance, any cells that contain the text "Above Average" are orange and the following cell, regardless of the value needs to be the same color as the "Above Average" cell because the two will always be the same value. If it says "Average" and is yellow then the following cell needs to be yellow also. How do I form and apply that rule?

4 Replies

@jeriinfo4u 

If your data like this

image.png

you may apply conditional formatting rule with formula

=OR(A2="Average",OFFSET(A2,-1,0)="Average")

to your column starting from A2

@Sergei Baklan Thank you for your response. So my data looks like the following:

 

DateOverall QuadrantOverall ScoreIncentive QuadrantIncentive Score
9.27.2019Above LCL5.07Above Average7.25

So Row 2 Column 2 and Column 3 need to be the same color just as Row 2 Column 4 and Column 5 need to be the same color. The values in Column 3 and Column 5 will always be the same color as Column 2 and Column 4 regardless of the number. So Above LCL is orange and so is 5.07 and Above Average is yellow and so is 7.25. If it were to drop down to below average or any other description then the colors set for those will either be red or green and the following column's value would match the values set for below average and would also be the same color as the color for below average. I guess the simpler way to say this is the Overall Quadrant and Overall Score will always be the same color as will the Incentive Quadrant and the Incentive Score. Sorry, I didn't mean to make that confusing.

@jeriinfo4u 

Could you please clarify the starting point - why your cell, let say Above LCL as in example, is orange or green or red? Do you apply conditional formatting to it use some formula or color manually? In first case you may expand the logic on other cells, if the latest then only with VBA programming.

@Sergei Baklan So this spreadsheet is the daily scorecard for all agents at my job. We have been provided a Mac for employment purposes only and are not allowed to email anything from our work computer to our personal email or anything like that. So I have pulled my row out of the spreadsheet and am manually entering the data daily based on the scorecard and the color key is as follows:

Red is Below LCL, orange is Above LCL, yellow is Above Average, and green is Above LCL. The first four columns of colored cells are labeled as follows: Overall Quadrant and Overall Score which will always be the same color based on what the scores are. If the scores are Below LCL then both cells for that day in those two columns will be red. If the scores go up to Above LCL then those two cells for that day in those two columns will be orange. Then the next two columns of the four columns are labeled as follows: Incentive Quadrant and Incentive Score and the same rules apply for the color code depending on the score. The remaining columns I am able to apply conditional formatting based on the cut-off point for those percentages. I am just recreating an already created spreadsheet but am unable to copy and paste due to the policies in place which doesn't allow the copying or emailing of data. So I just need to extend the conditional formatting for the Overall Score to match the color of the Overall Quadrant, which is determined on a scale of 1-10 with 1 being the lowest and 10 being the highest. Anything that is 1 standard deviation below the average score is Above LCL (orange), anything that is 1 standard deviation above the average score is Above Average (yellow), Anything that is 1 standard deviation above Above Average is Above UCL (green) and anything that is 1 standard deviation below Above LCL is Below LCL (red). The same rule applies to the Incentive Quadrant and the Incentive Score. So I just need to know how to make a rule for the Overall Quadrant which will always produce a result that is text and the Overall Score which will always produce a result that is numeric to make both columns be the same color based on the scores which will show whether it's Below LCL, Above LCL, Above Average, or Above UCL. Hopefully, this answers your question. If not please let me know what other information I can provide. Thank you!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies