Building on yesterdays podcast, Episode 554 shows how to make full use of the custom number formatting zones to add specific words to a balance due column.
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.
Now in yesterday's netcast I quickly showed how to use a custom number format using the zones, I use zero semicolon to show the positive numbers and zero and to hide the negative numbers, let's take another look at that custom number format because it's pretty interesting I have a column here of amounts due, a balance due column, so for example in this first cell the person owes us 44$ and the next row they paid in full in the next row they've actually overpaid we owe them 77$, well we can adjust the way that that's displayed without using any concatenation or anything like that we can actually set up a custom number format, let me go to another worksheet where I've already set this up I'm going to go to format cells that's CTRL 1, and we're going to take a look at the number tab and basically I used all three zones, I said if the value is positive I want to ask the customer to send in the money, so in quotes please remit a space close quotes, and then my custom number format of dollar signs 0.00, then a semicolon.
Now I'm working on what happens if it's negative, if it's negative we actually owe them money so I say you have a credit balance of and you'll notice that I don't put a negative sign in the custom number format I just put dollar signs 0.00 this will take that negative number and not display the negative sign but instead let them know that they have a credit balance as semicolon and now I'm dealing with what happens if it's zero at zero then they're painful we don't know them they don't know less we can just replace that zero with paid in full and I use no number codes there, and when I click OK, you'll see that for the value that was zero we actually still see zero in the formula bar but what we're displaying and Excel are the words paid in full here this is a positive 44, so we asked them to please remit $44, here this was a negative 77 and we say you have a credit balance of $77, it's pretty wild did that custom number format it allows three different zones and if you put your text in quotes we were able to actually modify the number very well it all continues to work I can even come down here and sum up these numbers because they're all actually stored as numbers but displayed is txt we see that we actually have a positive $37 this owed to us from all of those numbers, amazing use of the custom number format using the semicolons to separate the number format into three zones.
Hey thanks for setting by; we'll see you next time for another netcast from MrExcel.
Now in yesterday's netcast I quickly showed how to use a custom number format using the zones, I use zero semicolon to show the positive numbers and zero and to hide the negative numbers, let's take another look at that custom number format because it's pretty interesting I have a column here of amounts due, a balance due column, so for example in this first cell the person owes us 44$ and the next row they paid in full in the next row they've actually overpaid we owe them 77$, well we can adjust the way that that's displayed without using any concatenation or anything like that we can actually set up a custom number format, let me go to another worksheet where I've already set this up I'm going to go to format cells that's CTRL 1, and we're going to take a look at the number tab and basically I used all three zones, I said if the value is positive I want to ask the customer to send in the money, so in quotes please remit a space close quotes, and then my custom number format of dollar signs 0.00, then a semicolon.
Now I'm working on what happens if it's negative, if it's negative we actually owe them money so I say you have a credit balance of and you'll notice that I don't put a negative sign in the custom number format I just put dollar signs 0.00 this will take that negative number and not display the negative sign but instead let them know that they have a credit balance as semicolon and now I'm dealing with what happens if it's zero at zero then they're painful we don't know them they don't know less we can just replace that zero with paid in full and I use no number codes there, and when I click OK, you'll see that for the value that was zero we actually still see zero in the formula bar but what we're displaying and Excel are the words paid in full here this is a positive 44, so we asked them to please remit $44, here this was a negative 77 and we say you have a credit balance of $77, it's pretty wild did that custom number format it allows three different zones and if you put your text in quotes we were able to actually modify the number very well it all continues to work I can even come down here and sum up these numbers because they're all actually stored as numbers but displayed is txt we see that we actually have a positive $37 this owed to us from all of those numbers, amazing use of the custom number format using the semicolons to separate the number format into three zones.
Hey thanks for setting by; we'll see you next time for another netcast from MrExcel.
