Formatting Numbers in Excel

Excel offers a wide range of numeric formats, so you can choose the one that best suits your needs. Numeric formats are set on the number tab of the Format Cells dialog. To display this dialog, use any  of  these  methods:

• Press Ctrl+1.

• Right click a cell and choose Format Cells.

• Press Alt+O+E.

• From the menu, select Format, Cells.

Although Excel offers a wide variety of formats, the range is incomplete. Standard formats do not offer formats such as formatting negative numbers with parentheses, rounding a number to the thousands, adding characters to the number format (for example, characters that identify weight, like ton and pounds), adding characters like  the  euro  sign  in Excel 97, adding words and text to the format, colouring values according   to criteria, and more.

With Excel, you can create custom number formats that suit your needs and save them for repeated use.

This article teaches you the structure of number  formats  and  the  special symbols Excel uses to create them.

Where Custom Formats Are Saved

Display the Format Cells dialog. Select the Number tab, and under Category select Custom. You will see a list of additional formats. The custom formats that you  create are saved in  the  Custom dialog box in  the workbook in which you created and saved  them.

Symbols Used in Excel to Format Numbers

Get to know the special symbols you will use to create custom   formats.

0 (zero) symbol – displays a digit in a cell, including the digit   0.

Example: The format 0.00 displays the number 0.987 as a number with  two places after the decimal point, that is, 0.99 (without the 7). Places    are omitted after the number is rounded. Any omitted insignificant digits cause the number to be rounded. In  this case, 0.987 was  rounded to  0.99.

# (pound) sign – displays significant digits and does not display insignificant zeroes.

Example: A format with two places after the decimal point,  with  or without the digit 0:

The format code for 50 cents:

#.## The cell displays .5

#.#0 The cell displays .50

0.00 The cell displays 0.50

, (comma) – separates thousands

Example: With the format #,##0 the number 4543 is displayed as   4,543.

The comma has a second use in the number format. If you place the comma at the end of the digits, then the displayed number will be divided by 1000 for each comma.

#,##0, will display numbers in  thousands

#,##0,, will display numbers in millions

/ (forward slash) symbol – the division sign for displaying a   fraction.

* (asterisk) symbol – fills in empty characters, up to the beginning of the number.

Example:  The  number  4543  is  displayed  as  $  4,543  with  the  format

$ *#.##0. The $ sign is displayed on the left side of the cell, and the number is displayed on the right  side.

“TEXT” – if text characters are enclosed in quotation marks and followed by a number format, the text is displayed and the digits are   formatted.

Example: With the format “Balance” #,##0, the number 4543 is displayed as Balance 4,543. In the worksheet cell, you only  need  to  enter  the digits, not the text.

\<Any single character> – Use the backslash followed by a single text character to display that character. The following format will display a number in  millions: #,##0.0,,\M. Using this format, 123,789,456 would   be displayed as “123.8M”.

Excel will allow you to  omit the  backslash  when displaying a capital letter K. This format will display a number in thousands: #,##0,K. Using this format, 123,789,456 would be displayed  as 123,789K.

Special formats, rounding a number to the thousands, displaying in the thousands, displaying text and a number

The Four Sections of the Format

After you enter a number into a cell, Excel evaluates the number. The format of every number is divided into four sections. Excel  uses  the results of the evaluation to classify the number and display the correctly formatted number in the cell.

The four sections of the format are positive numbers, negative  numbers, zero values, and number+text.

The sections of the format are separated from one another by a semicolon (;).

Formatting a negative number with parentheses, replacing 0 with a dash

The format: #,##0 ;[RED](#,##0) ;-  ;

In the example, the format has three sections. Below are step-by- step instructions for formatting.

Formatting the positive section

In the Type box, enter #,##0

Press the Spacebar, and   type ; to indicate the end of the section.

Formatting the negative ection

• Type the color in brackets  [RED].

• Type (  (open parentheses), enter the format just as  you typed it  for  the positive number section, and type ) (close  parentheses).

• Type ; to indicate the end of the second  section.

• Formatting zero values

• Type the symbol – (minus), use the Spacebar to enter five spaces, and type ; to indicate the end of the third  section.

Click OK in the Formatting Cells dialog  box.