Training Program No related posts.
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.