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

Rank Smallest to Largest 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 SMALLEST TO LARGEST calculation. This option will immediately calculate the rankings (1 being the smallest value) for your values, allowin...
Data Bars, Color Scales & Icon Sets Conditional Formatting has improved in Excel 2010 with the introduction of Data Bars, Color Scales & Icon Sets. Data Bars: Includes graphic bars in a cell, proportional to the cell’s value - Good for Financial Analysis Color Scales: Includes a background color, proporti...
Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values. The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry,...
Pivot Table Conditional Formatting Inserting a Pivot Table is very easy to do in Microsoft Excel: Inserting a Pivot Table Adding some Conditional Formats to the Pivot Table allows a user to highlight key data in a split second. See how easy it is to add some color to your analysis. DOWNLOAD WORKBOOK ...