Conditional formatting rules using a formula AND an icon set

%3CLINGO-SUB%20id%3D%22lingo-sub-812483%22%20slang%3D%22en-US%22%3EConditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812483%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20create%20a%20conditional%20formatting%20rule%20that%20uses%20a%20formula%20and%20also%20allows%20me%20to%20use%20an%20icon%20set%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20conditional%20formatting%20dialog%20has%20an%20option%20for%20formatting%20based%20on%20a%20formula%2C%20which%20is%20ideal%2C%20but%20the%20resulting%20options%20don't%20let%20me%20use%20icon%20sets%20or%20data%20bars%20etc.%20They%20only%20let%20me%20change%20the%20font%20formatting%2C%20borders%2C%20patterns%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20show%20an%20icon%20based%20on%20the%20value%20of%20the%20cell%2C%20but%20only%20on%20lines%20where%20the%20word%20%22MAINT%22%20appears%20in%20a%20certain%20cell%20at%20the%20start%20of%20the%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20doable%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOlly%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-812483%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813144%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F395019%22%20target%3D%22_blank%22%3E%40olivermuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Olly%2C%3C%2FP%3E%0A%3CP%3EYou%20may%20work%20with%20formulas%20and%20the%20icons%20set%2C%20the%20only%20issue%20formulas%20here%20work%20only%20with%20absolute%20references.%20As%20workaround%20you%20may%20use%20OFFSET%2C%20INDIRECT%20or%20INDEX%20to%20have%20the%20proper%20references.%20For%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20699px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127643iD10357BE4543A285%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%3Efirst%20formula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDIRECT(%22A%22%26amp%3BROW())*1.2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Esecond%20one%20is%20similar.%3C%2FP%3E%0A%3CP%3EAnother%20trick%2C%20you%20can't%20apply%20such%20rule%20to%20entire%20range%20at%20once.%20Thus%20first%20apply%20to%20B1%20(for%20our%20sample)%20and%20that%20move%20formatting%20by%20Format%20Painter%20on%20other%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2236997%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2236997%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%3BHmmm...whilst%20it%20works%2C%20you%20end%20up%20with%20a%20CF%20rule%20for%20each%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2237152%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2237152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F40858%22%20target%3D%22_blank%22%3E%40Jeremy%20Norbury%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20how%20it%20works.%20I%20don't%20know%20another%20solution%2C%20sorry.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2237159%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2237159%22%20slang%3D%22en-US%22%3EI%20agree%20-%20it's%20just%20not%20very%20scalable.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2237803%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2237803%22%20slang%3D%22en-US%22%3ESergei%20thank%20you%2C%20your%20suggestion%20worked%20a%20treat%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716660%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F40858%22%20target%3D%22_blank%22%3E%40Jeremy%20Norbury%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20not%20help%20when%20scrolling%20though%20the%20CF%20list%20the%20contect%20of%20'Applies%20to'%20just%20disappears(not%20really).%20Close%20and%20open%20the%20'Rules%20Manger'%20brings%20them%20back.%20This%20makes%20it%20very%20hard%20to%20adjust%20the%20rules%20on%20the%20specific%20cells.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Florisz_0-1630668791061.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307927i88D5FA242057A44E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Florisz_0-1630668791061.png%22%20alt%3D%22Florisz_0-1630668791061.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716704%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20rules%20using%20a%20formula%20AND%20an%20icon%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394961%22%20target%3D%22_blank%22%3E%40Florisz%3C%2FA%3E%26nbsp%3BYeah%20that's%20not%20very%20handy%20at%20all%2C%20is%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all

 

Is it possible to create a conditional formatting rule that uses a formula and also allows me to use an icon set?

 

The conditional formatting dialog has an option for formatting based on a formula, which is ideal, but the resulting options don't let me use icon sets or data bars etc. They only let me change the font formatting, borders, patterns etc.

 

I want to show an icon based on the value of the cell, but only on lines where the word "MAINT" appears in a certain cell at the start of the row. 

 

Is that doable?

 

Olly

 

7 Replies

@olivermuk 

Hi Olly,

You may work with formulas and the icons set, the only issue formulas here work only with absolute references. As workaround you may use OFFSET, INDIRECT or INDEX to have the proper references. For such sample

image.png

first formula is

=INDIRECT("A"&ROW())*1.2

second one is similar.

Another trick, you can't apply such rule to entire range at once. Thus first apply to B1 (for our sample) and that move formatting by Format Painter on other cells.

 

 

 

@Sergei Baklan Hmmm...whilst it works, you end up with a CF rule for each cell.

 

@Jeremy Norbury 

That how it works. I don't know another solution, sorry.

I agree - it's just not very scalable.
Sergei thank you, your suggestion worked a treat

@Jeremy Norbury 

It does not help when scrolling though the CF list the contect of 'Applies to' just disappears(not really). Close and open the 'Rules Manger' brings them back. This makes it very hard to adjust the rules on the specific cells.

Florisz_0-1630668791061.png

 

@Florisz Yeah that's not very handy at all, is it?