Adjust the ranges used by the icon sets. Episode 924 will show you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So how are we going to analyze this?
Well, let's fire up a Pivot Table and see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
This week, we've been talking about Excel 2007 and the Icon Sets under Conditional Formatting.
So, normally, you choose the Icon Set, and it's just going to distribute the icons in equal fashion so, that way, within the data set, it's going to, basically, break it into, in this case, not quartiles, but thirds.
So, we want to have some control over that.
If we go into Conditional Formatting and say that we want to Manage Rules, Manage Rules will see the Icon Set that was set up.
We're going to choose that Icon Set and click Edit Rule.
And here, we actually get to absolute control.
So we can say that we don't want to do it based on a percent; we want to do it based on a number.
And we can say we want the green any time that we're at, let's say, 90 and above; the yellow will come in for 70 and above; and then the red X comes in under 70.
So if this was some sort of a grading scale or something like that, we click Ok-- click OK.
And now we've really changed the format, so we can see, you know, the people that have A's have the green checkmark; the people that are failing or close to failing have the red X; and everyone else with B's and C's is just, kind of, in the yellow range.
So, a lot more power when you go into Manage Rules-- you can actually change the various settings to build your own ranges, rather than just allowing Excel to apply right.
I kind of like this, they let you get to an Icon Set very, very quickly, but if you need more control you can go in and find more control.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Basically, we start out with massive amounts of data.
So how are we going to analyze this?
Well, let's fire up a Pivot Table and see if we can solve this problem.
Hey, welcome back to the MrExcel netcast.
This week, we've been talking about Excel 2007 and the Icon Sets under Conditional Formatting.
So, normally, you choose the Icon Set, and it's just going to distribute the icons in equal fashion so, that way, within the data set, it's going to, basically, break it into, in this case, not quartiles, but thirds.
So, we want to have some control over that.
If we go into Conditional Formatting and say that we want to Manage Rules, Manage Rules will see the Icon Set that was set up.
We're going to choose that Icon Set and click Edit Rule.
And here, we actually get to absolute control.
So we can say that we don't want to do it based on a percent; we want to do it based on a number.
And we can say we want the green any time that we're at, let's say, 90 and above; the yellow will come in for 70 and above; and then the red X comes in under 70.
So if this was some sort of a grading scale or something like that, we click Ok-- click OK.
And now we've really changed the format, so we can see, you know, the people that have A's have the green checkmark; the people that are failing or close to failing have the red X; and everyone else with B's and C's is just, kind of, in the yellow range.
So, a lot more power when you go into Manage Rules-- you can actually change the various settings to build your own ranges, rather than just allowing Excel to apply right.
I kind of like this, they let you get to an Icon Set very, very quickly, but if you need more control you can go in and find more control.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
