Show Menu
Cheatography
  1. Home >
  2. Software >

Excel Cell Number Formatting Cheat Sheet by

Excel cell formatting codes

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
Example
[Red]G­ene­ral­;[B­lue­]Ge­neral
Complete list of color codes
Black
Green
White
Blue
Magenta
Yellow
Cyan
Red
The General 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 Characters
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 Characters
The following characters can be added to a format section without being escaped
$
+-
<=>
()
{}
^
'
:
/
~
&
!
(space)
 

Decimals places, Digits, and Commas

Symbol
Descri­ption
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.
Underscore (_)
 
Format
_(#.##­_);­(#.##)
  
1234.567
 
1234.5­7 
  
-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
Descri­ption
Notation
 
/
Forward Slash
Fraction
 
%
Percent Sign
Percentage
 
E
Expone­ntial
Scientific
Fractions
Fraction notation rounds values to the nearest possible fraction. Remember that fractions can be either proper, or improper.
Examples
 
Data
 
Result
Reduced Fractions
 
Format
# ???/???
  
0.23
 
23/100
  
0.25
 
1/4 ­ 
  
1
 
1      
  
1.25
 
1   1/4 ­ 
 
Format
# ??/??
  
0.23
 
3/13
Fixed Base Fractions
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
Percen­tages (%)
 
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%
Scientific 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.0000­000­00123
 
1E-10
  
456000­000000
 
5E+11
  
1
 
1E+0
  
1.25
 
1E+0
  
0
 
0E+0
 
Format
0.00E+00
  
0.0000­000­00123
 
1.23E+10
  
456000­000000
 
4.56E+11
  
1
 
1.00E+00
  
1.25
 
1.25E+00
  
0
 
0.00E+00
       
 

Metadata

  • Languages:
  • Published: 18th November, 2015
  • Last Updated: 12th May, 2016
  • Rated: 5 out of 5 stars based on 5 ratings

Favourited By

Glennith MoakDesigns dockawash

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
          Python Cheat Sheet
          python string formatting Cheat Sheet

          More Cheat Sheets by nqramjets

          XML 1.0 Cheat Sheet