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, 5 and 0 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, control+1, to take a look at look at this with three zones.
What happens is the first zone applies to the positive numbers, but not to 0 and then to the negative numbers and then to 0.
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 okay 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's 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 let me scroll up, so we can see this at the same time.
This cell F18 actually has a 5 in it.
It has a 5, but it's being displayed as your balance is 5.
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.
And 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 0 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.
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.
Well, I wanna 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, 5 and 0 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, control+1, to take a look at look at this with three zones.
What happens is the first zone applies to the positive numbers, but not to 0 and then to the negative numbers and then to 0.
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 okay 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's 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 let me scroll up, so we can see this at the same time.
This cell F18 actually has a 5 in it.
It has a 5, but it's being displayed as your balance is 5.
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.
And 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 0 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.
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.
Well, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
