Forum Discussion
Probleam: Icons in condictional formatting change with wrong cells
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).
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
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!
