Show Menu

Excel Cell Number Formatting Cheat Sheet by

Excel cell formatting codes
development     excel     format

Number Format Codes

Number format codes are strings of symbols which define how Excel displays that data in your cells
Each number format code is made up of up to 4 blocks, separated by a semicolon (;)
Example
Sect 1; Sect 2; Sect 3; Sect 4
These sections correspond to different types of data as shown below
§ 1
§ 2
§ 3
§ 4
Positive values
Negative values
Zero
Text
The behavior for each section depends on how many sections are defined

Cell Section Format Behavior

Changing font color

You can change the color of the section by using a simple format code
[Color­ ­Name]
To use you simply set the color in the section you wish to color
Exam­ple
[Red]G­ene­ral­;[B­lue­]Ge­neral
Complete list of color codes
Black
Green
White
Blue
Magenta
Yellow
Cyan
Red
The Gene­ral message just tell Excel to represent the number as entered by the user. Be careful when using this for negative numbers, as you only get the value!

Adding Text

You can add text around numbers is a section in two ways
Single Charac­ters
For single characters simply type a backslash before the character
Eg. \@General
 
1234.567
@1234.567
 
-1234.567
-@1234.567
 
0
@0
 
Text
Text
Note: Text is not affected in this example
Text Strings
To add an entire string to a number surround the string in quotes (" ")
Eg. Genera­l" units"
 
1234.567
1234.567 units
 
-1234.567
-1234.567 units
 
0
0 units
 
Text
Text
Note: Again, text is not affected by this format code (since that section is not explicitly listed)
Eg. Genera­l" unit A";G­ene­ral­" unit B";G­ene­ral­" unit C";G­ene­ral­" unit D"
 
1234.567
1234.567 unit A
 
-1234.567
1234.567 unit B
 
0
0 unit C
 
Text
Text unit D
Note that there is no repres­ent­ation of the the fact that the negative value is negative. Our definition of the negative section did not include one.
Special Charac­ters
The following characters can be added to a format section without being escaped
$
+-
<=>
()
{}
^
'
:
/
~
&
!
(space)
 

Decimals places, Digits, and Commas

Symbol
Description
Summary
0
Zero
Forced Digit
?
Question Mark
Aligned Digit
#
Pound Sign
Un-Forced Digit
.
Period
Decimal Point
,
Comma
Thousands Separator
*
Asterisk
Repeating Character
_
Underscore
Space Modifier
Examples
Data
Result
Zero (0)
 
Format
0.00
 
0
0.00
 
0.123
0.12
 
1234
1234.00
Question Mark (?)
 
Format
0.??
 
0
0.  
 
0.123
0.12
 
1234
1234.  
Pound Sign (#)
 
Format
#.##
 
0
.
 
0.123
0.12
 
1234
1234.
Period (.)
The period in a number format code speifies the location of the decimal point
Comma (,)
 
Format
$??,???.00
 
1234.567
$ 1,234.57
 
-1234.567
-$ 1,234.57
 
0
$      .00
 
1234
$ 1,234.00
Asterisk (*)
 
Format
*=0.##
 
1234.567
===1234.57
 
-1234.567
-==1234.57
 
0
========0.
Unde­rscore (_)
 
Format
_(#.##_);(#.##)
 
1234.567
1234.57 
 
-1234.567
(1234.57)
 
0.123
.12 

Source

The content and examples for this cheat sheet are taken from this website:

http:/­/ww­w.e­xce­lta­cti­cs.c­om­/de­fin­iti­ve-­gui­de-­cus­tom­-nu­mbe­r-f­orm­ats­-excel/

I have condensed the inform­ation in order to fit it on a cheat sheet.
 

Fractions, Percen­tages, and Scientific Notation

Symbol
Description
Notation
/
Forward Slash
Fraction
%
Percent Sign
Percentage
E
Exponential
Scientific
Frac­tions
Fraction notation rounds values to the nearest possible fraction. Remember that fractions can be either proper, or improper.
Exam­ples
Data
Result
Reduced Fracti­ons
 
Format
# ???/???
 
0.23
23/100
 
0.25
1/4  
 
1
1      
 
1.25
1 1/4 ­ 
 
Format
# ??/??
 
0.23
3/13
Fixed Base Fracti­ons
It's possible to force Excel to use a specific denomi­nator by specifying it in the format code
 
Format
# ##/15
 
0.23
3/15
 
0.25
4/15
 
1.25
1 4/15
Perc­entages (%)
 
Format
#%
 
0.235
24%
 
0.25
24%
 
1
100%
 
1.25
125%
 
0
%
You can also specify fractional percen­tages
 
Format
# #/#%
 
0.235
23 1/2%
 
0.25
25%
You can specify the number of digits with decimal places
 
Format
#.0%
 
0.235
23.5%
 
0.25
25.0%
 
1
100.0%
 
1.25
125.0%
 
0
.0%
Scie­ntific Notation
Excel uses E+ notation for expone­ntial values. The format code in front of the E+ describes the relevant digits, and another format code on the other side of the E+ describes the handling of the exponent.
 
Format
#E+#
 
0.000000000123
1E-10
 
456000000000
5E+11
 
1
1E+0
 
1.25
1E+0
 
0
0E+0
 
Format
0.00E+00
 
0.000000000123
1.23E+10
 
456000000000
4.56E+11
 
1
1.00E+00
 
1.25
1.25E+00
 
0
0.00E+00

Download the Excel Cell Number Formatting Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/nqramjets_excel-cell-number-formatting.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          Regular Expressions Cheat Sheet
          JavaScript Cheat Sheet
          python string formatting Cheat Sheet

          More Cheat Sheets by nqramjets

          XML 1.0 Cheat Sheet