Look Who it Was Random video

Excel - Deep Dive in Excel Custom Number Formats - Episode 724

329 views · Published 13 February 2009 · 5:34 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Deep Dive in Excel Custom Number Formats.
Welcome back to the MrExcel netcast! In this video, I will be discussing custom number formats in Excel. Yesterday, I shared an old trick with custom number formats and today, I want to dive deeper into this topic. If you go to Excel help and search for "create or delete a custom number format", you will find all the details you need. But in this video, I will be showing you some fascinating possibilities with custom number formats.

The general rule with custom number formats is that if you have one format, it will apply to all numbers. However, you can have multiple zones within a custom number format. For example, in the custom number format I have here, I have two zones. The first zone will display positive numbers in blue with a dollar sign, while the second zone will display negative numbers in red without a dollar sign. This means that if a number falls within the first zone, it will be formatted accordingly, and if it falls within the second zone, it will be formatted differently. This is a great way to differentiate between positive and negative numbers.

Moving on to the next example, if we have three zones in a custom number format, the first zone will apply to positive numbers, the second zone will apply to negative numbers, and the third zone will apply to zero. This means that we can have different formatting for zero values as well. Similarly, if we have four zones, the fourth zone will be used for text. This is a great way to format numbers and text in the same cell without using any formulas.

You may be wondering what colors are available for custom number formats. Well, there are eight colors to choose from, including black, cyan, magenta, blue, green, red, yellow, and white. You can also use the add sign as an abbreviation for text. Another interesting feature of custom number formats is that you can leave a format out if you want to hide certain numbers. For example, if you don't want to show any negative numbers, you can leave the second zone blank. This will make all negative numbers disappear. You can also use three semicolons to hide all numbers and text in a cell.

Now, let's look at an example where custom number formats can come in handy. In this scenario, I have used a custom number format to display different text depending on the value in the cell. For example, if the number is positive, it will display "Your balance is" followed by the number. If the number is negative, it will display "You have a credit balance of" followed by the number. And if the number is zero, it will display "Your account is paid in full". This is a great way to add some context to your numbers without using any IF functions.

In yesterday's podcast, I talked about using conditions to add flexibility to your data. In this example, I have used a custom number format to display different text based on the number of days that have elapsed. For example, if the number is less than 31, it will display "Your account is current". If the number is between 31 and 60, it will display "Your account is past due". And if the number is greater than 60, it will display "Your account is seriously past due". This is a great way to visually represent data without using any complex formulas.

I hope you found this video helpful and learned something new about custom number formats in Excel. I hardly ever have a chance to use this feature, but it can come in handy in certain situations. Thank you for watching and don't forget to subscribe to our channel for more Excel tips and tricks. See you next time for another netcast from MrExcel!

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
(00:15) Custom Number Format Trick
(00:25) Excel Help for Custom Number Formats
(00:35) Two Zones for Custom Number Formats
(00:49) Three Zones for Custom Number Formats
(01:41) Four Zones for Custom Number Formats
(02:00) Available Colors for Custom Number Formats
(02:22) Using Semicolons in Custom Number Formats
(02:59) Example of Using Semicolons in Custom Number Formats
(03:09) Adding Text to Custom Number Formats
(03:30) Example of Adding Text to Custom Number Formats
(03:59) Using Conditions in Custom Number Formats
(04:26) Example of Using Conditions in Custom Number Formats
(05:00) 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

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

More from this channel