In yesterday's netcast, I talked about using four zones in custom number formats for positive, negative, zero, and text. In Episode 1064, you will learn how to create your own zones for formatting.
This is the video 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 is the video 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.
Now, yesterday we talked about setting up custom number formats 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 based on positive and negative, maybe you want to highlight everything that's above 90 as 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, all right 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, in quotes, and then the number so 0, 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 0, and then finally what to do if it's anything else if it's anything else we just want in square brackets blue and 0 [ >90 ][ Green ]”* “0;[ <80 ][ Red ]”!!
“0;[ Blue ]0,click OK, you'll see what we get there to instantly highlight all of the high numbers in green with the low numbers in red with the exclamation points and everything else in blue and of course you can be as creative as you one 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 a great 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 a 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 MrExcel.
Now, yesterday we talked about setting up custom number formats 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 based on positive and negative, maybe you want to highlight everything that's above 90 as 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, all right 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, in quotes, and then the number so 0, 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 0, and then finally what to do if it's anything else if it's anything else we just want in square brackets blue and 0 [ >90 ][ Green ]”* “0;[ <80 ][ Red ]”!!
“0;[ Blue ]0,click OK, you'll see what we get there to instantly highlight all of the high numbers in green with the low numbers in red with the exclamation points and everything else in blue and of course you can be as creative as you one 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 a great 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 a 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 MrExcel.
