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.


GeneralGeneral format display123456
#Displays significant digits123
#.00%Displays percentage64.89%
$ – + / ( ) :Displays this character-$1234567890
\Displays the next character following the \#1234567890
“text”Displays the text in between the quotations1234567890 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 Format1.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

Rank Largest to Smallest With Excel Pivot Tables 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 RANK LARGEST TO SMALLEST calculation.This option will immediately calculate the rankings (1 being the LARGEST value) for your values, allowing you to pi...
Fix Excel Hyperlinks to a Named Range Hyperlinks in Excel must be one of the funkiest features that I love playing around with!They allow you to create interactive buttons within Excel (without the need to create a Macro) and you can make them take you to any cell or range within your Excel worksheet.One shor...
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...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list?Do you want to make them uniform by adding leading zeros to them?Well, it's a pain to add zeros in front of them one by one!Thankfully, Excel allows you to add leading zeros with one singl...