101 Excel 2013 Tips, Tricks and Timesavers (9 page)
Read 101 Excel 2013 Tips, Tricks and Timesavers Online
Authors: John Walkenbach
[Green]General;[Red]-General;[Black]General;[Blue]General
This example takes advantage of the fact that colors have special codes. A cell formatted with this custom number format displays its contents in a different color, depending on the value. When a cell is formatted with this custom number format, a positive number is green, a negative number is red, a zero is black, and text is blue. By the way, using the Excel conditional formatting feature is a much better way to apply color to cells based on their content.
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 by using the format string in the Type field. Be sure 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’s added to the list. When you finally get the correct format string, open the Format Cells dialog box one more time and delete your previous attempts.
Custom number format codes
Table 16-1 briefly describes the formatting codes available for custom formats.
Table 16-1:
Codes Used to Create Custom Number Formats
Code | What It Does |
General | Displays the number in General format. |
# | Serves as a digit placeholder that displays only significant digits and doesn’t display insignificant zeros. |
0 (zero) | Serves as a digit placeholder that displays insignificant zeros if a number has fewer digits than there are zeros in the format. |
? | Serves as a digit placeholder that adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font; also used for fractions that have varying numbers of digits. |
. | Displays the decimal point. |
% | Displays a percentage. |
, | Displays the thousands separator. |
E- E+ e- e+ | Displays scientific notation. |
$ – + / ( ) : space | Displays the actual character. |
\ | Displays the next character in the format. |
* | Repeats the next character to fill the column width. |
_ (underscore) | Leaves a space equal to the width of the next character. |
“text” | Displays the text inside the double quotation marks. |
@ | Serves as a text placeholder. |
[color] | Displays the characters in the specified color and can be any of the following text strings (not case-sensitive): Black, Blue, Cyan, Green, Magenta, Red, White, or Yellow. |
[COLOR n] | Displays the corresponding color in the color palette, where |
[condition value] | Enables you to set your own criteria for each section of a number format. |
Table 16-2 describes the codes used to create custom formats for dates and times.
Table 16-2:
Codes Used in Creating Custom Formats for Dates and Times
Code | What It Displays |
M | The month as a number without leading zeros (1–12) |
mm | The month as a number with leading zeros (01–12) |
mmm | The month as an abbreviation (Jan–Dec) |
mmmm | The month as a full name (January–December) |
mmmmm | The first letter of the month (J–D) |
d | The day as a number without leading zeros (1–31) |
dd | The day as a number with leading zeros (01–31) |
ddd | The day as an abbreviation (Sun–Sat) |
dddd | The day as a full name (Sunday–Saturday) |
yy or yyyy | The year as a two-digit number (00–99) or as a four-digit number (1900–9999) |
h or hh | The hour as a number without leading zeros (0–23) or as a number with leading zeros (00–23) |
m or mm | The minute as a number without leading zeros (0–59) or as a number with leading zeros (00–59) |
s or ss | The second as a number without leading zeros (0–59) or as a number with leading zeros (00–59) |
[ ] | Hours greater than 24 or minutes or seconds greater than 60 |
AM/PM | The hour using a 12-hour clock or no AM/PM indicator if the hour uses a 24-hour clock |
Tip 17: Using Custom Number Formats to Scale Values
If you work with large numbers, you may prefer to display them scaled to thousands or millions rather than display the entire number. For example, you may want to display a number like 132,432,145 in millions: 132.4.
The way to display scaled numbers is to use a custom number format. The actual unscaled number, of course, will be used in calculations that involve that cell. The formatting affects only how the number is displayed. To enter a custom number format, press Ctrl+1 to display the Format Cells dialog box. Then click the Number tab and select the Custom category. Put your custom number format in the Type field.
Table 17-1 shows examples of number formats that scale values in millions.
Table 17-1:
Examples of Displaying Values in Millions
Value | Number Format | Display |
123456789 | #,###,, | 123 |
1.23457E+11 | #,###,, | 123,457 |
1000000 | #,###,, | 1 |
5000000 | #,###,, | 5 |
–5000000 | #,###,, | –5 |
0 | #,###,, | (blank) |
123456789 | #,###.00,, | 123.46 |
1.23457E+11 | #,###.00,, | 123,457.00 |
1000000 | #,###.00,, | 1.00 |
5000000 | #,###.00,, | 5.00 |
–5000000 | #,###.00,, | –5.00 |
0 | #,###.00,, | .00 |
123456789 | #,###,,”M” | 123M |
1.23457E+11 | #,###,,”M” | 123,457M |
1000000 | #,###,,”M” | 1M |
–5000000 | #,###,,”M” | –5M |
123456789 | #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) | 123.5M |
1000000 | #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) | 1.0M |
–5000000 | #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) | (5.0M) |
0 | #,###.0,,”M”_);(#,###.0,,”M)”;0.0”M”_) | 0.0M |
Table 17-2 shows examples of number formats that scale values in thousands.
Table 17-2:
Examples of Displaying Values in Thousands
Value | Number Format | Display |
123456 | #,###, | 123 |
1234565 | #,###, | 1,235 |
–323434 | #,###, | –323 |
123123.123 | #,###, | 123 |
499 | #,###, | (blank) |
500 | #,###, | 1 |
500 | #,###.00, | .50 |
Table 17-3 shows examples of number formats that display values in hundreds.
Table 17-3:
Examples of Displaying Values in Hundreds
Value | Number Format | Display |
546 | 0”.”00 | 5.46 |
100 | 0”.”00 | 1.00 |
9890 | 0”.”00 | 98.90 |
500 | 0”.”00 | 5.00 |
–500 | 0”.”00 | –5.00 |
0 | 0”.”00 | 0.00 |
Tip 18: Creating a Bulleted List
Word-processing software, such as Microsoft Word, makes it very easy to create a bulleted list of items. Excel doesn’t have a feature that creates bulleted lists, but it’s easy to fake it.
Using a bullet character
You can generate a bullet character by pressing Alt, and typing
0149
on the numeric keypad. If your keyboard lacks a numeric keypad, press the Function key and type numbers using the normal keys.
Figure 18-1 shows a list in which each item is preceded with a bullet character and a space. The cells use wrap-text formatting. Items that occupy more than one line are not indented. In a bulleted list, multiple lines are usually indented so they line up with the first line.
Figure 18-1:
Inserting a bullet character before each item.
Figure 18-2 shows a second attempt. This approach requires two columns. Column A holds the bullet character, formatted so the bullet is top-aligned and right-aligned. The text is in column B.
Figure 18-2 also shows an alternative, a numbered list. The cells that contain the numbers use this custom number format, which displays a decimal point, but no decimal places:
General”. “
You can, of course, use any character you like for the bullet. Use Insert⇒Symbols
➜
Symbol to display (and insert) characters from any font that’s installed on your system.