Custom Zones - 1064 - Learn Excel from MrExcel Podcast
Hey, welcome back to the Mr. Excel netcast. I’m Bill Jelen. Face it you, you start out with a massive amounts of data. Say, how are you going to analyze this, well, let’s fire up activity table and see if we can solve this problem. Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Now, yesterday, we talked about setting up custom number format using the four zones, positive, negative, zero, and text. But it’s possible to define your own zones, and this is pretty cool. You know, what if you don’t want to differentiate base on positive and negative. Maybe you want to highlight everything that’s above 90 is good, and everything that’s below 80 is bad or something like that. You can build your own zones here, it’s pretty cool. They have to be in square brackets, so greater than 90. Alright, now, what do we want to do if it’s greater than 90, well, we do green. And then in quotes, I want to put a star by it, star, space, end quotes and then the number, so zero. So that’s our first zone. You can define up to two conditions here. So, let’s define a second condition that says if it is less than 80. And we’ll put that in square brackets. Now if it’s less than 80, then what we want to do is we want to have it in red and maybe put an exclamation mark before it, so in quotes, exclamation, exclamation, space, and then zero. And then finally what to do if it’s anything else? If it’s anything else, we just want in square brackets blue and zero, click OK. You’ll see when we get there. It instantly highlights all of the high numbers in green, with the asterisk, the lower numbers in red with exclamation points and everything else in blue. And of course you can be as creative as you want here. Now, this is much older than conditional formatting, and with conditional formatting you can go through and have much more control than what we have here. But still, it’s a clever way to go through, and actually control the color of the cells. Add extra words, you know, maybe create job, or whatever you might need to do. And again, the really important thing here is that it still contains the number. It’s just the number 91. And we can use formulas to add these up, that all works. It’s not a matter of, you know, that we actually put text in the cell. We’re actually storing just the value and modifying how that value is displayed and printed. So, yet another use for the custom number formatting. Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from Mr. Excel.