Microsoft Excel 2003/2004

Formatting Your Worksheets

It is easy to format your Excel worksheets, because formatting cells is similar to formatting text in Word. Cells can be formatted to a particular style you choose, such as formatting all numbers consistently. You can also copy the formatting of a particular cell without affecting the information within the cell. Once you have formatted your worksheet(s), for instructions on adjusting rows and columns, refer to Working with Rows and Columns.

Basic Design Elements
AutoFormat
Formatting Fonts
Formatting Numbers
Merging and Centering Text
Wrapping Text
Copying Cell Formatting
Clearing Cell Formatting

Basic Design Elements

Working with large amounts of data can be challenging for a variety of reasons. One of the ways to make data management easier is to organize your workbook into different worksheets. Planning the design of your workbook from the start can make it easier to work with, especially when the workbook gets larger or contains several sections. For example, worksheets within a faculty gradebook can be color coded (text, cells, or background) to differentiate among class sections; even the worksheet tabs can be color coded. Other things you can do to make organizing your data more efficient include the following:

Format the decimal points to align correctly.
Only use the dollar sign for the first item in a monetary list and for totals.
Improve readability by putting subtotals in separate columns.
Distinguish elements of each worksheet by color coding the cells, text, sheet tab, and background

AutoFormat

To make formatting your worksheets a little easier, Excel has several "preset" formats available. With these preset formats, you can select all of the characteristics or only some (e.g., just the borders). Either way, it can be an efficient way to start formatting your worksheet. Once you apply the formatting with AutoFormat, you can still make adjustments to the cells.

Select the cell(s) to be formatted
From the Format menu, select AutoFormat...
The AutoFormat dialog box appears.
Select the desired AutoFormatting option
OPTIONAL: To apply only some of the characteristics of a particular AutoFormat,
1. Click OPTIONS...
  The AutoFormat dialog box expands.
2. Under Formats to apply, select/deselect the desired options
Click OK

Formatting Fonts

Two options for formatting fonts in Excel are the Formatting toolbar option and the Format menu option. The Format Cells dialog box contains many other style choices not available on the Formatting toolbar.

Formatting Fonts: Formatting Toolbar

Windows:

Select the cell(s) to be formatted
On the Formatting toolbar, click the desired formatting option
NOTES:
Holding the pointer over a toolbar button for a short period displays a description of that option.
If the desired option is not available on the Formatting toolbar, refer to Formatting Font: Menu Option.

Formatting Fonts: Formatting Palette

Macintosh:

Formatting Palette Expanded Section

Select the cell(s) to be formatted
In the Formatting Palette, in the Font section, click the desired formatting option
HINT: If the Formatting Palette is not displayed, on the Standard toolbar, click FORMATTING PALETTE
NOTES:
Holding the pointer over a toolbar button for a short period displays a description of that option.
If the desired option is not available on the Formatting toolbar, refer to Formatting Font: Menu Option.

Formatting Fonts: Menu Option

In addition to font choices, the Format Cells dialog box contains many other style choices that are not available on the Formatting toolbar.

Select the cell(s) to be formatted
From the Format menu, select Cells…
The Format Cells dialog box appears.
Select the Font tab
Make the desired changes
Click OK

Formatting Numbers

Formatting cells for numbers can be very helpful and time-saving. You can format cells to display time, currency, numbers, etc. in the desired style.

Formatting Numbers: Toolbar Option

Select the cell(s) to be formatted
On the Formatting toolbar, click the desired formatting option
NOTES:
Holding the pointer over a toolbar button for a short period displays a description of that option.
If the desired option is not available on the Formatting toolbar, refer to Formatting Numbers: Menu Option.

Formatting Numbers: Menu Option

Select the cell(s) to be formatted
From the Format menu, select Cells…
The Format Cells dialog box appears.
Select the Number tab
NOTE: Under Category, you will see some common choices for formatting numbers.
From the Category scroll box, select the appropriate number category
Based on your selection, additional options appear.
Make the appropriate choices
Click OK

Four-Digit Year Options:

Select the cell(s) to be formatted
From the Format menu, select Cells...
The Format Cells dialog box appears.
Select the Number tab
From the Category scroll box, select Date
From the Type scroll box, select the desired four-digit year option
Click OK

Merging and Centering Text

Merging and centering text in your Excel document can add a professional appearance. Merging and centering is useful for titles and headings so they are not broken up into different cells or displayed with inappropriate alignment.

Merging and Centering Text: Toolbar Option

Type the desired text in the first cell of the group to be merged
Select the text and a cell from each column you want merged
EXAMPLE: To center across columns A through D in row 2 of the worksheet, select cells A2, B2, C2, and D2
On the Formatting toolbar, click MERGE AND CENTER

Merging and Centering Text: Menu Option

Type the desired text in the first cell of the group to be merged
Select the text and a cell from each column you want merged
EXAMPLE: To center across columns A through D in row 2 of the worksheet, select cells A2, B2, C2, and D2
From the Format menu, select Cells…
The Format Cells dialog box appears.
Select the Alignment tab
From the Horizontal pull-down list, select Center Across Selection
Under Text control, select Merge cells
Click OK

Unmerging Text: Toolbar Option

Select the merged cell
On the Formatting toolbar, click MERGE AND CENTER

Unmerging Text: Menu Option

Select the merged cell
From the Format menu, select Cells...
The Format Cells dialog box appears.
Select the Alignment tab
From the Horizontal pull-down list, select General
Under Text control, deselect Merge cells
Click OK

Wrapping Text

If you have text that appears in a single cell and you want to increase the height of that cell to accommodate all of the words, you can use the Wrap text option.
Example of Wrapped and Unwrapped text

Wrapping Text: Menu Option

Select the cells to which Wrap text will be applied
From the Format menu, select Cells…
The Format Cells dialog box appears.
Select the Alignment tab
Under Text control, select Wrap text
Click OK
The text is wrapped.
NOTE: To display all of the text, it may be necessary to adjust row height.

Wrapping Text: Keyboard Option

Select the cells
In the Formula Bar text box, place the insertion point where Wrap text will be applied
Windows: Press [Alt] + [Enter]
Macintosh: Press [control] + [option] + [return]
The text is wrapped.

To unwrap text:

Select the cell containing wrapped text
From the Format menu, select Cells...
The Format Cells dialog box appears.
Under Text control, deselect Wrap text
Click OK
The text is unwrapped.

Copying Cell Formatting

The typical Copy and Paste function will copy the information (e.g., text, formula) and the formatting of the cell(s). If you want to copy only the formatting, you can use the Painter option. This will format the destination cell the same as the source cell without changing the content. For more information, refer to Formatting with the Painter.

Clearing Cell Formatting

If you want to remove all formatting from a cell but leave the contents (e.g., text, value, formulas), use the following command.

Select the cell(s) containing the formatting to be cleared
From the Edit menu, select Clear » Formats