Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 32 Create Custom Number Formats

Excel comes with built-in number formats, but
sometimes you need to use a number format that is not built into
Excel. Using the hacks in this section, you can
create number formats that you can customize to meet your
needs.

Before you try these hacks, it helps if
you understand how Excel sees cell formats. Excel sees a
cell's format as having the following four sections
(from left to right): Positive Numbers, Negative Numbers,
Zero Values, and Text Values. Each section is separated by
a semicolon (;).

When you create a custom number format, you do not have to specify
all four sections. In other words, if you include only two sections,
the first section will be used for both positive numbers and zero
values, while the second section will be used for negative numbers.
If you include only one section, all number types will use that one
format. Text is affected by custom formats only when you use all four
sections; the text will use the last section.

Don't interpret the word number to mean custom formats applying to numeric data only. Number formats apply to text as well.

The custom number format shown in Figure 2-18 is
Excel's standard currency format, which
shows negative currencies in red. We
modified it by adding a separate format for zero values and another
one for text. If you enter a
positive number as a currency value, Excel will format it
automatically so that it includes a comma for the thousands
separator, followed by two decimal places. It will do the same for
negative values, except they will show up in red. Any zero value will
have no currency symbol and will show two decimal places. If you
enter text into a cell, Excel will display the words
"No Text Please," regardless of the
true underlying text.

Figure 2-18. Custom number format sections

It is important to
note that formatting a cell's value does not affect
its underlying true value. For example, type any number into cell A1.
Select Format Cells Number Custom,
and using any format as a starting point, type
"Hello" (with the quotation marks). Then click
OK.

Although the cell displays the word Hello, you can
see its true value by selecting the cell and looking in the Formula
bar, or by pressing F2. If you were to reference this cell in a
formulae.g., =A1+20 the result cell
would take on the custom format. If you were to reference cell A1
along with many other cells that have any standard Excel
formate.g., =SUM(A1:A10)the result
cell would still take on the custom format of cell A1. Excel is
taking an educated guess that you want the result cell formatted the
same way as the referenced cell(s). If the referenced cells contain
more than one type of format, any custom format will take precedence.

This means you must always remember that Excel uses a
cell's true value for calculations, and not its
displayed value. This can create surprises when Excel calculates
based on cells that are formatted for no decimal places or for few
decimal places, for instance. To see this in action, enter
1.4 in cell A1 and 1.4
in cell A2, format both cells to show zero decimal places, and then
place =A1+A2 into a cell. The result, of
course, is 3, as Excel rounds.

Excel does have an option called "Precision as Displayed," which you can find by selecting Tools Options Calculation, but you should be aware that this option will permanently change stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. In other words, once it's been checked and given the okay, there is no turning back. (You can try, but the extra precision information is gone for good.)

The default format for any cell
is General. If you enter a number into a cell, Excel often will
guess the number format that is most
appropriate. For example, if you enter 10%
into a cell, Excel will format the cell as a percentage. Most of the
time, Excel guesses correctly, but sometimes you need to change it.

When using Format Cells, resist the temptation to force a left, right, or center horizontal format! By default, numbers are right-aligned and text is left-aligned. If you leave this alone, you can tell at a glance whether a cell is text or numeric, as in the case of the earlier example in which cell A1 appears to hold text, when in fact, it holds a number.

Each
section of a given format uses its own set of formatting codes. These
codes force Excel to make data appear how you want it to appear. So,
for instance, suppose you want negative numbers to appear inside
parentheses, and all numbers, positive, negative, and zero, to show
two decimal places. To do this, use this custom format:

0.00_ ;(-0.00)

If you also want negatives to show up in red, use this custom format:

0.00_ ;[Red](-0.00)

Note the use of the square brackets in the preceding code. The
formatting code tells Excel to make the number red.

You can use many different formatting
codes within sections of a custom format. Table 2-1 through Table 2-5, derived
from Microsoft documentation, explain these codes.

Note in particular the last kind of
formatting codes in Table 2-5: the comparison
operators. Assume you want the custom number format
0.00_ ;[Red](-0.00) to display
negative numbers in a red font and in brackets only if the number is
less than -100. To do this, use the following:

0.00_ ;[Red][<-100](-0.00);0.00

The formatting codes
[Red][<-100](-0.00) placed in the section for
negative numbers make this possible. Using this method in addition to
conditional formatting you can double the number of conditional
format conditions available from three to six.

Often, users want to display dollar
values as words. To do this, use the following custom format:

0 "Dollars and" .00 "Cents"

This format will
force a number entered as 55.25 to be displayed as
55 Dollars and .25 Cents. If
you want to convert numbers to dollars and cents, consult these two
custom functions from Microsoft: http://www.ozgrid.com/VBA/ValueToWords
and http://www.ozgrid.com/VBA/CurrencyToWords.

You can also use a custom format to display the words Low, Average,
or High, along with the number entered. Simply use this formatting
code:

[<11]"Low"* 0;[>20]"High"* 0;"Average"* 0

Note the use of the
*. This repeats the next character in the format
to fill the column width, meaning that all the Low, Average, or High
text will be forced to the right, while the number will be forced to
the left.