Custom Zones - 1064 - Learn Excel from MrExcel Podcast

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 23, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top