Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

The remainder of this appendix consists of useful examples of custom number formats. You can use most of these format codes as-is. Others may require slight modification to meet your needs.

Scaling Values

You can use a custom number format to scale a number. For example, if you work with very large numbers, you may want to display the numbers in thousands (that is, displaying 1,000,000 as 1,000). The actual number, of course, will be used in calculations that involve that cell. The formatting affects only how it is displayed.

DISPLAYING VALUES IN THOUSANDS

The following format string displays values without the last three digits to the left of the decimal place, and no decimal places. In other words, the value appears as if it's divided by 1,000 and rounded to no decimal places.

#,###,

A variation of this format string follows. A value with this number format appears as if it's divided by 1,000 and rounded to two decimal places.

#,###.00,

Table B-5 shows examples of these number formats.

DISPLAYING VALUES IN HUNDREDS

The following format string displays values in hundreds, with two decimal places. A value with this number format appears as if it's divided by 100 and rounded to two decimal places.

0"."00

Table B-6 shows examples of these number formats.

DISPLAYING VALUES IN MILLIONS

The following format string displays values in millions, with no decimal places. A value with this number appears as if it's divided by 1,000,000, and rounded to no decimal places.

#,###,,

A variation of this format string follows. A value with this number appears as if it's divided by 1,000,000, and rounded to two decimal places.

#,###.00,,

Another variation follows. This adds the letter M to the end of the value.

#,###,,M

The following format string is a bit more complex. It adds the letter M to the end of the value-and also displays negative values in parentheses as well as displaying zeros.

#,###.0,,"M"_);(#,###.0,,"M)";0.0"M"_)

Table B-7 shows examples of these format strings.

ADDING ZEROS TO A VALUE

The following format string displays a value with three additional zeros and no decimal places. A value with this number format appears as if it's rounded to no decimal places and then multiplied by 1,000.

#",000"

Examples of this format string, plus a variation that adds six zeros, are shown in Table B-8.

Hiding Zeros

In the following format string, the third element of the string is empty, which causes zero value cells to display as blank:

General;-General;

This format string uses the General format for positive and negative values. You can, of course, substitute any other format codes for the positive and negative parts of the format string.

Displaying Leading Zeros

To display leading zeros, create a custom number format that uses the 0 character. For example, if you want all numbers to display with ten digits, use the number format string that follows. Values with fewer than ten digits will display with leading zeros.

0000000000

You also can force all numbers to display with a fixed number of leading zeros. The format string that follows, for example, prepends three zeros to each number:

"000"#

In the following example, the format string uses the repeat character code (an asterisk) to apply enough leading zeros to fill the entire width of the cell:

*00

Displaying Fractions

Excel supports quite a few built-in fraction number formats. (Select the Fraction category from the Number tab of the Format Cells dialog box.) For example, to display the value .125 as a fraction with 8 as the denominator, select As Eighths (4/8) from the Type list.

You can use a custom format string to create other fractional formats. For example, the following format string displays a value in 50ths:

# ??/50

The following format string displays a value in terms of fractional dollars. For example, the value 154.87 is displayed as 154 and 87/100 Dollars.

0 "and "??/100 "Dollars"

The following example displays the value in sixteenths, with an appended double quotation mark. This format string is useful when you deal with inches (for example, 2/16").

# ??/16\"

Displaying N/A for Text

The following number format string uses General formatting for all cell entries except text. Text entries appear as N/A.

0.0;0.0;0.0;"N/A"

You can, of course, modify the format string to display specific formats for values. The following variation displays values with one decimal place:

0.0;0.0;0.0;"N/A"

Displaying Text in Quotes

The following format string displays numbers normally but surrounds text with double quotation marks:

General;General;General;\"@\"

Repeating A Cell Entry

The following number format is perhaps best suited as an April Fool's gag played on an office mate. It displays the contents of the cell three times. For example, if the cell contains the text Budget, the cell displays Budget Budget Budget. If the cell contains the number 12, it displays as 12 12 12.

@ @ @

Displaying a Negative Sign on the Right

The following format string displays negative values with the negative sign to the right of the number. Positive values have an additional space on the right, so both positive and negative numbers align properly on the right.

0.00_-;0.00-

When you create a custom number format, don't overlook the Sample box in the Number tab of the Format Cells dialog box.

This box displays the value in the active cell using the format string in the Type box. It's a good idea to test your custom number formats by using the following data: a positive value, a negative value, a zero value, and text. Often, creating a custom number format takes several attempts. Each time you edit a format string, it is added to the list. When you finally get the correct format string, access the Format Cells dialog box one more time and delete your previous attempts.

To make the negative numbers more prominent, you can add a color code to the negative part of the number format string:

0.00_-;[Red]0.00-

Conditional Number Formatting

Conditional formatting refers to formatting that is applied based on the contents of a cell. Excel's Conditional Formatting feature provides the most efficient way to perform conditional formatting of numbers, but you also can use custom number formats.

The following format string displays different text (no value), depending on the value in the cell. This format string essentially separates the numbers into three groups: less than or equal to 4, greater than or equal to 8, and other.

[<=4]"Low"* 0;[>=8]"High"* 0;"Medium"* 0

The following number format is useful for telephone numbers. Values greater than 9999999 (that is, numbers with area codes) are displayed as (xxx) xxx-xxxx. Other values (numbers without area codes) are displayed as xxx-xxxx.

[>9999999](000) 000-0000;000-0000

For U.S. ZIP codes, you might want to use the format string that follows. This displays ZIP codes using five digits. But if the number is greater than 99999, it uses the ZIP-plus-four format (xxxxx-xxxx).

[>99999]00000-0000;00000

Coloring Values

Custom number format strings can display the cell contents in various colors. The following format string, for example, displays positive numbers in red, negative numbers in green, zero values in black, and text in blue:

[Red]General;[Green]-General;[Black]General;[Blue]General

Following is another example of a format string that uses colors. Positive values are displayed normally; negative numbers and text cause Error! to be displayed in red.

General;[Red]"Error!";0;[Red]"Error!"

Using the following format string, values that are less than 2 are displayed in red. Values greater than 4 are displayed in green. Everything else (text, or values between 2 and 4) displays in black.

[Red][<2]General;[Green][>4]General;[Black]General

As seen in the preceding examples, Excel recognizes color names such as [Red] and [Blue]. It also can use other colors from the color palette, indexed by a number. The following format string, for example, displays the cell contents using the sixteenth color in the color palette:

[Color16]General

Formatting Dates and Times

When you enter a date into a cell, Excel formats the date using the system short date format. You can change this format using the Windows Control Panel (Regional and Language Options).

Excel provides many useful built-in date and time formats. Table B-9 shows some other custom date and time formats that you may find useful. The first column of the table shows the date/time serial number.

Displaying Text with Numbers

The ability to display text with a value is one of the most useful benefits of using a custom number format. To add text, just create the number format string as usual (or use a builtin number format as a starting point) and put the text within quotation marks. The following number format string, for example, displays a value with the text (US Dollars) added to the end:

#,##0.00 "(US Dollars)"

Here's another example that displays text before the number:

"Average: "0.00

If you use the preceding number format, you'll find that the negative sign appears before the text for negative values. To display number signs properly, use this variation:

"Average: "0.00;"Average: "-0.00

The following format string displays a value with the words Dollars and Cents. For example, the number 123.45 displays as 123 Dollars and .45 Cents.

0 "Dollars and" .00 "Cents"

Displaying a Zero with Dashes

The following number format string displays zero values as a series of dashes:

#,##0.0;-###0.0;------

Excel's TEXT function accepts a number format string as its second argument. For example, the following formula displays the contents of cell A1 using a custom number format that displays a fraction:

=TEXT(A1,"# ??/50")

However, not all formatting codes work when used in this manner. For example, colors and repeating characters are ignored. The following formula does not display the contents of cell A1 in red:

=TEXT(A1,"[Red]General")

You can, of course, create lots of variations. For example, you can replace the six hyphens with any of the following:

<0> -0- ~~ "<NULL>" "[NULL]"

Using Special Symbols

Your number format strings can use special symbols, such as the copyright symbol, degree symbol, and so on.

The easiest way to use insert a symbol into a number format string is to enter it into a cell. Copy the character and then paste it into your custom number format string (using Ctrl+V). Use the Insert Text Symbol command, which displays the Insert Symbol dialog box, to enter a special character into a cell.

Suppressing Certain Types of Entries

You can use number formatting to hide certain types of entries. For example, the following format string displays text but not values:

;;

Excel doesn't have a worksheet function that displays the number format for a specified cell. You can, however, create your own function using VBA. Insert the following function procedure into a VBA module:

Function NumberFormat(cell) As String ' Returns the number format string for a cell Application.Volatile True NumberFormat = cell.Range("A1").NumberFormat End Function

Then you can create a formula such as the following:

=NumberFormat(C4)

This formula returns the number format for cell C4. If you change a number format, use Ctrl+Alt+F9 to force the function to be reevaluated.

This function can be useful in formulas that calculate a conditional sum. For example, you can create a formula that sums only the cells that use a particular number format. See Chapter 7 for information about computing conditional sums.

This format string displays values (with one decimal place) but not text or zeros:

0.0;-0.0;;

This format string displays everything except zeros (values display with one decimal place):

0.0;-0.0;;@

You can use the following format string to completely hide the contents of a cell:

;;;

Note that when the cell is activated, however, the cell's contents are visible on the formula bar.

Filling a Cell with a Repeating Character

The asterisk (*) symbol specifies a repeating character in a number format string. The repeating character completely fills the cell, and adjusts if the column width changes. The following format string, for example, displays the contents of a cell padded on the right with dashes:

General*-;-General*-;General*-;General*-

Displaying Leading Dots

The following custom number format is a variation on the accounting format. Using this number format displays the dollar sign on the left and the value on the right. The space in between is filled with dots.

_($*.#,##0.00_);_($*.(#,##0.00);_($* "-"??_);_(@_)