Dermot sends in todays question; a spreadsheet is showing zeroes as a dash, and positive numbers with a plus sign! It turns out that the custom number format has four zones, where you can specify different formatting for positive, negative, zero, and text values. Episode 521 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Dermot. Dermot said. Hey, someone sent me a spreadsheet and when I looked through the numbers in column A, I'm noticing a couple of things first of all the negative numbers show up negative.
I understand that but the positive numbers are showing up with a plus sign before it and the zeros are showing up with just a dash. It's not showing the zero at all.
Derman said how can they do this so I have the exact same numbers over here in column C, without any custom number format.
I'm going to go into Format cells and the fast way to Format cells is Ctrl 1.
Now on the Number tab I know that lots of times people go in and choose Number or Currency or something like that.
I want to go to the custom tab and maybe you've experimented with typing in some custom number format here.
For example, the one that I like is #,##0,K that'll show numbers in thousands.
But it turns out that this custom number format actually has up to four zones.
And each zone can be separated by a semicolon.
So the way that it works is if you enter three zones.
For example, the first zone is what format to use for a positive number.
The second zone is what format to use for a negative number and the third Zone is what format to use for 0.
Now we're not going to talk about the fourth zone today.
That's what happens if you have text. By the way if you put in just two zones.
First zone is positive and 0, seconds zone is negative.
If you put in just one zone which is what we do most of the time.
That's used for all numbers. So we have to think about this the first zone is for positive numbers.
We can actually put in a +0; to go on to the second zone.
Now here we want to show a negative, so we use -0; And then finally what to do if it's a zero.
If it's a zero, we don't want to show any digits at all.
We just want to put a dash in there. Will click ok and you'll see that our numbers over here in column C are now formatted exactly the way that Dermot described.
Negative numbers show up negative, positive numbers show up with the + sign and our zeroes down here in C10 shows up with just a dash.
So again that's Format cells and actually using the three different zones of the custom number format in order to specify a different format for positive negative and zero.
Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
Today we have a question sent in by Dermot. Dermot said. Hey, someone sent me a spreadsheet and when I looked through the numbers in column A, I'm noticing a couple of things first of all the negative numbers show up negative.
I understand that but the positive numbers are showing up with a plus sign before it and the zeros are showing up with just a dash. It's not showing the zero at all.
Derman said how can they do this so I have the exact same numbers over here in column C, without any custom number format.
I'm going to go into Format cells and the fast way to Format cells is Ctrl 1.
Now on the Number tab I know that lots of times people go in and choose Number or Currency or something like that.
I want to go to the custom tab and maybe you've experimented with typing in some custom number format here.
For example, the one that I like is #,##0,K that'll show numbers in thousands.
But it turns out that this custom number format actually has up to four zones.
And each zone can be separated by a semicolon.
So the way that it works is if you enter three zones.
For example, the first zone is what format to use for a positive number.
The second zone is what format to use for a negative number and the third Zone is what format to use for 0.
Now we're not going to talk about the fourth zone today.
That's what happens if you have text. By the way if you put in just two zones.
First zone is positive and 0, seconds zone is negative.
If you put in just one zone which is what we do most of the time.
That's used for all numbers. So we have to think about this the first zone is for positive numbers.
We can actually put in a +0; to go on to the second zone.
Now here we want to show a negative, so we use -0; And then finally what to do if it's a zero.
If it's a zero, we don't want to show any digits at all.
We just want to put a dash in there. Will click ok and you'll see that our numbers over here in column C are now formatted exactly the way that Dermot described.
Negative numbers show up negative, positive numbers show up with the + sign and our zeroes down here in C10 shows up with just a dash.
So again that's Format cells and actually using the three different zones of the custom number format in order to specify a different format for positive negative and zero.
Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
