facebook google plus twitter
Webucator's Free Introduction to Microsoft Excel 2013 Tutorial

Lesson: Finalizing Microsoft Excel Worksheets

Welcome to our free Introduction to Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Introduction to Microsoft Excel 2013 Training course.

In this lesson, you will learn to finalize your worksheets.

Lesson Goals

  • Set margins in Microsoft Excel worksheets.
  • Set the page orientation to portrait or landscape.
  • Set the print area in a worksheet.
  • Scale a worksheet so that all data prints on one page.
  • Add headers and footers to a worksheet.

Setting Margins

You can increase and decrease margins in Microsoft Excel worksheets to change the amount of white space that shows up between your data and the edge of the page when you print. By default, top and bottom margins are .75 inches, side margins are .7 inches, and header and footer margins are .3 inches.

To set the margins in a worksheet:

  1. On the PAGE LAYOUT tab, in the Page Setup group, click the Margins command:Margins in Page Setup Group of Page Layout Tab
  2. Select from the options available (Last Custom Setting, Normal, Wide, or Narrow) or select Custom Margins to set your own margins:Margin Options
  3. If you select Custom Margins, you will be presented with the Page Setup dialog box, where you can set all margins. Note that on the Margins tab of the Page Setup dialog box, you also have the option to center your data horizontally or vertically on the page. After setting your margins, click OK:Page Setup Dialog Box

Setting Page Orientation

You can choose to orient your pages vertically (Portrait), which is the default, or horizontally (Landscape). To set the page orientation:

  1. On the PAGE LAYOUT tab, in the Page Setup group, click the Orientation command:Orientation in Page Setup Group of Page Layout Tab
  2. Select Portrait or Landscape:Orientation Choices

Setting the Print Area

Often times it is desirable to print only a section of a worksheet. To do this, you simply need to set this section as the print area. To set the Print Area:

  1. Select the cells you wish to print.
  2. On the PAGE LAYOUT tab, in the Page Setup group, click the Print Area command:Print Area in Page Setup Group of Page Layout Tab
  3. Select Set Print Area:Set Print Area
  4. To clear the Print Area, select any cell in the worksheet and then click Print Area and Clear Print Area:Clear Print Area

Print Scaling (Fit Sheet on One Page)

Often times it is desirable to print a whole worksheet on one page. Fortunately, there is an easy way to do this (you don't have to play with font sizes, column and row sizes, etc.).

To print a worksheet on one page:

  1. On the PAGE LAYOUT tab, in the Scale to Fit group, set the Width and Height to 1 page:Print Scaling

Print Headings on Each Page/Repeating Headers and Footers

If your printout spans multiple pages, you may want to have column headers and/or row titles be repeated at the top and left of each page.

To print headings on each page:

  1. On the PAGE LAYOUT tab, from the Page Setup group, select Print Titles. Print Titles in Page Setup Group of Page Layout Tab
  2. In the Page Setup dialog box, on the Sheet tab, under Print titles, type the references of the rows and columns that you want to appear, respectively, in the Rows to repeat at top and Columns to repeat at left options and click OK. Page Setup Dialog Box

Headers and Footers

To insert a header or footer into a Microsoft Excel workbook:

  1. On the INSERT tab, in the Text group, click Header & Footer:Header and Footer in Text Group of Insert Tab
  2. Note that:
    1. Excel has switched to the PAGE LAYOUT view, in which the header and footer are visible. When done editing the header and footer, simply switch back to the Normal view:Views on Status Bar
    2. The header contains three sections: left, center and right. By default your cursor starts in the center section of the header, but you can switch to one of the others by clicking on it:Header Sections
    3. Excel has automatically opened the HEADER & FOOTER TOOLS DESIGN toolbar:Header and Footer Tools Design Toolbar
  3. From the HEADER & FOOTER TOOLS DESIGN toolbar you can:
    1. In the Header & Footer group, click Header or Footer and insert one of the options available:Header Options
    2. In the Header & Footer Elements group, insert one of the following:
      1. Page Number. Adds the page number to the selected section of the header or footer.
      2. Number of Pages. Adds the number of pages to the selected section of the header or footer.
      3. Current Date. Adds the current date, at the time of printing, to the selected section of the header or footer.
      4. Current Time. Adds the current time, at the time of printing, to the selected section of the header or footer.
      5. File Path. Adds the file path to the selected section of the header or footer.
      6. File Name. Adds the file name to the selected section of the header or footer.
      7. Sheet Name. Adds the sheet name to the selected section of the header or footer.
      8. Picture. Opens up the Insert Picture dialog box, where you can surf to and select a picture by clicking Insert.Header and Footer Elements Group
    3. In the Options group, check Different First Page to set the header and footer such that they do not show on the first page:Options Group
  4. To move between the header and the footer, click Go to Footer or Go to Header in the Navigation group:Navigation Group
  5. To exit the Header/Footer go to View > Workbook Views > Normal

Preparing to Print

Duration: 10 to 20 minutes.

In this exercise, you will prepare your worksheet for printing.

  1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Preparing to Print.xlsx from your Excel2013.1/Exercises folder.
  2. Center data horizontally and vertically on the page.
  3. Set the page orientation to landscape.
  4. Set the worksheet to print on one page.
  5. Add the picture LemonadeLogo.gif, found in your Excel2013.1/Exercises folder, to the header in the center section.
  6. Save the workbook.

Solution:

  1. Open or go to the specified file.
  2. To center data horizontally and vertically on the page:
    1. On the PAGE LAYOUT tab, in the Page Setup group, click the Margins command:Margins in Page Setup Group of Page Layout Tab
    2. Select Custom Margins:Custom Margins
    3. In the Center on Page section, check both Horizontally and Vertically and click OK:Page Setup Dialog Box
  3. To set the page orientation to landscape:
    1. On the PAGE LAYOUT tab, in the Page Setup group, click the Orientation command:Orientation in Page Setup Group of Page Layout Tab
    2. Select Landscape:Orientation Options
  4. To set the worksheet to print on one page:
    1. On the PAGE LAYOUT tab, in the Scale to Fit group, set the Width and Height to 1 page:Print Scaling
  5. To add the logo to the header:
    1. On the INSERT tab, in the Text group, click Header & Footer:Header and Footer in Text Group of Insert Tab
    2. On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click Picture:Picture in Header and Footer Elements Group
    3. Surf to your Excel2013.1/Exercises folder, select LemonadeLogo.gif, and click INSERT:Insert Picture Dialog Box
  6. Save the workbook. If you print it, it should look like this:Print Preview

Printing Headings on Each Page/Repeating Headers and Footers

If your printout spans multiple pages, you may want to have column headers and/or row titles be repeated at the top and left of each page.

To print headings on each page:

  1. On the PAGE LAYOUT tab, from the Page Setup group, select Print Titles. Print Titles in Page Setup Group of Page Layout Tab
  2. In the Page Setup dialog box, on the Sheet tab, under Print titles, type the references of the rows and columns that you want to appear, respectively, in the Rows to repeat at top and Columns to repeat at left options and click OK. Page Setup Dialog Box