Pinterest Pixel

Custom Number Formats in Excel

A custom number format in Excel can have up to four different sections in the following order:... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

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

Custom Number Formats in Excel

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

Custom Number Formats in Excel

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

See also  Turn Text To Values With Paste Special Values

Custom Number Formats in Excel

Try out the 4 scenarios and see them change!

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

Custom Number Formats in Excel

Custom Number Formats in Excel | MyExcelOnline
DOWNLOAD WORKBOOK

CODE OUTPUT HOW IT APPEARS
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%

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!