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.


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


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation. This is a new calculation in Excel 2010 and onwards. This option will immediately calculate the percentages f...
Excel Table: Styles There are lots of different Table Styles that you can choose from to spice up your Excel Table.  You can also customize your own style if you don't like any of the default Excel styles. To access the Table Styles, you simply need to click inside your Table and then the Table T...
Excel Hyperlinks: Buttons Excel´s hyperlink capability is amazing but many people don't use it as they don't know its full capabilities. With a hyperlink you can link an object/text to open an existing file on your desktop, go to a website, open up an email to a specific contact or go to a cell within ...
Turn Text To Values With Paste Special Values Many times you would have received data from your IT system which is formatted wrong!  Well a gazillion times if you work in a mid sized firm 🙂 When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value....