MrExcel's Learn Excel #554 - Formatting Zones

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 20, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top