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.

 

STEP 1:  Copy the custom number format

STEP 2:  Press Ctrl+1 to open the Format Cells dialog. Or you can right click on the cell and select Format Cells.

STEP 3:  Choose Custom and Paste the Number Format. Then Press OK.

Try out the 4 scenarios and see them change!

  • Positive – 100
  • Negative – -200
  • Zero – 0
  • Text – Excel ROCKS!


DOWNLOAD WORKBOOK

CODEOUTPUTHOW IT APPEARS
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%

 

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn