A custom number format in Excel can have up to four different sections in the following order:

Positive format; Negative format; Zero format; Text format

 

You can specify different format codes for each section as long as they are separated by a semicolon.

So you can display a positive number in black, a negative number in red, a zero in green and any text in blue.  See the gif below to see how this can be achieved.

The following table displays the different custom codes that you can enter in the Format Cells dialogue box and how the values will appear.  You can download the Excel workbook below and press CTRL+1 in each cell to see the custom format entered.

DOWNLOAD WORKBOOK

CODE OUTPUT HOW IT APPEARS
General General format display 123456
# Displays significant digits 123
#.00% Displays percentage 64.89%
$ – + / ( ) : Displays this character -$1234567890
\ Displays the next character following the \ #1234567890
“text” Displays the text in between the quotations 1234567890 units
[Color n] Displays the color in the Excel color pallete                           (from 0 to 56) 1234567890
[condition value] Custom condition eg [If it meets this condition]               True Format; False Format 1.00%

Number Formats - Custom

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin