add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
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, coloring 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 chapter teaches you the structure of number formats and the special symbols Excel uses to create number formats.
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.
You can create and save custom number formats and use them in additional workbooks by saving the number format as a style in a workbook template.
See Chapter 5, Styles.
See the Template section of Chapter 13, Customizing Excel.
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.98 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
#.#0The cell displays .50
0.00The 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 figure below shows examples of special formats. The format code is in column D, and the explanation is in column A.
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
1. Press the Spacebar, and type ; to indicate the end of the section.
Formatting the negative section
2. Type the color in brackets [RED].
3. Type ( (open parentheses), enter the format just as you typed it for the positive number section, and type ) (close parentheses).
4. Type ; to indicate the end of the second section.
Formatting zero values
5. Type the symbol – (minus), use the Spacebar to enter five spaces, and type ; to indicate the end of the third section.
6. Click OK in the Formatting Cells dialog box.
Explanation
On the left hand side of the format (positive number), you left a space. This means that the positive number is entered into the cell with a space on the right side, since the format code for the negative number includes parentheses (see Cells A1 and A2 in the figure).
In the third section of the number format, display of zero values, the resulting 0 in the cell is replaced by a dash. The five spaces you entered position the dash in line with the hundreds place in the positive or negative number (see cell A3 in the figure).
Tip
Is the data in the cell formatted as a date instead of as a number?
Press Ctrl+Shift+~.
Special Formats – Examples
The examples in the figure below illustrate format codes for three sections of the number format.
Adding special symbols to the number format
You can add special symbols to the number format, such as symbols that are not included in the currency or accounting formats.
The CHAR formula displays the symbol. To add a symbol to the new format you create, copy the symbol to the Type box.
Example: Create a format with the € symbol (euro)
(The symbol does not appear in the format list in Excel 97).
1. Enter the formula =CHAR(128) in the cell.
2. Press F2 and then F9 to delete the formula and leave the value in the cell.
3. In the Formula bar, select the € symbol, and press Ctrl+C (copy).
4. Select another cell. Press Ctrl+1.
5. Select the Number tab, and choose Custom.
6. In the Type box, press Ctrl+V.
7. Continue by typing the format code #,##0.
8. Press Enter.
The result: €#,##0.
Tip – Insert the Euro sign into AutoCorrect
Refer to Chapter 2, Text.
Returning characters specified by numbers
To display all symbols, letters and numbers, create a series of ascending numbers from 33 to 250 in Column A beginning with Cell A1. In Cell B1, type the formula CHAR with a reference to Cell A1. Copy the formula to all the cells in Column B, alongside the series in Column A.
Formatting Numbers According to Criteria
There are two ways to format numbers according to criteria:
© Custom formatting for a number with criteria
© Conditional formatting
Using custom formats for numbers with criteria
Your options for coloring a number are not limited to displaying negative numbers red. You can color positive and/or negative numbers in any color you wish (do not get excited – the number of colors is limited to eight, and most are difficult to read). Do this by adding the name of the color to the number format in brackets.
Example: [BLUE] #,##0 ;[RED](#,##0)
A positive number is displayed in blue; a negative number is displayed in red; and 0 is displayed in blue (since there is no third section, 0 receives the format of the positive number by default).
Add a condition to the formatting, and have each section be displayed in a different color:
[BLUE] [>5000]#,##0 ;[RED](#,##0); #,##0
Explanation:
A positive number that is greater than 5,000 will be displayed in blue; a negative number will be displayed in red; and positive numbers from 0 to 4,999 in black (the default is applied to the third section of the number format).
Other valid colors are [BLACK], [CYAN], [MAGENTA], [WHITE], [GREEN], and [YELLOW].
Conditional Formatting
With Conditional formatting, you can add up to three criteria. The number or text is evaluated according to these criteria, before the custom format for a conditional number is applied.
Select a cell or cells in the sheet. From the Format menu, select Conditional Formatting.
The figure illustrates three different format conditions, according to the calculated value in the cell(s) you selected.
Totaling Rounded Numbers
How Excel performs calculations
Excel does not take the number format into account when performing mathematical calculations. For example, a cell contains a number with 10 digits after the decimal. The chosen format is a whole number format, with no places after the decimal. When performing the calculation, Excel relates to the entire number, without considering the formatting, for a calculation of up to 15 significant digits.
When the amounts displayed in the cells and the amounts that Excel uses for calculations are different, there can be difference in the number totals.
Example:
In Cells B3 to B7, the numbers were rounded according to their number formats. The total in Cell B8 is not equal to total of the displayed numbers, which is 16.
Permanent solution (no way back)
Choose Tools, Options.
Select the Calculation tab, and select the Precision as displayed option.
The result: all the places after the decimal are cut in the cells. The numbers displayed in the cells are whole numbers, and the total is the sum of these whole numbers.
The disadvantage – there is no way to undo this, as the numbers have been permanently cut.
Flexible solution, array formula
Use an array formula to total rounded numbers. See the Array Formula section of Chapter 7, Formulas.
1. In Cell C8 (see figure below), type =ROUND. Press Ctrl+A. In the first Number box of the ROUND formula, enter the range C3:C7. In the Num_digits box, type 0, which rounds the number to the nearest whole number.
2. After the = symbol, type SUM. Then, type ( (open parentheses), press End, and type ) (close parentheses). Press Ctrl+Shift+Enter at the same time, creating an array formula.
If the formula returns a #VALUE error, press F2, and then press Ctrl+Shift+Enter.
Enter this array formula in every intermediate total. This formula means you do not have to use many ROUND formulas, and the result assures that the totals match the precision that you used in the numeric formats.