Conditional Formatting Using Percentages

%3CLINGO-SUB%20id%3D%22lingo-sub-1017548%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017548%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20highlight%20a%20cell%20that%20is%2010%25%20over%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB1%20is%20%24100%3C%2FP%3E%3CP%3EB2%20is%20%24110%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20need%20B2%20to%20be%20highlighted.%20since%20its%2010%25%20higher.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20do%20this%20if%20put%20in%20with%20exact%20numbers%20but%20i%20want%20it%20done%20in%20percentage%20for%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1017548%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017593%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462032%22%20target%3D%22_blank%22%3E%40MiMiMarie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20the%20following%20formula%20for%20making%20a%20new%20rule%20for%20conditional%20formatting...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DB2%26gt%3B%3DB1*1.1%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017688%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWORKED%20GREAT!!!!%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20Question%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20get%20it%20to%20work%20for%20the%20rest%20of%20my%20cells%20so%20i%20don't%20have%20to%20input%20it%20for%20every%20single%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%3A%3C%2FP%3E%3CP%3EB1%20%24100%3C%2FP%3E%3CP%3EB2%20%24110%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3DB2%26gt%3B%3DB1*1.1%3C%2FP%3E%3CP%3EB3%20%24200%3C%2FP%3E%3CP%3EB4%20%24220%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3DB4%26gt%3B%3DB3*1.1%3C%2FP%3E%3CP%3EB5%20%24300%3C%2FP%3E%3CP%3EB6%20%24330%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3DB6%26gt%3B%3DB5*1.1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20B2%20to%20change%2C%20B4%20to%20change%2C%20%26amp%3B%20B6%20to%20change.%20I%20can't%20copy%20the%20formatting%20because%20it%20only%20copies%20the%20original%20formatting%20and%20doesn't%20continue%20it%20down%20the%20line%20with%20new%20cells.%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%2010%20workbooks%20to%20do%20this%20to%20with%20about%20200%2B%20cells%20and%20I%20dont%20want%20to%20spend%20weeks%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017775%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017775%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462032%22%20target%3D%22_blank%22%3E%40MiMiMarie%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20conditional%20format%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eapplied%20to%20only%20B2%2C%20select%20B1%26nbsp%3B%3CEM%3Eand%26nbsp%3B%3C%2FEM%3EB2.%20Then%20select%20the%20format%20painter%20(looks%20like%20a%20paint%20brush%20in%20the%20home%20tab)%20and%20select%20your%20entire%20list.%20This%20will%20apply%20your%20format%20to%20every%20other%20cell%20starting%20at%20B2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20You%20may%20double%20click%20the%20format%20painter%20to%20use%20it%20multiple%20times%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017818%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017818%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20didn't%20work.%20It%20only%20copied%20the%20original%20format%20with%20the%20original%20cells%20in%20the%20format.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017846%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462032%22%20target%3D%22_blank%22%3E%40MiMiMarie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20that%20the%20conditional%20format%20in%20B2%20does%20not%20use%20absolute%20references.%20The%20conditional%20format%20in%20B2%20should%20be%26nbsp%3B%3CSPAN%3E%3CEM%3E%3DB2%26gt%3B%3DB1*1.1%3C%2FEM%3E%2C%20not%26nbsp%3B%3CEM%3E%3D%24B%242%26gt%3B%3D%24B%241*1.1%3C%2FEM%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1018599%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1018599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F462032%22%20target%3D%22_blank%22%3E%40MiMiMarie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20case%20you%20may%20select%20the%20range%20B2%3AB6%20and%20make%20a%20new%20rule%20for%20conditional%20formatting%20using%20the%20formula%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAND(MOD(ROW()%2C2)%3D0%2CB2%26gt%3B%3DB1*1.1)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1018698%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1018698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%20-%20Format%20Painter%20will%20create%20a%20bunch%20of%20new%20rules%2C%20better%20to%20apply%20formatting%20to%20entire%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1018729%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Using%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1018729%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20comment.%20You%20are%20correct.%3C%2FP%3E%3CP%3EWith%20that%20in%20mind%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Boffers%20a%20much%20more%20elegant%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I need to highlight a cell that is 10% over another cell.

 

example

 

B1 is $100

B2 is $110

 

i need B2 to be highlighted. since its 10% higher.

 

I know how to do this if put in with exact numbers but i want it done in percentage for

8 Replies
Highlighted

@MiMiMarie 

You may try the following formula for making a new rule for conditional formatting...

=B2>=B1*1.1

Highlighted

@Subodh_Tiwari_sktneer 

 

WORKED GREAT!!!! 

Next Question:

 

How can I get it to work for the rest of my cells so i don't have to input it for every single one.

 

example:

B1 $100

B2 $110      =B2>=B1*1.1

B3 $200

B4 $220      =B4>=B3*1.1

B5 $300

B6 $330      =B6>=B5*1.1

 

I need B2 to change, B4 to change, & B6 to change. I can't copy the formatting because it only copies the original formatting and doesn't continue it down the line with new cells. Any suggestions?

 

I have 10 workbooks to do this to with about 200+ cells and I dont want to spend weeks on this.

 

Highlighted

Hello @MiMiMarie,

 

With the conditional format applied to only B2, select B1 and B2. Then select the format painter (looks like a paint brush in the home tab) and select your entire list. This will apply your format to every other cell starting at B2.

 

P.S. You may double click the format painter to use it multiple times

Highlighted

That didn't work. It only copied the original format with the original cells in the format. @PReagan 

Highlighted

@MiMiMarie 

 

Make sure that the conditional format in B2 does not use absolute references. The conditional format in B2 should be =B2>=B1*1.1, not =$B$2>=$B$1*1.1

Highlighted

@MiMiMarie 

In that case you may select the range B2:B6 and make a new rule for conditional formatting using the formula given below...

 

=AND(MOD(ROW(),2)=0,B2>=B1*1.1)

Highlighted

@PReagan 

As a comment - Format Painter will create a bunch of new rules, better to apply formatting to entire range.

Highlighted

@Sergei Baklan 

 

Thank you for your comment. You are correct.

With that in mind, @Subodh_Tiwari_sktneer offers a much more elegant solution.