Section 5.1. Formatting Cell Values


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.

Figure 5-1. This worksheet shows how different formatting can affect the appearance of the same data. Each of the cells B2, B3, and B4 contains the exact same number: 5.18518518518519. In the formula bar, Excel always displays the exact number it's storing, as you see here with cell B2. However, in the worksheet itself, each cell's appearance differs depending on how you've formatted the cell.


To format a cell's value, follow these steps:

  1. Select the cells you want to format .

    You can apply formatting to individual cells or 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 Excel applies number formats only 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.
  2. Select Home Cells Format Format Cells, or just right-click the selection, and then choose Format Cells .

    In either case, the Format Cells dialog box appears, as shown in Figure 5-2.

    Figure 5-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. You can use the Alignment, Font, Border, and Fill tabs to control the cell's appearance.


  3. Set the format options .

    The Number tab's options let you choose how Excel translates the cell value into a display value. For example, you can change the number of decimal places that Excel uses to show the number. (Number formatting choices are covered in much more detail in the next section, "Formatting Numbers.")

    Most of the Format Cells dialog box's other tabs 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 comes along for the ride if you copy a cell, 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 formatting is to highlight the cell and select Home Editing Clear Clear Formats. This command removes the formatting, restoring the cell to its original, General number format (which youll learn more about next), but it doesn't remove any of the cell's content.
  4. Click OK .

    Excel applies your formatting changes and changes the appearance of the selected cells accordingly .

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). 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.

Figure 5-3. The ribbon's Home tab gives you a quick way to open the Format Cells dialog box from three different spots: the Font, the Alignment, or the Number tab.


Figure 5-4. You can learn about the different number formats by selecting a cell that already has a number in it, and then choosing a new number format from the Category list (Home Cells Format Format Cells). When you do so, Excel uses the Format Cells dialog box to show how itll display the number if you apply that format. In this example, you see that the cell value, 5.18518518518519, will appear as 5.19E+00, which is scientific notation with two decimal places.


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 the number's got more decimal places than Excel can display, then it leaves out the ones that don't fit. (It rounds up the last displayed digit, when appropriate). If you change a column width, then 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 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.

Figure 5-5. Each column contains the same list of numbers. Although this worksheet shows an example for each number format (except dates and times), it doesn't show all your options. Each number format has its own settings (like the number of decimal places) that affect how Excel displays data.


5.1.1.1. General

The General format is Excel's standard number format; it applies no special formatting other than the basic rules described in Section 5.1.1.2. 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.

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, then 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 0s and 1s.) When deciding how to format a cell, you may 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 is simply 0.3. In both cases, Excel still keeps the full 15 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's 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.

To change what Excel does, select Office button Excel Options, chose the Advanced section, and then scroll down to the "When calculating this workbook group of settings. A "Set precision as displayed" checkbox appears. When you turn on this checkbox, Excel adjusts all the values in your current spreadsheet so that the stored value matches the displayed value. Unfortunately, with this choice, you'll get less precise data. For example, if you use this option with the 1/3 example, Excel stores the display value 0.3 instead of 0.333333333333333. Because you can't reverse this change, Excel warns you and asks for a final confirmation when you try to apply the "Precision as displayed" setting.


5.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.

5.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 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.

5.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 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.

5.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, then 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).

5.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 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, then 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, 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.
5.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 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). 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.

5.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).

TIMESAVING TIP
Shortcuts in the Ribbon

You don't need to waste hours jumping between your worksheet and the Format Cells dialog box. The ribbon gets you to some of the most commonly used number formats in the Home Number section.

The Home Number sections most prominent part is the drop-down list of number formats (Figure 5-6). Just underneath are buttons that let you apply one of the three most common formats: Accounting, Percent, or Number. Just to the right are two buttons that let you increase or decrease the number of decimal places that you see at once.

One of the neatest features is the list of currency options for the Accounting button. If you click the drop-down arrow on the Accounting button (which looks like a dollar sign), then you see a list with different currency symbols you can choose (like Pounds, Euros, Chinese Yuan, and so on). But if you click the other portion of the Accounting button (not the arrow), then you get the currency symbol that's appropriate based on your computer's regional settings.


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.

Figure 5-6. The all-around quickest way to apply a number format is to select some cells, and then, from the number format list, choose an option. Best of all, you see a small preview of what the value in the first selected cell will look like if you apply the format.


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:

  • Formats that take the regional settings of the spreadsheet viewer's computer into account . With these formats, dates display differently depending on the computer that's running Excel. This choice is a good one 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.

    Figure 5-7. Excel gives you dozens of different ways to format dates and times. You can choose between formats that modify the date's appearance depending on the regional settings of the computer viewing the Excel file, or you can choose a fixed date format. When using a fixed date format, you don't have to stick to the U.S. standard. Instead, choose the appropriate region from the Locale list box. Each locale provides its own set of customized date formats.


  • 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, 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. 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).

Figure 5-8. Special number formats are ideal for formatting sequences of digits into a common pattern. For example, in the Type list, if you choose Phone Number, then Excel converts the sequence of digits 5551234567 into the proper phone number style(555) 123-4567with no extra work required on your part.




Excel 2007 for Starters. The Missing Manual
Excel 2007 for Starters. The Missing Manual
ISBN: 596528329
EAN: N/A
Year: 2004
Pages: 75

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net