I used an old custom number formatting trick in yesterday's podcast. Today, we take a look at more possibilities using custom number formats. Episode 724 shows you how.
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 the MrExcel netcast.
I'm Bill Jelen.
Now, yesterday I pulled out an old, old custom number format trick and I wanted to talk about those custom number formats.
If you go to Excel help and look up create or delete a custom number format.
It actually goes into all the detail here.
I'm just going to run through a few of the fascinating possibilities in custom number formats.
Now, basically the first general rule, is that if we have one custom number format it applies to all numbers.
But we can have zones and so if for example, we take a look at this custom number format, I have two zones.
I say if it falls in the first zone, I want it blue with the dollar sign.
If it falls in the second zone, I want it red with no dollar sign.
And the way that this works if you specify two zones the positive numbers.
So five and zero get the first format and the negative numbers get the second format.
And you notice that for the negative numbers if you really wanted the negative sign to show up, you would have to put the negative number in because I left it out.
I forced this negative 2 to show up in red but as a positive number.
All right! Now, let's go on to the next example, if we have three zones, [ ctrl 1 ] to take a look at this with three zones.
What happens, is the first zone applies to the positive numbers, but not to zero.
And then to the negative numbers and then to zero.
So, we can actually put 0 in a different color.
Okay! Now, if we have four zones, the fourth zone is used for text.
So, here we have a situation, we're saying positive numbers show up in blue with a dollar sign, negative numbers show up in red, no dollar sign, no minus sign.
Green numbers, 0 should show up in green and then any text would just show up as yellow as text the add sign is the abbreviation for text.
Okay! So, you wonder what colors are available?
There are eight colors and of course this predates conditional formatting.
We could do all this with conditional formatting, today.
You'll see that we have Black, Cyan, Magenta, Blue, Green ,Red, Yellow.
You can also specify something as White.
Great way to hide it although, if we show it on a black background.
You can see that the white shows up.
Now, here's some interesting things if you have zones, separated by semicolons, and you leave a format out.
For example, let's say we didn't want to show any negative numbers.
Here, I would put in a custom number format of 0 for the positive and then leave the second zone blank.
Nothing for a negative and then a zero to handle the zero values.
You'll see that all of the negative numbers disappear.
Interesting or let's say we just want to hide everything.
Well, put in zones and put nothing at all.
So, I just put three semicolons, that says no matter what, positive, negative, zero, text.
I don't want to show anything.
Cool!
Now, here's an example, where this would actually come in handy.
We have positive, negative and zero and I went in and apply to custom number format that add some text depending on what we have.
So, we'll look at this custom number format.
I said if it's positive, we say your balance is zero.
If it's negative you have a credit balance of and then the number and if it's zero says your account is paid in full.
And so you'll see and let me scroll up, so we can see this at the same time.
This cell F18 actually has a five in it.
It has a five but if being displayed as your balance is five if I would type 17 here, your balance is 17.
If I would type a negative number, you have a credit balance of 100.
Now, in yesterday's podcast.
I talked about using conditions and conditions to add a whole new bit of flexibility.
Over here, I have a simple calculation, where I have the date that the payment was due.
Today's date and then calculate the number of days that have elapsed.
Well, rather than just report the number of days of elapsed, maybe I want to put some text in there to tell the people that their account is current, past, due or seriously past due.
So, let's take a look at this custom number format, again these are the same numbers.
It's the 20 to 35 to 55 and 72 but I'm completely displaying them in a different manner, using a custom number format that says we have a condition.
First condition in square brackets If it's less than 31, your account is current and notice I don't put any digits in there.
I'm not showing the number at all.
I don't have a zero to show the digits.
If it's less than 61, then your account is past due and then finally anything else in other words greater than 61.
I'm going to say your account is seriously past due.
So, you can have some fun with custom number formats to actually change numbers into text without creating if functions or anything like that.
Very interesting, it's obscure.
I find that, I hardly ever have a chance to use it.
It wasn't until that question came up in the seminar and I couldn't figure out a good way to do it in the pivot table that I resorted to this to show those 'yes' and 'no' values from yesterday's podcast.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Now, yesterday I pulled out an old, old custom number format trick and I wanted to talk about those custom number formats.
If you go to Excel help and look up create or delete a custom number format.
It actually goes into all the detail here.
I'm just going to run through a few of the fascinating possibilities in custom number formats.
Now, basically the first general rule, is that if we have one custom number format it applies to all numbers.
But we can have zones and so if for example, we take a look at this custom number format, I have two zones.
I say if it falls in the first zone, I want it blue with the dollar sign.
If it falls in the second zone, I want it red with no dollar sign.
And the way that this works if you specify two zones the positive numbers.
So five and zero get the first format and the negative numbers get the second format.
And you notice that for the negative numbers if you really wanted the negative sign to show up, you would have to put the negative number in because I left it out.
I forced this negative 2 to show up in red but as a positive number.
All right! Now, let's go on to the next example, if we have three zones, [ ctrl 1 ] to take a look at this with three zones.
What happens, is the first zone applies to the positive numbers, but not to zero.
And then to the negative numbers and then to zero.
So, we can actually put 0 in a different color.
Okay! Now, if we have four zones, the fourth zone is used for text.
So, here we have a situation, we're saying positive numbers show up in blue with a dollar sign, negative numbers show up in red, no dollar sign, no minus sign.
Green numbers, 0 should show up in green and then any text would just show up as yellow as text the add sign is the abbreviation for text.
Okay! So, you wonder what colors are available?
There are eight colors and of course this predates conditional formatting.
We could do all this with conditional formatting, today.
You'll see that we have Black, Cyan, Magenta, Blue, Green ,Red, Yellow.
You can also specify something as White.
Great way to hide it although, if we show it on a black background.
You can see that the white shows up.
Now, here's some interesting things if you have zones, separated by semicolons, and you leave a format out.
For example, let's say we didn't want to show any negative numbers.
Here, I would put in a custom number format of 0 for the positive and then leave the second zone blank.
Nothing for a negative and then a zero to handle the zero values.
You'll see that all of the negative numbers disappear.
Interesting or let's say we just want to hide everything.
Well, put in zones and put nothing at all.
So, I just put three semicolons, that says no matter what, positive, negative, zero, text.
I don't want to show anything.
Cool!
Now, here's an example, where this would actually come in handy.
We have positive, negative and zero and I went in and apply to custom number format that add some text depending on what we have.
So, we'll look at this custom number format.
I said if it's positive, we say your balance is zero.
If it's negative you have a credit balance of and then the number and if it's zero says your account is paid in full.
And so you'll see and let me scroll up, so we can see this at the same time.
This cell F18 actually has a five in it.
It has a five but if being displayed as your balance is five if I would type 17 here, your balance is 17.
If I would type a negative number, you have a credit balance of 100.
Now, in yesterday's podcast.
I talked about using conditions and conditions to add a whole new bit of flexibility.
Over here, I have a simple calculation, where I have the date that the payment was due.
Today's date and then calculate the number of days that have elapsed.
Well, rather than just report the number of days of elapsed, maybe I want to put some text in there to tell the people that their account is current, past, due or seriously past due.
So, let's take a look at this custom number format, again these are the same numbers.
It's the 20 to 35 to 55 and 72 but I'm completely displaying them in a different manner, using a custom number format that says we have a condition.
First condition in square brackets If it's less than 31, your account is current and notice I don't put any digits in there.
I'm not showing the number at all.
I don't have a zero to show the digits.
If it's less than 61, then your account is past due and then finally anything else in other words greater than 61.
I'm going to say your account is seriously past due.
So, you can have some fun with custom number formats to actually change numbers into text without creating if functions or anything like that.
Very interesting, it's obscure.
I find that, I hardly ever have a chance to use it.
It wasn't until that question came up in the seminar and I couldn't figure out a good way to do it in the pivot table that I resorted to this to show those 'yes' and 'no' values from yesterday's podcast.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
