Look Who it Was Random video

Excel - Custom Number Formatting with Zones for Positive, Negative, Zero - Episode 554

199 views · Published 20 July 2009 · 2:55 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Custom Number Formatting with Zones for Positive, Negative, Zero.

Welcome back to another MrExcel netcast! In yesterday's video, I briefly demonstrated how to use custom number formatting with zones. Today, we're going to take a closer look at this feature and see how it can be used to display data in a more visually appealing and informative way. Let's dive in!

In this example, I have a column of amounts due and I want to display them in a way that is easy to understand. For positive numbers, I want to ask the customer to send in the money. For negative numbers, I want to let them know that they have a credit balance. And for zero values, I want to indicate that the payment has been made in full. With the use of custom number formatting and zones, we can achieve this without any complicated formulas or concatenation.

To set up this custom number format, I use all three zones and separate them with semicolons. In the first zone, I use the text "Please remit" followed by a space and then the custom number format of dollar signs and two decimal places. In the second zone, for negative numbers, I use the text "You have a credit balance" followed by the same custom number format without the negative sign. And in the third zone, for zero values, I simply use the text "Paid in full". This allows us to display the data in a clear and concise manner.

But the best part is that even though the data is displayed as text, it is still stored as numbers in the formula bar. This means we can still perform calculations on these values, as shown by the sum of the column. This is just one example of how the custom number format with zones can be used to enhance the visual representation of data in Excel. I hope you found this tip useful and stay tuned for more netcasts from MrExcel. Thanks for watching!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction to custom number format
(00:18) Using zero semicolon to show positive and negative numbers
(00:32) Applying custom number format to a column of amounts due
(00:48) Setting up custom number format using all three zones
(01:13) Dealing with negative numbers and displaying credit balance
(01:40) Replacing zero with "paid in full" in custom number format
(02:15) Summing up numbers with custom number format
(02:35) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Custom number format using zones
Displaying credit balance without negative sign
Displaying text in Excel
Format cells shortcut (CTRL 1)
Modifying numbers with custom number format
Replacing zero with paid in full
Setting up a custom number format
Show positive and hide negative numbers
Storing numbers as text in Excel
Summing numbers with custom number format
Using dollar signs in custom number format
Using semicolons to separate number format zones

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152523/



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!

More from this channel