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 formula-e.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 format-e.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.htm and http://www.ozgrid.com/VBA/CurrencyToWords.htm.

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.