Formatting Cells & Worksheets

Formatting Cells & Worksheets

Microsoft Excel 97 offers a wide range of formatting options, several of them new in this version. Some of the most annoying problems users encounter result from improperly formatted data. Using proper formatting will prevent a lot of frustration when you're trying to get data to display properly or a bar chart to behave!

Using Excel 97's formatting features also lets you add to the effectiveness of your worksheets by calling attention to important areas or highlighting changes in your data (see Conditional Formatting).

Unless otherwise noted, cell formatting is accessed by right-clicking in a selected cell or range and selecting Format Cells... from the context menu or if you prefer, select Cells... from the Format menu and then select the appropriate tab.

The Number Tab

Setting formats correctly is one key to making your data behave the way you want it to. If you've ever lost the leading zero from a zip code, or entered a date that the program decided was a math problem, you know how frustrating it is to enter data into incorrectly formatted cells.

The Number tab includes a variety of format options, many of which are self-explanatory. Once you understand the principles, you'll be able to make the correct formatting decisions. There are formats for Text, Date and Time, Currency, and several others.

The General Format

General is the default format setting. If you don't select a different formal, the defaults will be used. Text will be left-aligned and numbers will be right-aligned. The reason that the leading zeros on some zip codes disappear is because when Excel sees a number that uses the default settings, it assumes that the number is meant to be a number, and numbers are displayed without leading zeros!

For zip codes, serial numbers, product ID numbers, use either one of the special formats discussed below, or format the cells as Text. Your leading zeros will stay put!

The Number Format

The Number format should be used for any cells containing numbers that will be used in calculations. To select a number format for a range of cells, highlight the range, then click on Number. Enter the number of decimal places you want to display.

While Excel will use 15 digits in calculations, you can specify the number of places you want to display. This can cause some calculations to appear to be incorrect. For example, in calculating currency, you use the full 15 places of precision, but display only 2 decimal places. The longer answer may include .005, which will be rounded to .01 in the display. If you add 2 numbers together, each of which contains .01, the decimal part of the total will still be .01 because the actual numbers being added are .005 + .005, which equals .01! Is that confusing? Probably.

You can select to use the number as displayed for calculations (in the Tools menu, select Options; on the Calculation tab, select Precision as displayed). Be careful when you use this option. Once you select it, the normal 15 places of precision will be gone, and the rest of the calculations will be done with only the number of places that you specify. You won't be able to reverse the process.

You can also select whether or not to use a 1000's separator. A number without a separator will display as 54778, for example. With a separator, the number will appear as 54,778.

Select a format for negative numbers by clicking on one of the choices in the lower portion of the dialog box on the right. Negative numbers can be displayed either in black with a minus sign or in parentheses (the first and third choices) or in red - with or without parentheses (the second and fourth examples in the list).

The Currency and Accounting Formats

The Currency and Accounting formats give you the opportunity to automatically include currency symbols (e.g., the dollar sign) for various currencies and to specify the number of decimal places to display. The Currency format also gives you a choice of ways to represent negative numbers.

Date & Time Formats

Select a Date format to make sure that dates display the way you want them to (and to make sure that Excel doesn't mistake 8/14/97 for a division problem, which only happens if you enter a date (8/14/97) in cells formatted for numbers.)

In addition to all the options in the Date and Time format lists (U.S., 12- and 24-hour, European formats, etc.) you can create your own or customize the ones Excel provides. See Custom later in this document.

The Percentage, Fraction, & Scientific Formats

In the Percentage format, select the number of decimal places to use; Excel will provide the percentage signs. The Fraction format allows you to actually type fractions into a cell, and then displays them as fractions! And Scientific lets you display numbers in scientific notation.

The Text Format

The Text format does not contain a choice of formats. Selecting Text simply tells Excel to accept the entry as a text entry (which means that it won't get rid of the leading zeros in your zip codes!).

NOTE: Make sure that you don't you the Text format on numbers that you may later want to use in a chart or for calculations. One user got tired of losing all those leading zeros in her zip codes and formatted the entire worksheet as text. When she tried to chart some data, all the values showed up as zero. I got an emergency help call: "What's wrong with this stupid program???" Well, you just can't chart text, that's all!

Special Formats

The Special category has formats for social security numbers, zip codes, long zip codes, and phone numbers. When you use these formats, the program automatically inserts dashes, slashes, or parentheses; all you have to do is type in the numbers!

Social Security Number
543-21-4132
Enter: 543214132

Zip Code + 4
43230-2676
Enter: 432302676

Phone Number
(614) 441-2071
Enter: 6144412071

Note: the formatting will be applied after you press [Tab] or [Enter].

Custom Formats

Most formats can be customized by selecting Custom, then modifying one of the existing choices. For example, if you want numbers to always display a leading zero and 3 decimal places (0.123), select the 0.00 format, then modify it by typing an extra zero so that it becomes 0.000.

Another choice - #,##0 - will display numbers with a thousands separator and no decimal places.

There are several different formats in the Date category. However, if you prefer to show all four numbers of the year, you'll have to modify one of them in the Custom window. Scroll down until you see the format closest to the one you want to use. For example, m/d/yy will display the date as 7/4/97. To modify it to display all four digits of the year, add two y's to the format: m/d/yyyy.

Alignment Tab

Horizontal Alignment: By default, Excel left aligns text and right aligns numbers, dates, and times. The logical values True and False are centered.

1. Select the cells to be formatted.
    
2. On the Alignment tab, select General to align the selected cells using the default settings. You can change the alignment for any cells without changing the data type that they contain.

Other choices from the drop-down list are:

• Left: Aligns cell contents to the left. If you want to leave some extra space between the cell border and the contents, enter an indent amount in the Indent field. Indents are measured in character spaces based on the standard font and font size.
   
• Center: Centers the contents of the cell.
   
• Right: Right-aligns the contents of the cell.
   
• Fill: Repeats the characters in the left-most cell of a selected range of empty cells across the entire range. Useful for producing interesting borders to separate sections of a worksheet or for decorative purposes.
   
• Justify: Wraps text within the cell and aligns it evenly between the right and left cell borders. To use the Justify command, you must turn on Wrap by placing a check next to it in the middle portion of the screen under Text Control.
   
• Center across selection Centers the contents of the left-most cell across the selected range. The other cells in the range must be empty.

The Align Left, Center, Align Right, and Center across selection commands can also be accessed by clicking the appropriate buttons on the Formatting toolbar.

Horizontal Alignment:: By default, data is aligned at the bottom of the cells. To change the effect or leave more 'white space' between your data and the top and bottom cell borders, select Center or Top from the drop-down list.

NOTE: Center text in a double-height row instead of adding an extra row when you want your heading to stand out. If you add extra rows (just for visual effects!) you'll probably run into problems later on when you want to sort or filter data, or use some of Excel's other advanced features.

Wrap: Wraps the text without justifying it.

Shrink to Fit: Use this when you have long entries in a column that you don't want to widen. You can apply this command to a single cell, a range, or a whole column.

Merge Cells: Select two or more cells (horizontally or vertically) then click next to Merge Cells to create a single cell from the selected range.

New In Excel 97: Text Orientation

While previous versions of Excel allowed you to align text vertically, the '97 version allows to change the orientation to an exact angle on a 180 degree arc.

1. Select the cells whose orientation you want to change.
    
2. Right-click in the selected cells and select Format Cells... from the context menu, then click on the Alignment tab.
    
3. Either enter the number of degrees to rotate the text in the Degrees field or click on the angle in the left pane under Orientation.