Chapter 9: Printing Your Work

Printing row and column headers

By default, row and column headers for a worksheet are not printed. If you want your printout to include these items, choose Page Layout Sheet Options Headings Print.

Using a background image

Would you like to have a background image on your printouts? Unfortunately, you can’t. You may have noticed the Page Layout Page Setup Background command. This button displays a dialog box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed.

Tip

In lieu of a true background image, you can insert a Shape, WordArt, or a picture on your worksheet and then adjust its transparency. Then copy the image to all printed pages. Alternatively, you can insert an object in a page header or footer. See the sidebar titled, “Inserting a Watermark.” n

Adding a Header or Footer to Your Reports

A header is information that appears at the top of each printed page. A footer is information that appears at the bottom of each printed page. By default, new workbooks do not have headers or footers.

You can specify headers and footers by using the Header/Footer tab of the Page Setup dialog box. This task is much easier if you switch to Page Layout view, though, where you can click the section labeled Click to Add Header or Click to Add Footer.

Note

If you’re working in Normal view, you can choose Insert Text Header & Footer. Excel switches to Page Layout view and activates the center section of the page header. n

You can then type the information and apply any type of formatting you like. Note that headers and footers consist of three sections: left, center, and right. For example, you can create a header that prints your name at the left margin, the worksheet name centered in the header, and the page number at the right margin.

Tip

If you would like a consistent header or footer for all your documents, create a book.xlt with your headers or footers specified. A book.xlt template is used as the basis for new workbooks. See Chapter 8 for details. n

185

Part I: Getting Started with Excel

When you activate the header or footer section in Page Layout view, the Ribbon displays a new contextual tab: Header & Footer Tools Design. Use the controls on this tab to work with headers and footers.

Selecting a predefined header or footer

You can choose from a number of predefined headers or footers by using either of the two dropdown lists in the Header & Footer Tools Design Header & Footer group. Notice that some items in these lists consist of multiple parts, separated by a comma. Each part goes into one of the three header or footer sections (left, center, or right). Figure 9.6 shows an example of a header that uses all three sections.

FIGURE 9.6

This three-part header is one of Excel’s predefined headers.

Understanding header and footer element codes

When a header or footer section is activated, you can type whatever text you like into the section. Or, to insert variable information, you can insert any of several element codes by clicking a button in the Header & Footer Tools Design Header & Footer Elements group. Each button inserts a code into the selected section. For example, to insert the current date, click the Current Date button. Table 9.2 lists the buttons and their functions.

TABLE 9.2

Header & Footer Buttons and Their Functions

186

You can combine text and codes and insert as many codes as you like into each section.

Note

If the text that you enter uses an ampersand (&), you must enter the ampersand twice (because Excel uses an ampersand to signal a code). For example, to enter the text Research & Development into a section of a header or footer, enter Research && Development. n

You can also use different fonts and sizes in your headers and footers. Just select the text that you want to change and then use the formatting tools in the Home Font group. Or use the controls on the Mini toolbar, which appears automatically when you select the text. If you don’t change the font, Excel uses the font defined for the Normal style.

Tip

You can use as many lines as you like. Press Enter to force a line break for multiline headers or footers. If you use multiline headers or footers, you may need to adjust the top or bottom margin so the text won’t overlap with the worksheet data. See “Adjusting the page margins,” earlier in this chapter. n

Unfortunately, you can’t print the contents of a specific cell in a header or footer. For example, you may want Excel to use the contents of cell A1 as part of a header. To do so, you need to enter the cell’s contents manually — or write a VBA macro to perform this operation before the sheet is printed.

Other header and footer options

When a header or footer is selected in Page Layout view, the Header & Footer Design Options group contains controls that let you specify other options:

•Different First Page: If checked, you can specify a different header/footer for the first printed page.

•Different Odd & Even Pages: If checked, you can specify a different header/footer for odd and even pages.

•Scale with Document: If checked, the font size in the header and footer will be sized accordingly if the document is scaled when printed. This option is enabled, by default.

•Align with Page Margins: If checked, the left header and footer will be aligned with the left margin, and the right header and footer will be aligned with the right margin. This option is enabled, by default.

187

Part I: Getting Started with Excel

Copying Page Setup Settings across Sheets

Each Excel worksheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup group of the Page Layout tab.

When you add a new sheet to a workbook, it contains the default page setup settings. Here’s an easy way to transfer the settings from one worksheet to additional worksheets:

1.Activate the sheet that contains the desired setup information. This is the source sheet.

2.Select the target sheets. Ctrl-click the sheet tabs of the sheets you want to update with the settings from the source sheet.

3.Click the dialog box launcher in the lower-right corner of the Page Layout Page

Setup group.

4.When the Page Setup dialog box appears, click OK to close it.

5.Ungroup the sheets by right-clicking any selected sheet and choosing Ungroup Sheets from the shortcut menu. Because multiple sheets are selected when you close the Page Setup dialog box, the settings of the source sheet will be transferred to all target sheets.

Note

Two settings located on the Sheet tab of the Page Setup dialog box are not transferred: Print Area and Print Titles. In addition, pictures in the header or footer are not transferred. n

Preventing Certain Cells from Being Printed

If your worksheet contains confidential information, you may want to print the worksheet but not the confidential parts. You can use several techniques to prevent certain parts of a worksheet from printing:

•Hide rows or columns. When you hide rows or columns, the hidden rows or columns aren’t printed. Use the Home Cells Format drop-down list to hide the selected rows or columns.

•Hide cells or ranges.

•You can hide cells or ranges by making the text color the same color as the background color. Be aware, however, that this method may not work for all printers.

•You can hide cells by using a custom number format that consists of three semicolons (;;;). See Chapter 24 for more information about using custom number formats.

•Mask an area. You can mask a confidential area of a worksheet by covering it with a rectangle Shape. Choose Insert Illustrations Shapes and click the Rectangle Shape. You’ll probably want to adjust the fill color to match the cell background and remove the border.

188