Patrick asks how Excel chooses to assign icons to values when you use the icon set feature. Episode 1208 explains how the icon sets work and how to change the default behavior. Learn Excel 97-2007 from Mrexcel.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm laughing because we're going to do conditional formatting, yet again I didn't plan it this way.
Just got a great question from Patrick, Patrick has sent in many questions to the podcast, and he’s asking “How the heck the icon sets actually work?
How do we know which cells are going to get which color?” So icon sets were added in Excel 2007, you know, in this case, largest numbers in green, smallest numbers in red.
And where's the break?
Patrick asks.
So now by default, let me explain how this works, is they look at the range of data.
So in this case my data goes from 1-99, and so what we should see, roughly, is, numbers in the 1-33 range should be red, and the lowest number with yellow should be, you know, around 36.
Also again, the break from yellow to green should be around 67-68, somewhere in there.
So here you see 67 in green, but the danger here is that Microsoft is automatically looking at the largest and smallest values in the range.
So if all of a sudden you would get an outlier, so you'd have a -99 and a +199.
Then, all of a sudden, the range of this data, you know, spans three hundred rows.
And basically everything from -99-0 is going to get a red, everything from 100-199 is going to get a green.
And you have just a whole bunch of data that’s getting yellows, because those two outliers are really screwing things up.
So, what we can do, Conditional Formatting, Manage Rules, and then say Edit the Rule.
There are a lot of great settings here where we can control this, so right now it's based on percent, but we could put in absolute numbers.
So if it's >=67, then we get the green.
We can also go with a number of =33 or 34, whichever you want to do, OK, click OK, and those now kind of go back to a relatively normal distribution.
This can also be good if you had some sort of a scoring level so, you know, if you were looking at quality.
Any quality above %95 for the day is good, 90% is yellow and everything else would be red.
You can set those up using the Manage Rules dialog in Conditional Formatting, so certainly a lot more powerful than just turning on the tool and getting what you get, you have a lot of different options for control here.
There you have it.
Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm laughing because we're going to do conditional formatting, yet again I didn't plan it this way.
Just got a great question from Patrick, Patrick has sent in many questions to the podcast, and he’s asking “How the heck the icon sets actually work?
How do we know which cells are going to get which color?” So icon sets were added in Excel 2007, you know, in this case, largest numbers in green, smallest numbers in red.
And where's the break?
Patrick asks.
So now by default, let me explain how this works, is they look at the range of data.
So in this case my data goes from 1-99, and so what we should see, roughly, is, numbers in the 1-33 range should be red, and the lowest number with yellow should be, you know, around 36.
Also again, the break from yellow to green should be around 67-68, somewhere in there.
So here you see 67 in green, but the danger here is that Microsoft is automatically looking at the largest and smallest values in the range.
So if all of a sudden you would get an outlier, so you'd have a -99 and a +199.
Then, all of a sudden, the range of this data, you know, spans three hundred rows.
And basically everything from -99-0 is going to get a red, everything from 100-199 is going to get a green.
And you have just a whole bunch of data that’s getting yellows, because those two outliers are really screwing things up.
So, what we can do, Conditional Formatting, Manage Rules, and then say Edit the Rule.
There are a lot of great settings here where we can control this, so right now it's based on percent, but we could put in absolute numbers.
So if it's >=67, then we get the green.
We can also go with a number of =33 or 34, whichever you want to do, OK, click OK, and those now kind of go back to a relatively normal distribution.
This can also be good if you had some sort of a scoring level so, you know, if you were looking at quality.
Any quality above %95 for the day is good, 90% is yellow and everything else would be red.
You can set those up using the Manage Rules dialog in Conditional Formatting, so certainly a lot more powerful than just turning on the tool and getting what you get, you have a lot of different options for control here.
There you have it.
Want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
