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

Text To Columns: Dates Whenever you download data from an external ERP system like Oracle, SAP, etc, you can have data that is not formatted the way you and Excel likes.Sometimes "Date" values are downloaded as "Text", so you cannot sort in the periodic date format.No worries!  Text to Columns ...
Excel Table: Row Calculations One of the most powerful feature of an Excel Table is the use of formulas to calculate its Column contents.  To activate this you need to click in your Table and go to the Table Tools tab in the Ribbon.  From there you simply need to activate the Total Rows check box.This wil...
Find Blank Cells In Excel With A Color In Excel you can have a data set that comes from an external source which isn't always formatted to your liking.One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to anal...
Excel Table Slicers Slicers were introduced with Pivot Tables in Excel 2010 and they allowed us to select items to filter with beautiful interactive buttons.You can see the power of Slicers and the different ways you can format them in these posts: https://www.myexcelonline.com/category/pivot-ta...