SOLVED

Conditional formatting icon sets aren’t working

%3CLINGO-SUB%20id%3D%22lingo-sub-1412526%22%20slang%3D%22en-US%22%3EConditional%20formatting%20icon%20sets%20aren%E2%80%99t%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412526%22%20slang%3D%22en-US%22%3EI%20have%20a%20column%20I%20would%20like%20to%20conditionally%20format%20by%20adding%20icons%20to%20the%20cells.%20The%20data%20in%20each%20cell%20is%20simply%20a%20number%20from%201%20to%205.%20When%20I%20try%20to%20apply%20the%20conditional%20formatting%20rule%2C%20nothing%20happens.%20No%20icons%20appear%20at%20all.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20starting%20from%20scratch%20in%20case%20there%20was%20some%20underlying%20formatting%20that%20was%20overruling%20the%20new%20rule%2C%20but%20nothing%20has%20worked.%20I%E2%80%99ve%20also%20tried%20to%20change%20the%20conditions%20of%20the%20rule%20(numbers%20instead%20of%20%25%2C%20etc.)%2C%20but%20that%20doesn%E2%80%99t%20work.%20Other%20formatting%20rules%20work%2C%20just%20not%20the%20icons.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20successfully%20applied%20the%20same%20formatting%20to%20another%20table%20with%20a%20similar%20set%20of%20data.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20context%2C%20the%20table%20is%20appended%20from%20a%20PivotTable%20in%20the%20same%20worksheet.%20The%20PivotTable%20received%20its%20data%20from%20another%20source%20table%20in%20the%20worksheet.%20The%20only%20difference%20I%20can%20gather%20from%20the%20one%20that%20works%20and%20one%20that%20doesn%E2%80%99t%20is%20that%20they%20come%20from%20different%20source%20tables%3B%20otherwise%2C%20the%20formatting%20and%20data%20is%20nearly%20identical.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20try%20to%20attach%20a%20screenshot%20soon.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1412526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412537%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20icon%20sets%20aren%E2%80%99t%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412537%22%20slang%3D%22en-US%22%3ESelect%20the%20range%20and%20click%20on%20Icon%20Set%20conditional%20formatting..%20Then%2C%20you%20can%20modify%20the%20rule%20to%20what%20you%20need%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412563%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20icon%20sets%20aren%E2%80%99t%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677310%22%20target%3D%22_blank%22%3E%40Charlespower74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20formatting%20rule%20doesn't%20care%20how%20values%20appeared%20in%20the%20range%20cells.%3C%2FP%3E%0A%3CP%3EIcon%20could%20not%20appear%20if%20you%20apply%20the%20rule%20to%20text%20values%2C%20are%20you%20sure%20the%20range%20definitely%20have%20numbers%2C%20not%20the%20texts%20which%20present%20numbers%3F%20Other%20words%2C%20what%20returns%20%3DISTEXT()%20on%20such%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413902%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20icon%20sets%20aren%E2%80%99t%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413902%22%20slang%3D%22en-US%22%3EThat%20did%20the%20trick!%20The%20formula%20in%20the%20source%20table%20asked%20for%20the%20value%20to%20be%20%E2%80%9C1%E2%80%9D%20instead%20of%201.%20Once%20I%20changed%20that%2C%20the%20icons%20appeared%20immediately.%20It%E2%80%99s%20amazing%20how%20the%20data%20remains%20the%20same%20through%20so%20many%20different%20processes.%20There%20was%20the%20source%20table%2C%20then%20the%20PivotTable%2C%20and%20finally%20a%20third%20table%20where%20I%20wanted%20the%20icons.%20Thank%20you%20for%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413961%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20icon%20sets%20aren%E2%80%99t%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677310%22%20target%3D%22_blank%22%3E%40Charlespower74%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20great%20to%20know%20you%20sorted%20this%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
I have a column I would like to conditionally format by adding icons to the cells. The data in each cell is simply a number from 1 to 5. When I try to apply the conditional formatting rule, nothing happens. No icons appear at all.

I have tried starting from scratch in case there was some underlying formatting that was overruling the new rule, but nothing has worked. I’ve also tried to change the conditions of the rule (numbers instead of %, etc.), but that doesn’t work. Other formatting rules work, just not the icons.

I have successfully applied the same formatting to another table with a similar set of data.

For context, the table is appended from a PivotTable in the same worksheet. The PivotTable received its data from another source table in the worksheet. The only difference I can gather from the one that works and one that doesn’t is that they come from different source tables; otherwise, the formatting and data is nearly identical.

I will try to attach a screenshot soon.
4 Replies
Highlighted
Select the range and click on Icon Set conditional formatting.. Then, you can modify the rule to what you need
Highlighted
Solution

@Charlespower74 

In general formatting rule doesn't care how values appeared in the range cells.

Icon could not appear if you apply the rule to text values, are you sure the range definitely have numbers, not the texts which present numbers? Other words, what returns =ISTEXT() on such cells.

Highlighted
That did the trick! The formula in the source table asked for the value to be “1” instead of 1. Once I changed that, the icons appeared immediately. It’s amazing how the data remains the same through so many different processes. There was the source table, then the PivotTable, and finally a third table where I wanted the icons. Thank you for the help!
Highlighted

@Charlespower74 , you are welcome, great to know you sorted this out.