5.1. Formatting Cell Values
Cell value formatting is one aspect of worksheet design you don't want to ignore, because the values Excel stores can differ from the numbers that it displays in the worksheet, as shown in Figure 5-1. In many cases, it makes sense to have the numbers that appear in your worksheet differ from Excel's underlying values, since a worksheet that's displaying numbers to, say, 13 decimal places, can look pretty cluttered.
To format a cell's value, follow these steps:
You'll spend a lot of time in this chapter at the Format Cells dialog box. As you've already seen, the most obvious way to get there is to choose Home Format Cells Format Cells. However, your mouse fingers sure to tire out with that method. Fortunately, there's a quicker routeyou can use one of three dialog box launchers . Figure 5-3 shows the way.
5.1.1. Formatting Numbers
In the Format Cells dialog box, the Number tab lets you control how Excel displays numeric data in a cell. Excel gives you a lengthy list of predefined formats (as shown in Figure 5-4), and it also lets you design your own formats. Remember, Excel uses number formats when the cell contains only numeric information. Otherwise, Excel simply ignores the number format. For example, if you enter Half past 12 in a column full of times, Excel considers it plain ol' textalthough, under the hood, the cell's numerical formatting stays put, and Excel uses it if you change the cell content to a time.
When you create a new spreadsheet, every cell starts out with the same number format: General. This format comes with a couple of basic rules:
As you saw in Chapter 2, the way you type in a number can change a cell's formatting. For example, if you enter a number with a currency symbol, the number format of the cell changes automatically to Currency. Similarly, if you enter three numbers separated by dashes (-) or backward slashes (/), Excel assumes you're entering a date, and adjusts the number format to Date.
However, rather than rely on this automatic process, it's far better just to enter ordinary numbers and set the formatting explicitly for the whole column. This approach prevents you from having different formatting in different cells (which can confuse even the sharpest spreadsheet reader), and it makes sure you get exactly the formatting and precision you want. You can apply formatting to the column before or after you enter the numbers. And it doesn't matter if a cell is currently empty; Excel still keeps track of the number format you've applied.
Different number formats provide different options. For example, if you choose the Currency format, then you can choose from dozens of currency symbols. When you use the Number format, you can choose to add commas (to separate groups of three digits) or parentheses (to indicate negative numbers). Most number formats let you set the number of decimal places.
The following sections give a quick tour of the predefined number formats available in the Format Cells dialog box's Number tab. Figure 5-5 gives you an overview of how different number formats affect similar numbers.
The General format is Excel's standard number format; it applies no special formatting other than the basic rules described in Section 188.8.131.52. General is the only number format (other than Text) that doesn't limit your data to a fixed number of decimal places. That means if you want to display numbers that differ wildly in precision (like 0.5, 12.334, and 0.120986398), it makes sense to use General format. On the other hand, if your numbers have a similar degree of precision (for example, if you're logging the number of miles you run each day), the Number format makes more sense.
The Number format is like the General format but with three refinements. First, it uses a fixed number of decimal places (which you set). That means that the decimal point always lines up ( assuming you've formatted an entire column). The Number format also allows you to use commas as a separator between groups of three digits, which is handy if you're working with really long numbers. Finally, you can choose to have negative numbers displayed with the negative sign, in parentheses, or in red lettering.
The Currency format closely matches the Number format, with two differences. First, you can choose a currency symbol (like the dollar sign, pound symbol, Euro symbol, and so on) from an extensive list; Excel displays the currency symbol before the number. Second, the Currency format always includes commas. The Currency format also supports a fixed number of decimal places ( chosen by you), and it allows you to customize how negative numbers are displayed.
The Accounting format is modeled on the Currency format. It also allows you to choose a currency symbol, uses commas, and has a fixed number of decimal places. The difference is that the Accounting format uses a slightly different alignment. The currency symbol's always at the far left of the cell (away from the number), and there's always an extra space that pads the right side of the cell. Also, the Accounting format always shows negative numbers in parentheses, which is an accounting standard. Finally, the number 0 is never shown when using the Accounting format. Instead, a dash (-) is displayed in its place. There's really no reason to prefer the Currency or the Accounting format. Think of it as a personal decision, and choose whichever looks nicest on your worksheet. The only exception is if you happen to be an accountant , in which case you really have no choice in the matterstick with your namesake.
The Percentage format displays fractional numbers as percentages. For example, if you enter 0.5, that translates to 50%. You can choose the number of decimal places to display.
There's one trick to watch out for with the Percentage format. If you forget to start your number with a decimal, Excel quietly "corrects" your numbers. For example, if you type 4 into a cell that uses the Percentage format, Excel interprets this as 4%. As a result, it actually stores the value 0.04. A side-effect of this quirkiness is that if you want to enter percentages larger than 100%, you can't enter them as decimals. For example, to enter 200%, you need to type in 200 (not 2.00).
The Fraction format displays your number as a fraction instead of a number with decimal places. The Fraction format doesn't mean you have to enter the number as a fraction (although you can if you want by using the forward slash, like 3/4). Instead it means that Excel converts any number you enter and display it as a fraction. Thus, to have 1/4 appear you can either enter .25 or 1/4.
Note: If you try to enter 1/4 and you haven't formatted the cell to use the Fraction number format, you won't get the result you want. Excel assumes you're trying to enter a date (in this case, January 4th of the current year). To avoid this misunderstanding, change the number format before you type in your fraction. Or, enter it as 0 1/4 (zero and one quarter).
People often use the Fraction format for stock market quotes, but it's also handy for certain types of measurements (like weights and temperatures ). When using the Fraction format, Excel does its best to calculate the closest fraction, which depends on a few factors including whether an exact match exists (entering .5 always gets you 1/2, for example) and what type of precision level you've picked when selecting the Fraction formatting.
You can choose to have fractions with three digits (for example, 100/200), two digits (10/20), or just one digit (1/2), using the top three choices in the Type list. For example, if you enter the number 0.51, then Excel shows it as 1/2 in one-digit mode, and the more precise 51/100 in three-digit mode. In some cases, you may want all numbers to use the same denominator (the bottom number in the fraction) so that it's easy to compare different numbers. (Don't you wish Excel had been around when you were in grammar school?) In this case, you can choose to show all fractions as halves (with a denominator of 2), quarters (a denominator of 4), eighths (8), sixteenths (16), tenths (10), and hundredths (100). For example, the number 0.51 would be shown as 2/4 if you chose quarters .
Tip: Entering a fraction in Excel can be awkward because Excel may attempt to convert it to a date. To prevent this confusion, always start by entering 0 and then a space. For example, instead of typing 2/3 enter 0 2/3 (which means zero and two- thirds ). If you have a whole number and a fraction, like 1 2/3, you'll also be able to duck the date confusion.
The Scientific format displays numbers using scientific notation, which is ideal when you need to handle numbers that range widely in size (like 0.0003 and 300) in the same column . Scientific notation displays the first non-zero digit of a number, followed by a fixed number of digits, and then indicates what power of 10 that number needs to be multiplied by to generate the original number. For example, 0.0003 becomes 3.00 x 10 -4 (displayed in Excel as 3.00E-04). The number 300, on the other hand, becomes 3.00 x 10 2 (displayed in Excel as 3.00E02). Scientists surprise, surpriselike the Scientific format for doing things like recording experimental data or creating mathematical models to predict when an incoming meteor will strike the Earth.
Few people use the Text format for numbers, but it's certainly possible to do so. The Text format simply displays a number as though it were text, although you can still perform calculations with it. Excel shows the number exactly as it's stored internally, positioning it against the left edge of the column. You can get the same effect by placing an apostrophe before the number (although that approach won't allow you to use the number in calculations).
5.1.2. Formatting Dates and Times
Excel gives you lots of options here. You can use everything from compact styles like 3/13/07 to longer formats that include the day of the week, like Sunday, March 13, 2007. Time formats give you a similar range of options, including the ability to use a 12-hour or 24- hour clock, show seconds, show fractional seconds, and include the date information.
To format dates and times, first open the Format Cells dialog box shown in Figure 5-7 (Home Cells Format Format Cells). Choose Date or Time from the column on the left and then choose the format from the list on the right. Date and Time both provide a slew of options.
Excel has essentially two types of date and time formats:
The first group (the formats that rely on a computer's regional settings) is the smallest. It includes two date formats (a compact, number-only format and a long, more descriptive format) and one time format. In the Type list, these formats are at the top and have an asterisk next to them.
The second group (the formats that are independent of a computer's regional settings) is much more extensive. In order to choose one of these formats, you first select a region from the Locale list, and then you select the appropriate date or time format. Some examples of locales include "English (United States)" and "English (United Kingdom)."
If you enter a date without specifically formatting the cell, Excel usually uses the short region-specific date format. That means that the order of the month and year vary depending on the regional settings of the current computer. If you incorporate the month name (for example, January 1, 2007), instead of the month number (for example, 1/1/2007), Excel uses a medium date format that includes a month abbreviation, like 1-Jan-2007.
Note: You may remember from Chapter 2 that Excel stores a date internally as the cumulative number of days that have elapsed since a certain long-ago date that varies by operating system. You can take a peek at this internal number using the Format Cells dialog box. First, enter your date. Then, format the cell using one of the number formats (like General or Number). The underlying date number appears in your worksheet where the date used to be.
5.1.3. Special Formats for Special Numbers
You wouldn't ever want to perform mathematical operations with some types of numeric information. For example, it's hard to image a situation where you'd want to add or multiply phone numbers or Social Security numbers.
When entering these types of numbers, therefore, you may choose to format them as plain old text. For example, you could enter the text (555) 123-4567 to represent a phone number. Because of the parentheses and the dash (-), Excel won't interpret this information as a number. Alternatively, you could just precede your value with an apostrophe (') to explicitly tell Excel that it should be treated as text (you might do this if you don't use parentheses or dashes in a phone number).
But whichever solution you choose, you're potentially creating more work for yourself because you have to enter the parentheses and the dash for each phone number you enter (or the apostrophe). You also increase the likelihood of creating inconsistently formatted numbers, especially if you're entering a long list of them. For example, some phone numbers may end up entered in slightly similar but somewhat different formats, like 555-123-4567 and (555)1234567.
To avoid these problems, apply Excel's Special number format (shown in Figure 5-8), which converts numbers into common patterns. And lucky you: In the Special number format, one of the Type options is Phone Number (other formats are for Zip codes and Social Security numbers).
The Special format is a good idea, but it's limited. Out of the box, Excel provides only a small set of special types that you can use. However, there's no reason you can't handle similar problems by creating your own custom formats, as you'll see in the next section.
5.1.4. Custom Formats
As versatile as Excel is, it can't read your mind. You'll find some situations when you want to format numbers in a specialized way that Excel just doesn't expect. For example, you may want to use the ISO (International Organization for Standardization) format for dates, which is used in a wide range of scientific and engineering documents. This format is year-month-day (as in 2007-12-25). Although it's fairly straightforward, Excel doesn't provide this format as a standard option.
Or maybe you want to type in short versions of longer numbers. For example, say your company, International Pet Adventures, uses an employee number to identify each worker, in the format 0521-1033. It may be that 0521-is a departmental identification code for the Travel department. To save effort, you want to be able to enter 1033 and have Excel automatically insert the leading 0521-in your worksheets.
The solution lies in creating your own custom formats . Custom formats are a powerful tool for taking control of how Excel formats your numbers. Unfortunately, they aren't exactly easy to master.
The basic concept behind custom formats is that you define the format using a string of special characters and placeholders. This format string tells Excel how to format the number or date, including details like how many decimal places it should include, and how it should treat negative numbers. You can also add fixed characters that never change, like the employee number format just described.
184.108.40.206. Creating a custom format
Here's the easiest way to apply a custom format:
220.127.116.11. Custom format string codes
The tricky part about Custom formats is creating the right format string. To the untrained eye, the format string looks like a cryptic jumble of symbolswhich it is. But these symbols, or formatting codes in Excel lingo, actually have very specific and clear meanings.
For example, the format string $#,##0.00 translates into the following series of instructions:
In fact, $#,##0.00 is the format string for the basic Currency format. Once you understand what the codes stand for and how they work together, you can create some really useful Custom format strings.
You have three types of codes at your disposal for creating format strings: those used to format dates and times; those used to format numbers; and those used to format ordinary text. The following three sections tackle each type of format code.
18.104.22.168. Date and time format strings
Date and time format strings are built out of pieces. Each piece represents a single part of the date, like the day, month, year, minute, hour, and so on. You can combine these pieces in whatever order you want, and you can insert your own custom text along with these values.
Note: Keep in mind that none of these formatting codes actually generate or insert the date in your worksheet for you. That is, simply formatting an empty cell with one of these custom strings isn't going to cause the date to appear. Instead, these format strings take the dates that you enter and make sure that they all appear in a uniform style.
The basic ingredients for a date or time format string are shown in Table 5-1. These strings are placeholders that represent the different parts of the date. If you want to include fixed text along with the date, put it in quotation marks.
Table 5-1. Date and Time Formatting Codes
For example, consider the following format string:
If you apply this format string to a cell that contains a date, you'll end up with the following in your worksheet (assuming you entered the date January 15, 2007): 2007-01-15 .
Note: Regardless of how you type in the date, once you've formatted a cell using a Custom format string, that always overrides the format you use when you type in the date. In other words, it doesn't matter whether you type 1/15/07 or January 15, 2007 in the cellExcel still displays it as 2006-01-15, if that's what your custom format dictates.
Now if you format the same value with this format string:
you'll see this in your worksheet:
And remember, whatever information you choose to display or hide, Excel always stores the same date internally.
Note: You'll learn much more about date and time calculations in Chapter 11.
22.214.171.124. Number format strings
Custom number formats are more challenging than Custom date formats because Excel gives you lots of flexibility when it comes to customizing number formats. Table 5-2 shows the different codes you can use. The most important of these codes are the digit placeholders 0, ?, and #. You use these to tell Excel where it should slot in the various digits of the number that's currently in the cell (or that you're typing in). For example, a format string that looks like this:
displays a number in your worksheet that looks like this:
In this format string, the # character is a placeholder that lets you put the comma wherever you want it. The 0 character is a placeholder that makes sure the trailing zeroes appear, even when you're showing a whole number. Table 5-2 reveals many more tricks of the trade.
Table 5-2. Number Formatting Codes
Note: Excel uses custom number formats to decide how to round off displayed numbers, and how to format them (by adding commas, currency symbols, and so on). But no matter what format string you use, you can't coax Excel into shaving off digits that appear to the left of the decimal placeand for good reason: doing so would mangle your numbers beyond recognition.
It helps to keep a few pointers in mind when you use the number formatting codes listed in Table 5-2:
Note: Remember, Custom format strings control how Excel displays values. These strings aren't meant to control what values someone can enter in a cell. To set rules for allowed data, you need a different featuredata validation, which is described in Section 22.2.
Excel also lets you use codes that apply currency symbols, percent symbols, and colors. As with date values, you can insert fixed textalso known as literals into a number formatting string using quotation marks. For example, you could add "USD" at the end of the format string to indicate that a number's denominated in U.S. dollars. Excel automatically recognizes some characters as literals, including currency symbols, parentheses, plus (+) and minus (-) symbols, backward slashes (\), and spaces, which means you don't need to use quotation marks to have those characters appear.
Finally, the last thing you should know about Custom number format strings is that if you'd like your worksheet to display different types of values (for example, negative versus positive) differently, you can actually create a collection of four different format strings, each of which formats different types of numbers, depending on what values you type into the cell. Collectively, these four format strings tell Excel how to deal with positive values, negatives values, zero, and text values. The format strings must always appear in this order and be separated by semicolons. Here's an example:
#,###; [red]#,###; "---"; @
Excel uses the first format string (#,###) if the cell contains a positive number. Excel uses the second format string ([red]#,###) to display negative numbers. This format is the same as for positive numbers, except it displays the text in red. The third format string ("---") applies to zero values. It inserts three dashes into the cell when the cell contains the number 0. (If the cell is empty, no format string is used, and the cell remains blank.) Finally, Excel uses the last format if you enter text into the cell. The @ symbol simply copies any text into the cell as it's entered.
Tip: For a real trick, use the empty format string ;;; to puzzle friends and coworkers. This format string specifies that no matter what the content in the cell (positive number, negative number, zero, or text), Excel shouldn't display it. You can add information to the cell (and see it in the formula bar), but it doesn't appear on the worksheet or in your printouts.
126.96.36.199. Text format strings
Good news: Text format strings are extremely simple. Usually, you use a text format string to repeatedly insert the same text in a large number of cells. For example, you may want to add the word NOTE before a collection of entries. To do this, your format string needs to define the literal text you want to usein this case, the word "NOTE"and place the text within quotation marks (including any spaces you wish to appear). Use the @ symbol to indicate which side of the string the cell contents should go. For example, if you set the format string:
and then you type Transfer payment into the cell, Excel displays it as NOTE: Transfer payment .