LearnAccessByCrystal at YouTube asked for a podcast with information about the zones in a custom number format. In Episode 1063, an explanation of the four zones in a custom number format.
This blog 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 blog 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 MrExcel netcast, I'm Bill Jelen.
Today's tip is sent in by Crystal. Now, Crystal is a Microsoft MVP and Access and crystal is running some videos at YouTube just search for a LearnAccessbyCrystal all one word, that's a reducer ID and you'll be able to learn Access you go.
Crystal said hey why don't you talk about the different formatting codes in Excel and let's just take a quick look I'm going to choose these and press CTRL 1 and you know we'll set up some sort of a format code maybe currency with two decimal places, click OK, and what happens there if I go back in and click on CUSTOM you'll see that we've created a custom number format with a single zone if possible to have custom number formats that have two zones, so I'm going to choose these CTRL 1 and we'll go into custom, and I'm going to put two zones in each zone is separated by a semicolon so let's put number, comma, number, number, zero and then a semicolon and a second thing; this will be for negative numbers, number, comma, number, number, zero, now the way that this works is if you only have two zones the first zone is used for positive and zero numbers, the second zone is used for negative numbers so we click OK and you see that our negative numbers now have a minus sign in front of them, but we can change that if you wanted your minus to appear afterwards we can put the minus sign after the fact, click OK, and now you'll see that we get the minus sign as a trailing minus sign.
You can also do any kind of thing that you want for example; if you want to put CR to indicate that it's a credit that has to be in quotes, click OK, and it puts CR instead of a minus sign.
Very clever the way that works, now it's possible to have three zones, three zones?
How does that work with three zones?
The first zone is for positive numbers so let's just put a zero there, the second zone of course is for negative numbers negative zero, and then the third zone is for zeros, now maybe four zeros we don't want to have anything up here I don't know why, so let's just in quotes here put dash, dash, and click OK, and now you see that the zeros appear as a dash-dash and you really have a lot of flexibility, you can put you know the word zero or you know whatever you need to know sales or you know why didn't you show up today, or you know whatever, you have a lot of control there.
Now there is a fourth zone; in the fourth zone controls text, so here let's build this I will say for positive numbers we want a zero, for negative numbers we want them in parentheses, for zeros we want a zero, and then for the fourth zone if someone would enter some text we could put invalid entry, click OK, and you see that our positive numbers come out okay, the negative numbers are in parentheses and anytime that any words are there we get invalid entry.
Now if I enter a number it shows up, if I enter some text it shows up as invalid entry.
Now the cell still contains that text that we entered but it's displayed and printed using the custom number format.
Now some cool things we can do here; if you wanted to prevent text from showing up you would just leave that zone blank, so in other words just nothing after the semicolon and the text does not appear.
You can also use this trick that has a fast way to hide things just put one, two, three, semicolons with nothing and you'll get nothing for positive negative zero and text.
Now one more cool thing we can do here let's say we want to control some colors and of course we can do this we came to additional formatting but let's just talk about another way to go in square brackets of the word green and 0 that will make all of our positive number show up as green and then in square brackets red and a 0 and then maybe zeros themselves should be neutral with a blue and 0 and then just everything else as text will let that appear, click OK, and there you have it the positive numbers in green, 0 in blue, and the negative numbers in red notice without any sort of a minus sign because I forgot to put that in.
Now you only have eight colors here I think it's a white, black, magenta, blue, green, cyan, a few others you can check excel help on that but interesting most people they just see the one custom number format that you get when you choose any of the building items, but it's possible to go through and use two, three, or four zones, check it out next time you need to have some control over the way that you display your numbers.
I want to thank Crystal for that suggestion.
We'll see you next time for another netcast for MrExcel.
Today's tip is sent in by Crystal. Now, Crystal is a Microsoft MVP and Access and crystal is running some videos at YouTube just search for a LearnAccessbyCrystal all one word, that's a reducer ID and you'll be able to learn Access you go.
Crystal said hey why don't you talk about the different formatting codes in Excel and let's just take a quick look I'm going to choose these and press CTRL 1 and you know we'll set up some sort of a format code maybe currency with two decimal places, click OK, and what happens there if I go back in and click on CUSTOM you'll see that we've created a custom number format with a single zone if possible to have custom number formats that have two zones, so I'm going to choose these CTRL 1 and we'll go into custom, and I'm going to put two zones in each zone is separated by a semicolon so let's put number, comma, number, number, zero and then a semicolon and a second thing; this will be for negative numbers, number, comma, number, number, zero, now the way that this works is if you only have two zones the first zone is used for positive and zero numbers, the second zone is used for negative numbers so we click OK and you see that our negative numbers now have a minus sign in front of them, but we can change that if you wanted your minus to appear afterwards we can put the minus sign after the fact, click OK, and now you'll see that we get the minus sign as a trailing minus sign.
You can also do any kind of thing that you want for example; if you want to put CR to indicate that it's a credit that has to be in quotes, click OK, and it puts CR instead of a minus sign.
Very clever the way that works, now it's possible to have three zones, three zones?
How does that work with three zones?
The first zone is for positive numbers so let's just put a zero there, the second zone of course is for negative numbers negative zero, and then the third zone is for zeros, now maybe four zeros we don't want to have anything up here I don't know why, so let's just in quotes here put dash, dash, and click OK, and now you see that the zeros appear as a dash-dash and you really have a lot of flexibility, you can put you know the word zero or you know whatever you need to know sales or you know why didn't you show up today, or you know whatever, you have a lot of control there.
Now there is a fourth zone; in the fourth zone controls text, so here let's build this I will say for positive numbers we want a zero, for negative numbers we want them in parentheses, for zeros we want a zero, and then for the fourth zone if someone would enter some text we could put invalid entry, click OK, and you see that our positive numbers come out okay, the negative numbers are in parentheses and anytime that any words are there we get invalid entry.
Now if I enter a number it shows up, if I enter some text it shows up as invalid entry.
Now the cell still contains that text that we entered but it's displayed and printed using the custom number format.
Now some cool things we can do here; if you wanted to prevent text from showing up you would just leave that zone blank, so in other words just nothing after the semicolon and the text does not appear.
You can also use this trick that has a fast way to hide things just put one, two, three, semicolons with nothing and you'll get nothing for positive negative zero and text.
Now one more cool thing we can do here let's say we want to control some colors and of course we can do this we came to additional formatting but let's just talk about another way to go in square brackets of the word green and 0 that will make all of our positive number show up as green and then in square brackets red and a 0 and then maybe zeros themselves should be neutral with a blue and 0 and then just everything else as text will let that appear, click OK, and there you have it the positive numbers in green, 0 in blue, and the negative numbers in red notice without any sort of a minus sign because I forgot to put that in.
Now you only have eight colors here I think it's a white, black, magenta, blue, green, cyan, a few others you can check excel help on that but interesting most people they just see the one custom number format that you get when you choose any of the building items, but it's possible to go through and use two, three, or four zones, check it out next time you need to have some control over the way that you display your numbers.
I want to thank Crystal for that suggestion.
We'll see you next time for another netcast for MrExcel.
