Section 4.1. Formatting Cell Values
4.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 4-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:
-
Select the cells you want to format.
You can apply formatting to individual cells, or to a collection of cells. Usually, you'll want to format an entire column at once, because all the values in a column typically contain the same type of data. Remember, to select a column, you simply need to click the column header (the gray box at the top with the column letter).
Note: Technically, a column contains two types of data: the values you're storing within the actual cells and the column title in the topmost cell (where the text is). However, you don't need to worry about unintentionally formatting the column title because number formats are only applied to numeric cells (cells that contain dates, times, or numbers). Excel doesn't use the number format for the column title cell because it contains text.
-
Select Format
Cells, or just right-click the selection, and choose Format Cells. In either case, the Format Cells dialog box appears, as shown in Figure 4-2.
Figure 4-2. The Format Cells dialog box provides one-stop shopping for cell value and cell appearance formatting. The first tab, Number, lets you configure how numeric values are formatted. The Alignment, Font, Border, and Patterns tabs are all used to control the appearance of the cell. Finally, the Protection tab allows you to prevent changes and hide formulas. (You'll learn about worksheet protection features in Chapter 13.)
-
Set the format options, and then click OK to apply them.
The options in the Number tab let you choose how Excel translates the cell value into a display value. (Number formatting choices are covered in the next section, Section 4.1.1.) Most of the other tabs on this dialog box are for cell appearance formatting, which is covered later in this chapter.
Note: Once you apply formatting to a cell, it retains that formatting even if you clear the cell's contents (by selecting it and pressing Delete). In addition, formatting follows a cell copy, so if you copy the content from cell A1 to cell A2, the formatting comes with it. Formatting includes both cell value formatting and cell appearance.The only way to remove the formatting is to highlight the cell and select Edit
Clear Formats. This command removes the formatting, restoring the cell to its original, General number format (which you'll learn more about next), but it doesn't remove any of the cell's content. 4.1.1. Formatting Numbers
The Number tab in the Format Cells dialog box lets you control how Excel displays numeric data in a cell. Excel gives you a lengthy list of predefined formats (as shown in Figure 4-3), and it also lets you design your own formats. Remember, Excel uses number formats when the cell contains numeric information only. Otherwise, Excel simply ignores the number format (although the format is still there and will still be used if you change the cell content to a number, date, or 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:
-
If a number has any decimal places, Excel displays them, provided they fit in the column. If there are more decimal places than Excel can display, it leaves out the ones that don't fit. (It rounds up the last displayed digit, when appropriate). If you change a column width, Excel automatically adjusts the amount of digits it displays.
-
Excel removes leading and trailing zeros. Thus, 004.00 becomes 4. The only exception to this rule occurs with numbers between -1 and 1, which retain the 0 before the decimal point. For example, Excel displays the number .42 as 0.42.
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 Currency format, you can choose from dozens of currency symbols. If you use 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.
| Up To Speed The Relationship Between Formatting and Values |
| The format that you choose for a number doesn't affect Excel's internal storage of that number. For example, if a cell contains the fraction 1/3, Excel stores this value as 0.333333333333333. (The exact number of decimal places varies, depending on the number you've entered, due to the slight approximations computers need to make when converting fractional numbers into 0's and 1's.) When deciding how to format a cell, you might choose to show only two decimal places, in which case the number appears in your worksheet as 0.33. Or maybe you choose just one decimal place, in which case the number will simply be 0.3. In both cases, Excel still keeps the full fifteen or so decimal places on hand. To tell the difference between the displayed number and the real number that Excel stores behind the scenes, just move to the cell. Then look at the Formula bar, which always shows you the real deal. Because of this difference between the stored value and the displayed number, there may be some situations in which it looks like Excel is making a mistake. For example, imagine you have three cells, and each stores 0.333333333333333 but displays only 0.3. When you add these three cell values together, you won't end up with 0.3 + 0.3 + 0.3 = 0.9. Instead, you'll add the more precise stored values and end up with a number that's infinitesimally close to, but not quite, 1. Excel rounds this number up to 1. This is almost always the way you want Excel to work, because you know full well that if you add up 1/3 three times you end up with 1. But if you need to, you can change this behavior. If you select Tools |
The following sections give a quick tour of the predefined number formats available in the Number tab in the Format Cells dialog box. Figure 4-4 gives you an overview of how different number formats affect similar numbers.
4.1.1.1 General
The General format is Excel's standard number format; it applies no special formatting other than the basic rules described at the beginning of this chapter. 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), Number format makes more sense.
|
4.1.1.2 Number
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.
4.1.1.3 Currency
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 will display 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.
4.1.1.4 Accounting
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 is always shown at the far left of the cell (away from the number), and there is 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.
4.1.1.5 Percentage
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).
4.1.1.6 Fraction
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 backward slash, like 3/4). Instead it means that Excel will convert any number you enter and display it as a fraction. Thus, to have 1/4 appear you can either enter .25 or 1/4.
The Fraction format is often used 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 will always get 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, Excel shows it as 1/2 in one-digit mode, and the more precise 51/100 in three-digit mode. In some cases, you might want all numbers to use the same denominator (the bottom number in the fraction) so that it's easy to compare different numbers. 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: Sometimes, 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.
4.1.1.7 Scientific
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 10 -4 (displayed in Excel as 3.00E-04). The number 300, on the other hand, becomes 3.00 10 2 (displayed in Excel as 3.00E02). Scientistssurprise, surpriselike the Scientific format for doing things like recording experimental data or creating mathematical models to predict when an incoming meteor will graze the Earth.
4.1.1.8 Text
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).
| FREQUENTLY ASKED QUESTION Just How Precise Are Excel Numbers, Anyway? |
| Can I enter a number with 10 decimal places? How about 20? Here's a handy way to find out: type the fraction 2/3 into a cell, then check the Formula bar, which shows you the number Excel has stored. Turns out Excel thinks of 2/3 as 0.666666666666667. This test demonstrates that Excel is limited to about 14 significant digits, and that it rounds up the last digit. You might be slightly unnerved by the word "about," but in the binary world of computers, fractional numbers don't have a fixed number of digits and may just be approximations with very slight rounding errors. One good (but technical) explanation of this phenomenon can be found on Wikipedia (an online encyclopedia) at http://en.wikipedia.org/wiki/Floating_point. Because Excel doesn't store fractions precisely as they exist in the world of real math, you may occasionally experience minor rounding errors in calculations with more than 14 significant digits. (Recall from high school math that the number of significant digits is the number of digits starting with the first non-zero digit and ending with the last non-zero digit. Essentially , the significant digits hold all the information in your number.) This behavior shouldn't cause you to panicit's a limitation of nearly all computer software, based on the way that computers manipulate numbers. |
4.1.2. Formatting Dates and Times
To format dates and times, in the Format Cells dialog box (Format Cells), shown in Figure 4-5, 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. You can use everything from compact styles like 3/13/05 to longer formats that include the day of the week, like Sunday, March 13, 2005. 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.
|
There are essentially two types of date and time formats:
-
Formats that take the regional settings of the computer you're using into account. With these formats, dates display differently depending on the computer that's running Excel. This is a good choice because it lets everyone see dates in just the way they want to, which means no time-consuming arguments about month-day-year or day-month-year ordering.
-
Formats that ignore the regional settings of individual computers. These formats define a fixed pattern for month, day, year, and time components , and display date- related information in exactly the same way on all computers. If you need to absolutely make sure a date is in a certain format, you should use this choice.
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. But if you incorporate the month name (for example, January 1, 2005), instead of the month number (for example, 1/1/2005), Excel uses a medium date format that includes a month abbreviation, like 1-Jan-2005.
Tip: 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 will appear in your worksheet where the date used to be.
4.1.3. Special Formats for Special Numbers
There are some types of numeric information that you wouldn't ever want to perform mathematical operations with. 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 might 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 might 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, as shown in Figure 4-6, which converts numbers into common patterns. And lucky you: one of the Type options in the Special number format 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 only provides 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.
|
4.1.4. Custom Formats
As versatile as Excel is, it can't read your mind. There are some situations when you want to format numbers in a specialized way that Excel just doesn't expect. For example, you might 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 2006-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.
4.1.4.1 Creating a custom format
Here's the easiest way to apply a custom format:
-
Select the cells you want to format.
This can include any combination of cells, columns , rows, and so on. To make life easier, make sure the first cell you select contains a value you want to format. That way, you'll be able to use the Format Cells dialog box to preview the effect of your custom format.
Note: Excel stores custom format strings in your workbook file. That means that once you've perfected a format string you like, you can apply it to as many cells as you want.
-
Select Format
Cells, or just right-click the selection, and choose Format Cells. The Format Cells dialog box appears, as shown earlier in Figure 4-2.
-
Choose a format that's similar to the format you want to use.
For example, if you want to apply a custom date format, begin by selecting the Date number format and choosing the appropriate style. If you want to apply a custom currency format, begin by selecting the Currency number format and specifying the appropriate options (like the number of decimal places).
To create the International Pet Adventures employee code, it makes sense to first select the Number format, and then choose 0 decimal places (since the number format you're looking to model0521-1033doesn't use any decimal places).
-
At the bottom of the Category list, click Custom.
Now you'll see a list of different custom number strings. At the top of this list is a highlighted format string that's based on the format you chose in step 3. Now, you just need to modify this string to customize the format. (Make sure you don't accidentally select another format before you click Custom, or you won't end up with the right format string.)
If you're creating the International Pet Adventures employee code, you'll see a 0. This means you can use any number without a decimal place. However, what you really want in this situation is to create an employee number that always starts with 0521- and then has four more digits. You'll specify your new format in the next step.
-
Enter your custom string.
Type your custom string into the box below the Type label; Figure 4-7 shows the custom format string in the Format Cells dialog box. The correct format string for the International Pet Adventures example is as follows:
"0521-"0000
-
This string specifies that all cells that are formatted using this Custom format will begin with the digits 0521- and will then be followed by whatever four numbers are entered into the cell (if no numbers are entered, four zeroes will follow the 0521-). Page Section 4.1.4.2 explains all the ingredients you can use in your custom format.
Figure 4-7. Custom number strings allow you to do almost anything with a number format, but you'll need to spell it out explicitly using the cryptic code Excel provides. In the example shown here, the format string is "0521-"0000. The "0521-" is a fixed string of characters that's added to the beginning of every number. The four zeroes indicate that you need four digits. If you provide a one-, two-, or three-digit number, Excel will add the zeroes needed to make a four-digit number. For example, the number 4 will automatically be displayed as the employee code 0521-0004.
Tip: Remember, you can preview your custom format string in the Sample section of the Format Cells box. As you adjust the format string, the Sample box will show you what the current cell would look like if the format string were applied.
-
Click OK to commit your changes
If the results don't meet with your approval, you can start over again. But this time, skip step 3, because you want to change the current format string rather than replace it with a new format string.
-
To use the Custom format you've created, select one or more cells, pick the Custom format (by right-clicking the cells and choosing Format Cells...), and then select your new Custom format.
Newly created Custom formats are listed in the Custom category, at the bottom of the Type list. If you wanted to use the new International Pet Adventures employee code, click OK after selecting your new format and then begin entering the four digits specific to each employee. For example, if you format a cell with the new Custom format, and you then type 6754 into the cell, you'll see 0521-6754 .
4.1.4.2 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:
-
$ tells Excel to add a currency symbol before the number.
-
#,## tells Excel to use commas to separate thousands.
-
0.00 tells Excel to always include a single digit and two decimal places, no matter what the number is (0.00).
$#,##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.
4.1.4.3 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 (although Excel can automatically insert dates; see Section 10.2 for more on that). 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 4-1. These 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. For example, consider the following format string:
yyyy-mm-dd
Table 4-1. Date and Time Formatting Codes
| Code | Description | Example Value Displayed on Worksheet [1] |
|---|---|---|
| d | The day of the month, from 1 to 31, with the numbers between 1 and 9 appearing without a leading 0. | 7 |
| dd | The day of the month, from 01 to 31 (leading 0 included from 1 to 9). | 07 |
| ddd | A three-letter abbreviation for the day of the week. | Fri |
| dddd | The full name of the day of the week. | Friday |
| m | The number value, from 1 to 12, of the month (no leading 0 used). | 1 |
| mm | The number value, from 01 to 12, of the month (leading 0 used for 01 to 09). | 01 |
| mmm | A three-letter abbreviation for the month. | Jan |
| mmmm | The full name of the month. | January |
| yy | A two-digit abbreviation of the year. | 05 |
| yyyy | The year with all four digits. | 2005 |
| h | The hour, from 0 to 23 (no leading 0 used). | 13 |
| hh | The hour, from 00 to 23 (leading 0 used from 00 to 09). | 13 |
| :m | The minute, from 0 to 59. | 5 |
| :mm | The minute, from 0 to 59 (leading 0 used for 00 to 09). | 05 |
| :s | The second, from 0 to 59 (no leading 0 used). If you want to add tenths or hundredths of a second, follow this with .0 or.00, respectively. For example, :s. | 5 |
| :ss | The second, from 0 to 59 (leading 0 used from 00 to 09). If you want to add tenths or hundredths of a second, follow this with .0 or .00, respectively. | 05 |
| AM/PM | Tells Excel to use a 12-hour clock, including the AM or PM tag. | PM |
| am/pm | Tells Excel to use a 12-hour clock, with an am or pm tag. | pm |
| A/P | Tells Excel to use a 12-hour clock, with an A or P tag. | P |
| a/p | Tells Excel to use a 12-hour clock, with an a or p tag. | P |
| [ ] | Tells Excel that a given time component (hour, minute, or second) shouldn't "roll over." For example, Excel's standard approach is to have seconds become minutes once they hit the 60 mark, and minutes become hours at the 60 mark. Similarly, hours roll over into a new day when they hit 24. But if you don't want this to happenfor example, when tracking total minutes on a CD play listyou could use the format string [h]:[mm]:ss. Thus, if your total play time were 59:59 (59 minutes and 59 seconds), and you added a 3 minute long song, the new total would be 62:59, rather than 1:02:59. |
[1] Assumes the date January 7, 2005, 1:30:05 PM.
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 if you type 1/15/06 or January 15, 2006 in the cellExcel still displays it as 2006-01-15 if that's what your custom format dictates.
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, 2006): 2006-01-15 .
Now if you format the same value with this format string:
"Day" yyyy-mm-dd
you'll see this in your worksheet:
Day 2006-01-15
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 10.
4.1.4.4 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 4-2 shows the different codes you can use. The most important of these 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).
Table 4-2. Number Formatting Codes
| Code | Description | Example |
|---|---|---|
|
| This digit placeholder forces a zero to appear whenever a number is not provided. | 0.000 causes .3 to be displayed as 0.300. |
| ? | This digit placeholder forces a space to appear whenever a number is not provided. | ?.??? causes .3 to be displayed as " .3 " (quotations used to indicate spaces). |
| # | This digit placeholder indicates where a number can be placed, but doesn't automatically insert a zero or a space if there isn't a number in this position. You can use this symbol to set the precision of decimal values or to indicate where commas should go. | ### causes .3 to be displayed as .3. #,### causes 9999 to be displayed as 9,999. |
| . | The period, or decimal point, determines where the decimal place will go. It's used in conjunction with the digit placeholders 0, ?, and #. | #.## causes 1.23456 to be truncated to 1.23. |
| , | The comma is used to cause the thousands separator to appear. It's used with the digit placeholders. It can also be used to scale a number. For example, if you place one comma at the end of a format string, Excel will display the number rounded to thousands. Add two commas to the end, and Excel will only display the number in millions (and so on). | #,### causes 3000 to be displayed as 3,000. #,###,###, displays 12345678 as 123,457 (rounded to thousands). |
| / | The frontslash formats a number as a fraction. You use this symbol in conjunction with the digit placeholders ? and # to indicate the number of digits you want in your fraction. | ?/? causes 1.75 to be displayed as 7/4, while # ?/? formats the same number as 1 3/4. |
| E+ | This code formats numbers using scientific notation. You use this symbol in conjunction with the digit placeholders 0, ?, and #. | #.## E+## means 12345 will be displayed as 1.23 E+4. |
| [ color ] | Applies a specified color to the text that follows the closing bracket . The color name goes inside the square brackets. Excel supports eight colors: [black], [blue], [cyan], [green], [ magenta ] [red], [white], and [yellow]. | [red]#,### displays the number that follows the brackets in red lettering. |
| _ | The underscore character, when followed by any other character, inserts a space equal to the width of that other character. This code is occasionally used when aligning complex formatting codes. | _W causes a space as wide as the capital letter W to be inserted. |
| * | The asterisk, when followed by any other character, inserts that other character in a cell until the cell is filled. | #,###*- causes 9999 to be displayed as 9,999- (with the dashes appearing until the cell is filled). |
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 reasondoing so would mangle your numbers beyond recognition.
-
Use 0 to indicate a number that must be wherever the 0 is placedif it's not, Excel automatically inserts a 0. For example, the format string 0.00 would display the number .3 as 0.30. And the format string 00.00 would format the same value as 00.30.
-
The question mark (?) works similarly, but it turns into spaces instead of zeroes, ensuring that multiple numbers wind up aligned in a column. For example, ??.?? displays the number 3 as " 3 " (without the quote marks).
-
The # symbol lets you indicate where a number can exist but doesn't have to exist. For example, the format string 0.0# indicates that the first digit before the decimal place and the first digit after the decimal place must be present (that's what the zeroes tell Excel). However, the second number after the decimal place is optional. With this format string, Excel rounds additional digits starting with the third decimal place. Thus, this format string will display the value .3 as 0.3, .34 as 0.34, and .356 as 0.36. You can also use the # symbol to indicate where commas should go, as in the format string #,##.00. This string will display the value 3639 as 3,639.00.
Note: Remember, Custom format strings control how values are displayed. They aren't meant to control what values a person is allowed to enter in a cell. To set rules for allowed data, you need a different featuredata validation, which is described on Section 15.4.1.
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 is 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 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 (e.g., 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. The result is that you can add information to the cell (and see it in the Formula bar), but it won't appear on the worksheet or in your printouts.
4.1.4.5 Text format strings
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 might 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:
"NOTE: "@
and then you type Transfer payment into the cell, Excel will display it as NOTE: Transfer payment .
EAN: 2147483647
Pages: 185