Forum Discussion

somnath6309's avatar
somnath6309
Copper Contributor
Jul 15, 2025

Conditional Formatting Rules for Icon Sets

Hi, The following pivot table has been created using Conditional formatting Icon Sets : However, the Default Formatting Rule applied by Excel is not understood. Following is the screen shot of ...
excel
office 365
somnath6309's avatar
somnath6309
Copper Contributor
Jul 18, 2025

Hi, JKPieterse​ ,

applied your formula to calculate the Percentages. Pls see the Column E of the following screen shot :

I tried to calculate the Sales Amount Pct. The formula bar contains your formula. 

All the Pct values in Col E, are appearing in Negative figure as we are deducting the Max value from the Current Value. Pls note that the Pct for Maximuam Amount is coming 0% and for Minimum Amount is coming 100%. However, if we remove the Minus sign by using ABS() Function, the resulting values do not qualify the formatting rule. 

If the Formatting Rule follows the aforesaid calculation, the Sales amt of CANADA, DALLAS and DENVER, for example, should get "Green" icon because Sales Pct in Col E exceeds 67%.

I wish there should be some correction in the formula. Will wait for your reply. 

Regards,

Somnath6309

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 19, 2025

It's not clear what you'd like to receive. Here

on the left "default" rule which applies green to the values for which

(value - min) / (max - min)

 is greater than 66%, similar for next ones.

On the right we apply green to the values which are greater than 10% of total sum, yellow to ones between 5% and 10% of total sum, red to the rest.

Depends on goals you may generate another variants.

Resources