Key Applications Module Lesson 17 Organizing Worksheets

1
Key Applications ModuleLesson 17 Organizing
Worksheets

• Computer Literacy BASICS

2
Objectives

• Merge cells.
• Fill a data series in adjacent cells.
• Use AutoFormat to format a worksheet.
• Create multiple worksheets.
• Sort data in a worksheet.
• Change the page setup.
• Add headers and footers to a worksheet.
• Preview and print a worksheet.

3
Vocabulary

• Ascending order
• Descending order
• Gridlines
• Header row
• Merge

4
Merging Cells

• You can join two or more adjacent cells together.
• This allows you to create larger cells to
  accommodate a head that spans several columns,
  headers or footers, or long text fields.
• First select the cells to be merged.
• Click the Merge and Center button on the Standard
  toolbar.

5
Entering Data with AutoFill

• You can use the AutoFill feature to fill in a
  series of numbers and dates.
• To do so, a pattern must be established in the
  initially selected cells.
• When you drag the fill handle, the pattern is
  continued.
• When you drag the handle down or to the right,
  the series increases.
• When you drag the handle up or to the left, the
  series decreases.

6
AutoFill Options

• When you point to the AutoFill Options button
  (below the fill handle), the button expands to
  show a down arrow.
• Click the down arrow to display a shortcut menu
  with options to copy the content of the cells,
  fill the selected cells with formatting only, or
  fill the cells without the formatting.

The AutoFill Options shortcut menu
7
Applying AutoFormats

• Excel provides several predefined worksheet
  formats that can be used to give your worksheet a
  professional look.
• AutoFormats include font styles, colors, borders,
  shading, and other features.

8
Applying AutoFormats (cont.)

• To apply an AutoFormat
• Select the cells you want to format.
• Choose the AutoFormat option on the Format menu.
• Scroll through the available styles and select
  one you like.
• Click OK to apply the AutoFormat to your
  worksheet.
• You can modify formats after they are applied.

9
The AutoFormat Dialog Box
You can preview AutoFormat styles in the dialog
box.
10
Creating Multiple Worksheets

• When you open a new document in Excel, it
  automatically contains three worksheets.
• Excel documents are called workbooks, and each
  workbook contains worksheets.
• A tab for each worksheet is visible at the bottom
  of the screen.
• Click on a tab to switch to that worksheet.
• Double-click the tab to enter a new name.
• Insert additional worksheets using the Worksheet
  option on the Insert menu.

Worksheet tabs
11
Sorting Data

• To sort Excel data, select a column to sort on.
• The data in all rows will move accordingly with
  the sorted field.
• Excel can determine whether your worksheet has a
  header row and will not include that row in the
  sort process.
• Click the Sort Ascending button to sort the data
  in ascending order, from A to Z or smallest to
  largest.
• Click the Sort Descending button to sort the data
  in descending order, from Z to A or largest to
  smallest.

12
The Sort Dialog Box

• You can base a sort on data in up to three
  different columns in a worksheet.
• To sort a worksheet by multiple criteria, open
  the Sort dialog box by selecting Sort on the Data
  menu.

13
Using Print Preview

• Excel has a Print Preview mode that you can
  access from the Print Preview button on the
  Standard toolbar.
• This allows you to see the worksheet as it will
  look when it is printed and to look for errors
  before printing.
• Use the Page Setup dialog box to fix errors if
  you find problems with the document format.

14
The Page Setup Dialog Box

• Select the Page Setup option on the File menu to
  open the Page Setup dialog box.
• Or you can click the Setup button in the Print
  Preview window to open the dialog box.

Click the Page tab in the dialog box if necessary.
15
Changing the Page Setup

• Sometimes a column or two does not fit on the
  same page as the rest of the data when you
  preview a worksheet.
• You can use the Page Setup options to adjust the
  layout
• Change the orientation to Landscape to make the
  paper wider than it is tall.
• Use the Fit to command to scale the worksheet up
  or down to fit on the number of pages you
  designate.

16
Changing Margins
Click the Margins tab in the Page Setup dialog
box to adjust the amount of white space around a
worksheet when it prints.
17
Changing Alignment

• Basic cell content alignmentleft, center, and
  rightcan be applied by selecting the data and
  clicking the appropriate button on the Formatting
  toolbar.
• Other alignment options available on the
  Alignment tab of the Format Cells dialog box
  include
• Center cell content across selected merged cells.
• Orient cell contents to set at an angle.
• Adjust the vertical alignment of cell contents to
  top, bottom, justified, or distributed.

18
Aligning and Wrapping Cell Data

• You also can select an option on the Alignment
  tab of the Format Cells dialog box to shrink the
  content to fit within the cell.
• Or select the text wrap option to wrap to new
  lines within a cell if the contents do not fit on
  one line.

19
Gridlines in a Worksheet

• Gridlines that appear in your worksheet on the
  screen do not print by default.
• However, you can add gridlines to a printed
  worksheet or remove them from your on-screen
  worksheet if you prefer.

20
Gridlines in a Worksheet (cont.)

• To remove gridlines from the on-screen worksheet
• Select Options on the Tools menu and then choose
  the View tab.
• In the Window options section, deselect Gridlines
  and then click OK.
• To add gridlines to the printed document
• Open the Page Setup dialog box and choose the
  Sheet tab.
• Select Gridlines in the Print section.

21
Headers and Footers

• A header is information that appears at the top
  of every page when the worksheet is printed.
• A footer is information that prints at the bottom
  of every page.
• Headers and footers are only visible in Print
  Preview mode or when the worksheet is actually
  printed.

22
The Header and Footer Tab

• To add a header or footer, select the
  Header/Footer tab in the Page Setup dialog box.
• Headers and footers are used to provide
  descriptive text such as the date the worksheet
  was printed, the name of the person or company
  who created the worksheet, or the filename of the
  workbook.

Header text
Footer text
23
Printing a Worksheet

• Click the Print button on the Standard toolbar to
  print the worksheet with default settings.
• Open the Print dialog box by selecting Print on
  the File menu to
• Print the entire workbook.
• Print a selected area of the worksheet.
• Print multiple copies.
• Select a different printer.

24
The Print Dialog Box
25
Worksheet Page Breaks

• When a worksheet is more than one page in length,
  Excel determines where page breaks should occur.
• If you dont like where Excel sets them, you can
  create your own page break by dragging the page
  break indicator to a new location in the
  worksheet.
• You can also insert a manual page break by
  selecting Page Break on the Insert menu.

26
Pausing or Canceling a Print Job

• To check the progress of a print job,
  double-click the Printer icon that appears on the
  taskbar when a job is printing.
• The print queue, a list of print jobs currently
  in progress, will display.

27
Pausing or Canceling a Print Job (cont.)

• To cancel a print job, select the document name
  and then click Cancel Printing on the Document
  menu.
• Select the Pause Printing command on the Document
  menu to stop the print job temporarily.
• To restart printing, deselect the Pause Printing
  command.

28
Summary

• If you want text to span across several rows or
  columns, you can merge multiple cells into a
  single cell.
• The AutoFill feature can recognize a pattern in
  numbers or text and fill a series based on the
  pattern.
• You can use AutoFill to fill cells with or
  without formatting from the source cells.

29
Summary (cont.)

• Applying AutoFormats can give your worksheet a
  professional look quickly and can make reading
  the data easier.
• You can delete or add one or several worksheets
  to a workbook, and you can rename each worksheet.

30
Summary (cont.)

• The Sort feature provides options for organizing
  worksheet data numerically or alphabetically. You
  can sort the data based on a single column, or
  you can sort the data based on multiple criteria.
• The Alignment tab of the Format Cells dialog box
  offers advanced alignment options for cell
  contents, including wrapping text to new lines
  and orienting text vertically or at an angle.

31
Summary (cont.)

• Before you print, you can preview the worksheet
  on the screen to see what it will look like when
  it is printed.
• You can change the page orientation or use the
  Fit to feature to fit all of the data on one page.

32
Summary (cont.)

• Gridlines normally appear on screen in worksheets
  but do not appear when the worksheets are
  printed, but you can hide the gridlines on screen
  or have the gridlines appear in a printed
  worksheet by selecting appropriate options.
• Headers and footers can be added to worksheets to
  provide information, such as the source and date
  of the data.

33
Summary (cont.)

• Inserting page breaks manually or selecting part
  of a worksheet as a print area control the
  appearance of the print output.
• You can choose to print the active worksheet
  only, or you can choose to print all worksheets
  in the workbook.