MS Excel's Custom Number Format

Introduction

Microsoft Excel, for all its problems, is a great way to manipulate data. In particular, it provides a powerful way to format & display underlying data. Building this as a tutorial and tool to keep track of Excel's powerful ability to format numerical data; using its Custom Number Format capability.

Overview of Custom Number Format

The data in any cell can be formatted to look different than the underlying data itself. This is done by selecting the “Format Cells” option from the right-click menu for a cell. Depending on the type of data there are some standard formatting options provided by Excel. If the data is number, however, there is a whole level of custom formatting capability available. This is accessed by “Right-click > Format Cells > Number > Custom”.

A custom format is a basically series of awkward looking characters that code the way numbers are displayed. This tutorial will try to make sense of this code. Note that changing this custom code only changes the display, but does not change the underlying data. Double clicking a cell or hitting F2, allows you see the actual data itself.

Custom Number Format code

The format string

A custom number format consists of 1 to 4 segments separated by semi-colons. Each segment formats a particular type of data - the first formats positive numbers, the second formats negative numbers, the third formats zeros and the fourth formats text. See the image above (from Microsoft) that illustrates.

It is not necessary to have all four segments. If you only define the first segment, negative and zeros are displayed as-is; without any format. Now lets get started with creating these formatting strings.

Formatting After the Decimal Point

To manage the display of digits after a decimal, one needs to be able to round the value and pad the resulting number with either 0s or spaces.

Code What it does
. Matches the decimal point in the number. The code to the right of this character is used to manipulate the display of data after the decimal.
# Rounds the number, if it is longer than the number of characters used. Does not pad the result if there are fewer decimal digits.
0 Rounds the number if it is longer, but pads it with zeros if the decimal length is shorter.
? Rounds the number if it is longer, but pads it with spaces if the decimal length is shorter.
?/? The / denotes the separator between numerator and denominator when displayed as a fraction. The ? marks denote the number of characters in the numerators and denominators.

Here are a few examples to get you started. Of course the _ in the display represents a single space.

Code The number The display
.## 12.3456 12.35
  12.3333 12.33
.00 12.3456 12.35
  12.3 12.30
.?? 12.3456 12.35
  12.3 12.3_
?/? 12.3456 37/3
?/?? 12.3456 1000/81
# ?/? 12.3456 12 1/3

Formatting Thousands and Scientific Notation

For digits to the left of the decimal point, there are no rules of significant digits. However, it is possible to roll the number up by the thousands. This is very useful, for example, when you are doing budgets and need to roll up numbers in the thousands. Two key codes to keep in mind.

Code What it does
, Matches thousands. Using a single comma accesses the first thousand, and a second comma accesses the million and so on.
E+ or E- Displays the number in the exponential notation. The number of zeros or hash signs to the right of E determines the number of digits in the exponent. One can also use the + or - sign next to the letter E to force the sign of the exponent to be displayed always. The small letter e also works.

If you use a comma to roll-up by the thousands, you can use the decimal, to access the hundred digits in combination with the thousands. Some examples below:

Code The number The display
#, 123456 123
  12345678 12346
#,.## 12345678 12345.68
$ #,.## "MM" 345776 $ 345.78 MM
#e+# 12345678 1E+7

Other codes

In addition to formatting the numbers to the left and right of a decimal, it is possible to add additional codes to made the data more readable.

Code What it does
"" Used to include any text as part of the formatting of the cell. There are a few characters that do not need to be enclosed in quotes, as shown below.
$ Inserts the "$"dollar sign
£ Inserts the "£" pound sign. To enter the number, press alt key, enter the number 0163, and release alt. To enter "¢", enter alt+0162. To enter "¥", enter alt+0165.
% Formats the data as a percentage (multiplies by 100 and adds the "%" sign).
_ Formats the data as a percentage (multiplies by 100 and adds the "%" sign).
* Repeats a single charater, (the one following the asterix) for the width of the cell.
[Red] At the beginning of a code segment, this makes the number formatted in the Red color. Other codes that work include [Black], [Blue], [Cyan], [Green], [Magenta], [White], [Yellow]

Bringing it all together

The format string for any number can be repeated with the semi colon to cover negatives and zeros as well. Of course the fourth option covers text, but that is out of scope fo this tutorial. A few useful examples are shown below:

The first example rolls up numbers by thousands and adds the $k symbol. The negative numbers are shown in red with brackets and zeros are represented by dashes.

Code The number The display
$ #, k;[Red] $ (#,) k;-- 123456 $ 123 k
  -987654 $ (988) k
  0 --

Another example. Breaks the number down into dollars and cents.

Code The number The display
#,### "dollars &" .00 "cents";[Blue] #,### "dollars &" .00 "cents"; "No Money" 123456 123,456 dollars & .00 cents
  -987654.21 987,654 dollars & .21 cents
  0 No Money

More tools.

Code The result
#;-#;;@ This code suppresses all zeros, but displaying all other numbers and text as is.
+#;-#;0;@ Forces the sign on positive and negative values, leaving zeros and text unchanged.
;;;@ Suppresses all numbers, and displays only text.
#.0* +;#.0* -;;@ All numbers are displayed with one significant decimal, with the plus and minus forced to display, at the right edge of the cell.
(000)000-0000;(000)000-0000;; Numbers are displayed as a telephone number (US telephone number format). Negatives ignored.

There you go, a quick and easy way to format data for presentation within MS Excel.