SOLVED

Probleam: Icons in condictional formatting change with wrong cells

Copper Contributor

I have a confusing problem while trying to use icons in conditional formatting. To describe the problem clearly, I opened a new blank workbook..

 

I would like to use pie icons to visualize values in array A1:A10, see the captures below.

1.JPG2.JPG

 

I simply use the default base values and the rules shall be clear. The pie icons should only be based on the value of its own cell. Yet they seem to change as I change the values of other cells, and often don't make sense to me. It only works correctly when all the cells have been filled in with values.

 

3.png

 

I tried with different workbooks and changed the settings for a hundred times, yet it still doesn't work.... Do I understand the conditional formatting rule in a wrong way or something? Could someone be so kind to help?

6 Replies

Hey @CathySong 

 

The reason the conditional formatting changes as you type in a value is because Excel calculates what percentage the value you type in is to the entire column you applied the conditional formatting to.

 

Remember, when you changed the data type to percentage, the value in the cells was only formatted to percentage, the actual value is a numerical value. So for 10% it is stored as 0.1, for 20% it is 0.2

 

So, at first, when you typed in 10 in column A and excel formatted it to a percentage, the reason the black circle appeared is because Excel took the 0.1 (which was formatted to 10%) and divided it by the sum of the range you applied the formatting to, which is 0.1 + 0 + 0+ 0+ 0+ 0+ 0+ 0+ 0+ 0 = 0.1.

Therefore, 0.1/0.1 = 1, which as a percentage is 100% and according to you formatting rules, a black circle should appear for this circle

 

Same, with the second incident where you typed in 20 below the 10%.

A white circle is shown in the first cell because 0.1 / (0.1 +0.2 +0+ 0+ 0+ 0+ 0+ 0+ 0+ 0) * 100 = 16.7%, which according to your rules is below 20% and thus, must have a white circle

best response confirmed by CathySong (Copper Contributor)
Solution

@CathySong 

 

That's since you took percent as the type. Excel takes entire range and calculates for the cell value in which % it within the range. If to apply icon only based on concrete cell value select Number as type and set values as 0.8, 0.6, 0.4, 0.2 in your rule.

 

Type is not connected to the format you apply to your cells (%, date, whatever).

@Excel_World_Champion Thank you so much! I've changed the data type to "Number" and the values to 0.8, 0.6  0.4, 0.2, now the problem has been solved. You just made my day!

Hey @CathySong 

 

Also, I don't think that your conditional format works even when all the cells are filled since 80% shows a three-quarter filled cell, but I think you wanted it to show a fully black circle.

 

What you can do to fix this is that change your data type back to General, and it should come up with 0.1, 0.2, etc.

 

Then Edit Formatting Rule options, Change the Type from percentage to Number:

1.PNG

 

Then just reformat the column back to the data type Percentage

@Excel_World_Champion , it's not necessary to re-format source cells, Conditional Formatting doesn't care which format is applied to the cells, it takes only values.

@Excel_World_Champion

 

I kept the format setting in the cells and adjusted settings as shown below, it works well. :)

3.JPG

 

1 best response

Accepted Solutions
best response confirmed by CathySong (Copper Contributor)
Solution

@CathySong 

 

That's since you took percent as the type. Excel takes entire range and calculates for the cell value in which % it within the range. If to apply icon only based on concrete cell value select Number as type and set values as 0.8, 0.6, 0.4, 0.2 in your rule.

 

Type is not connected to the format you apply to your cells (%, date, whatever).

View solution in original post