Look Who it Was Random video

Excel - Mastering Custom Number Formats in Excel: Controlling Colors, Text, and More! - Episode 1063

856 views · Published 22 July 2009 · 5:10 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Custom Number Formats in Excel: Controlling Colors, Text, and More!

Welcome back to another episode of the MrExcel netcast. I'm Bill Jelen and today's tip comes from Crystal, a Microsoft MVP for Access. She runs a YouTube channel called LearnAccessbyCrystal where you can find helpful tutorials on using Access. Crystal suggested that we talk about the different formatting codes in Excel, so let's dive in.

First, let's take a look at creating a custom number format with a single zone. We can do this by selecting the cells we want to format, pressing CTRL 1, and choosing a format such as currency with two decimal places. However, did you know that it's possible to have custom number formats with two, three, or even four zones? Let's explore this further.

To create a custom number format with two zones, we simply separate the two zones with a semicolon. The first zone is used for positive and zero numbers, while the second zone is used for negative numbers. But did you know that you can also change the placement of the minus sign? By putting the minus sign after the second zone, it will appear after the number instead of before it.

But it doesn't stop there. You can also use any text or symbols in your custom number format. For example, if you want to indicate that a number is a credit, you can put "CR" in quotes in the second zone. And did you know that you can have up to four zones in a custom number format? The fourth zone is used for text, so you can control how text is displayed in your cells.

But wait, there's more! You can also use this trick to control the color of your numbers. By using square brackets and the word "green" or "red" followed by a zero, you can make your positive numbers appear in green and your negative numbers appear in red. And if you want to hide certain numbers, you can simply leave that zone blank.

So next time you need to have more control over the way your numbers are displayed, remember that you can use multiple zones in your custom number format. Thank you to Crystal for this great suggestion. Don't forget to check out her YouTube channel for more helpful tips and tutorials. And as always, we'll 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 to today's tip on formatting codes in Excel
(00:22) LearnAccessbyCrystal YouTube channel recommendation
(00:32) Creating a custom number format with two zones
(01:19) Adding a trailing minus sign or custom text to negative numbers
(01:53) Using three zones for positive, negative, and zero numbers
(02:33) Using a fourth zone to control text display
(03:05) Preventing text from appearing in the custom format
(03:29) Hiding numbers and text with multiple semicolons
(03:39) Controlling colors in the custom format
(04:47) 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:
Controlling colors in custom number formats
Controlling text in custom number formats
Creating custom number formats in Excel
Custom number formats in Excel
Formatting codes in Excel
Hiding text in custom number formats
LearnAccessbyCrystal
Number formatting with zones in Excel
Positive and negative numbers in Excel
Trailing minus sign in Excel
Using multiple zones in custom number formats
Using quotes in custom number formats

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



LearnAccessByCrystal at YouTube asked for a podcast with information about the zones in a custom number format. In Episode 1063, an explanation of the four zones in a custom number format.

More from this channel