This site will look much better in a browser that supports web standards, but it is accessible to any browser or Internet device.

Skip Navigation skip menu and banner
University of Wyoming

Excel
Formatting the Sales Spreadsheet

[pdf version]

Introduction

Auto Format

Percentages

Number Format

Column Width

Insert a Row

Change the Row Width

Delete a Column

Centering Titles

Fonts & Colors

Backgrounds

Remove Borders

Note:  Download the Sales spreadsheet file used in this example.

Introduction

Format a spreadsheet to make it easier to read when it is printed.

[top]

Auto Format

Excel has several formats available to use that quickly format a worksheet.  Changes can be made to individual items when auto format is used.

  1. Click and hold the left mouse button in cell A1.
  2. Drag it to cell H20, and release the mouse button.  This highlights the section of the worksheet to be formatted.
  3. Click Format > AutoFormat.
  4. Highlight Accounting 1.
  5. Click OK.

[top]

Percentages

Numbers in individual cells or a range of cells can be formatted as percentages.  Reformat the numbers in cells D5 to E9 as percentages rather than dollar amounts.

  1. Click and hold the left mouse button in cell D5.
  2. Drag it to cell E9, and release the mouse button.
  3. Click Format > Cells.
  4. Click the Number tab.
  5. Select Percentage.  Review the sample to be sure it is in the desired format.
  6. The number of decimal places can be changed.  Click the down arrow twice to change it to 0.
  7. Click OK.

[top]

Number Format

The sales and expense figures can be displayed as whole numbers.  Also, the state sales figures should not display a dollar sign.

  1. Click and hold the left mouse button in cell B5.
  2. Drag it to cell C20, and release the mouse button.
  3. Click Format > Cells.
  4. Click the Number tab.
  5. Click Accounting.
  6. Change the decimal places to 0.
  7. Click the Symbol down arrow.
  8. Click the $ sign.
  9. Click OK.

Next, change the state sales figures so a dollar sign is not displayed.

  1. Click and hold the left mouse button in cell B5.
  2. Drag it to cell C8, and release the mouse button.
  3. Click Format > Cells.
  4. Click the Number tab.
  5. Click Accounting.
  6. Change the decimal places to 0.
  7. Click the Symbol down arrow.
  8. Click None.
  9. Click OK.

[top]

Column Width

The columns appear to be too wide due to the formatting changes.  The column width can be changed to fit the data.

  1. Click the A column heading.
  2. Press and hold the Shift key.
  3. Click the H column heading.
  4. Click Format > Column > AutoFit Selection.  The column width adjusts.
  5. Click cell A1.

When cells show #####, the column is not wide enough to display the information.  Move the cursor to the line between the column headings, and double click.  The column width adjusts automatically.

[top]

Insert a Row

A row needs to be inserted above the contest information.

  1. Click cell A17.
  2. Click Insert > Row.

[top]

Change the Row Width

  1. Click cell A18.
  2. Click Format > Row > AutoFit.

[top]

Delete a Column

There are two blank columns between the last column of data and the date.  Delete one of the columns.

  1. Click cell G1.
  2. Click Edit > Delete.
  3. Click the Entire column radio button.
  4. Click OK.

[top]

Centering Titles

The company name and worksheet title are centered over the worksheet.

  1. Click and hold the left mouse button in cell A1.
  2. Drag it down to cell F2, and release the mouse button.
  3. Click Format > Cells.
  4. Click the Alignment tab.
  5. Click the Horizontal down arrow under Text alignment.
  6. Click Center Across Selection.
  7. Click OK.

The titles are centered across the body of the worksheet.  Click cell A1.  Look at the information in the formula bar.  Even though the information appears to be in cells B1 and C1, it actually resides in cell A1.

[top]

Fonts and Colors

The font size and color can be changed.  The font size and color of the titles are changed.

  1. Click cell A1.
  2. Click the Font Size down arrow.
  3. Highlight 14.
  4. Click the Font Color down arrow.
  5. Highlight Blue.
  6. Click cell A2.
  7. Click the Font color icon.  The subtitle changes color.

[top]

Backgrounds

A background color can be added to any cell in the worksheet.

  1. Select the range A1 to F2.
  2. Click Format > Cells.
  3. Click the Patterns tab.
  4. Select a color under Cell shading.
  5. Click OK.

[top]

Remove Borders

The sections for average and the contents have underlines without figures.  These can be removed.

  1. Highlight the range C14 to G21.
  2. Click Format > Cells.
  3. Click the Border tab.
  4. Click the None icon in the Presets section.
  5. Click OK.

Repeat this process for the other cells with underlines.

[top]  • Back • Home • Up • Next •