Chapter 11: Printing and Presenting

Microsoft Office Excel 2007 makes it easy for you to produce polished, professional-looking reports. In this chapter, we explain how to define the layout of your printed pages, control page breaks, and preview your pages for printing.

Controlling the Appearance of Your Pages

The most often used options affecting the appearance of your printed pages are available on the Page Layout tab on the Ribbon, shown in Figure 11-1. This is the central control panel for setting up paper sizes, margins, and page orientation, as well as for working with page breaks, print areas, and other printing options. For even more control over your printouts, click the Dialog Box Launcher in the Page Setup group on the Page Layout tab to display the Page Setup dialog box, also shown in Figure 11-1.

Figure 11-1: The Page Layout tab on the Ribbon and the Page Setup dialog box control most printing options.

Setting Page Options

The Page tab in the Page Setup dialog box, shown in Figure 11-1, is the tab you'll use most often; it contains settings that control page orientation, scaling, paper size, print quality, and page numbering.

Printing Wide or Tall

The Orientation button on the Page Layout tab offers two options: Portrait and Landscape. These options determine whether Office Excel 2007 prints your worksheet vertically (Portrait) or horizontally (Landscape). Portrait, the default setting, offers more room for rows but less room for columns. Select Landscape if you have more columns but fewer rows on each page. You can also find these options on the Page tab in the Page Setup dialog box.

Specifying Paper Size

The Size button on the Page Layout tab includes options for nearly every size of paper available (not just the sizes supported by your printer). You can additionally control the quality of your printout by clicking the Size button and then clicking More Paper Sizes (or clicking the Dialog Box Launcher in the Page Setup group) to display the Page tab in the Page Setup dialog box. The Print Quality drop-down list shows the print quality settings available for your printer. A laser printer, for example, might offer print-quality settings of 600 dots per inch (dpi), 300 dpi, and 150 dpi. Higher dpi settings look better but take longer to print. If the Print Quality drop-down list is not available, you might also be able to adjust these settings, and more, using your printer driver's dialog box, which you can access by clicking the Options button on the Page tab in the Page Setup dialog box.

For more information about printer drivers, see "Setting Printer Driver Options" on page 422.

AutoLetter/A4 Paper Resizing

Excel includes help for folks who routinely share work across international borders. In much of the world, the standard paper size is Letter (8.5 by 11 inches), but A4 paper (210 by 297 millimeters) is also widely used. Now you can print worksheets set for A4 paper on printers loaded with standard Letter paper (and vice versa), and Excel adjusts the page setup accordingly. Excel does this on the fly, without changing the page size setting in the Page Setup dialog box. If you want to turn this feature off, click the Microsoft Office Button, click Excel Options, select the Advanced category, and in the General group clear the Scale Content For A4 Or 8.5 x 11" Paper Sizes check box.

Setting a Reduction Ratio

Using the Scaling settings on the Page tab in the Page Setup dialog box, you can override the default size of your printouts in one of two ways: by specifying a scaling factor (from 10 percent through 400 percent) or by fitting the report to a specified number of pages. Excel always scales in both the horizontal and vertical dimensions. For example, if the full size of your print area is two pages deep but only one page wide and you tell Excel to scale it to a single page, the resulting printout will be both narrower and shallower. The Fit To options are a great way to print a worksheet that is ordinarily just a bit too large to fit on a single printed page. If you want to return to a full-size printout after selecting a scaling option, you can select the Adjust To option and type 100 in the % Normal Size box.

Setting the First Page Number

If you want to control the numbering of pages in your printout's header or footer-an essential tool when printing multipage worksheets-use the First Page Number box on the Page tab in the Page Setup dialog box. You can type any starting number, including 0 or negative numbers. By default, this option is set to Auto, but you can change it to any number you want.

Working in Page Layout View

The new Page Layout view in Excel 2007 represents a major upgrade to the worksheet-printing workflow, in comparison with the "old" ways of doing things. In previous versions of Excel, the last task you performed, after creating and formatting a worksheet, was to view the worksheet in Print Preview to see how it was going to fit on a page. This is partly because Print Preview is minimally interactive-you can drag to change margins and column widths, but that's all. In Page Layout view, however, Excel is fully functional. To see for yourself, click the Page Layout View button on the View tab. Page Layout view, shown on the next page, could become your preferred working environment, if you don't mind the considerable slowdown in performance that comes with a more graphically intensive interface.

In Page Layout view, you can do the following:

• Drag lines between row and column headers to adjust row height and column width.

• Refer to the rulers to see the actual dimensions of your data relative to the printed page.

• Drag the edge between the shaded and white areas on the rulers to adjust margins.

• Click the Page Layout tab and change settings in the Page Setup group to see the changes immediately reflected in graphical pages that appear as separate sheets of paper on your screen.

• Click and type directly in headers and footers.

• Click other tabs on the Ribbon to zoom, apply formatting, and add formulas, graphics, charts, and so on.

In fact, we couldn't find anything you couldn't do in Page Layout view. Page Layout view is applied per worksheet; you can specify a different view for each open worksheet, and the settings are saved with the workbook.

Setting Margins

You can adjust the margins of your printouts to allow the maximum amount of data to fit on a page, to customize the amount of space available for headers and footers, or to accommodate special requirements such as three-hole-punched paper. The Margins button on the Page Layout tab, shown in Figure 11-2, provides three settings that may meet most of your needs: Normal, Wide, and Narrow. These settings refer to the size of the margins, not the size of the printed area. For example, to fit more data on a page, use the Narrow setting. Note that when you apply your own margin settings, the Last Custom Setting command appears as the first item in the Margins menu, as Figure 11-2 shows. This command does not appear unless you have specified your own margin settings.

Figure 11-2: Click the Margins button on the Page Layout tab to choose a basic margin setting.

The Margins tab in the Page Setup dialog box offers precise control over the top, bottom, left, and right margins of your printed worksheets. You can display the Margins tab by clicking the Margins button and then clicking Custom Margins. As shown in Figure 11-3, the default settings are. 75 inch for the top and bottom margins and 0.7 inch for the left and right margins.

Figure 11-3: You can specify precise margin settings on the Margins tab in the Page Setup dialog box.

When you click in any of the text boxes on the Margins tab, a line appears in the sample page in the middle of the dialog box, showing you where the selected margin will appear.

If you want a header or footer to appear on each page, the top and bottom margins need to be large enough to accommodate them. For more information about setting up a header and footer, see "Creating a Header and Footer" on the next page.

Centering Your Work on the Page

Excel aligns worksheets to the upper-left corner of the printed page by default. If you want Excel to center your printout on the page vertically, horizontally, or both, select the Center On Page check boxes at the bottom of the Margins tab in the Page Setup dialog box (refer to Figure 11-3).

Creating a Header and Footer

On the Header/Footer tab in the Page Setup dialog box, you can provide essential information about your printout-such as file name, creation date, page number, and author's name-by including a header (printed at the top of each page) or footer (printed at the bottom of each page). By default, Excel prints footers. 3 inch from the bottom edge and headers. 3 inch from the top edge, but you can change this on the Margins tab in the Page Setup dialog box.

The drop-down lists that appear immediately under the words Header and Footer in the dialog box shown in Figure 11-4 offer predefined options you can use to customize your headers and footers. When you select an option in the drop-down list, the preview area adjacent to the list displays a sample of the selected option. In Figure 11-4, we selected predefined options for both Header and Footer, which are reflected in the previews.

Figure 11-4: You can choose from predefined headers and footers or create your own using the Header/Footer tab in the Page Setup dialog box.

Creating Custom Headers and Footers

If you don't find what you need in the list of predefined headers and footers, you can create your own or modify one that Excel offers. If you create custom headers or footers for the current workbook, Excel adds them to these drop-down lists. Click the Custom Header button to open the Header dialog box shown in Figure 11-5, or click the Custom Footer button to open a similar dialog box.

Figure 11-5: The Header dialog box contains tools to make creating a header a snap.

Create a Default Header and Footer

If you want your header and footer to be the same in every workbook you create, you can create a default header and footer. Open a new, blank workbook, and set the header and footer the way you want them to be every time. Next, save the workbook using the name Book.xls. Store this file in the XLStart folder:

• Windows Vista C: \Users\<your name>\AppData\Roaming\Microsoft\Excel\XLStart

• Windows XP C: \Documents and Settings\<your name>\Application Data\Microsoft\Excel\XLStart

Whenever you open a new workbook, it will have your header and footer already in place.

Excel uses various codes to represent information you might want to put in your headers and footers-such as the current time, current date, and current page number. Fortunately, you don't have to learn these codes to create headers and footers. Click the appropriate box (Left Section, Center Section, or Right Section) to indicate where you want the information to appear, and then click the appropriate buttons to add the information to your header or footer. Here's what each button does:

• Format Text Displays the Font dialog box, letting you specify the font and font style for the selected text

• Insert Page Number Inserts the page number in the selected section

• Insert Number Of Pages Inserts the total number of pages in the selected section; typically used in conjunction with the page number in a "Page x of y" construction

• Insert Date Inserts the date of printing in the selected section

• Insert Time Inserts the time of printing in the selected section

• Insert File Path Inserts the folder path and file name of the workbook in the selected section

• Insert File Name Inserts only the file name of the current workbook in the selected section

• Insert Sheet Name Inserts the name of the current worksheet in the selected section

• Insert Picture Displays the Insert Picture dialog box, letting you add a picture to the selected section

• Format Picture Displays the Format Picture dialog box, letting you adjust the settings of an inserted picture

To specify text in your header or footer, click the appropriate text box, and type your text. To divide the text between two or more lines, press Enter at the end of each line. To include an ampersand in your text, type two ampersands.

Adding Pictures to Headers and Footers

You can add pictures to custom headers and footers using the Insert Picture and Format Picture buttons (refer to Figure 11-5). For example, you can insert pictures to add company logos or banners to your documents. Click the Insert Picture button to access the Insert Picture dialog box, which you use to locate the picture you want to use. When you insert the picture, Excel displays &[Picture] in the section box of the Header (or Footer) dialog box. (Unlike other header and footer codes, you can't just type this code-you have to use the Insert Picture button.)

After you insert the picture, click the Format Picture button to specify the size, brightness, and contrast of the picture and to rotate, scale, or crop the picture. (You can't directly manipulate Header or Footer pictures-you must use the Format Picture button.) It might take some trial and error to obtain the result you want, adjusting the size of the picture as well as the top or bottom margins to accommodate it. Figure 11-6 shows a sample of a picture used in a header, displayed in Page Layout view.

Figure 11-6: You can manipulate the fonts and even add pictures to headers and footers.

On the CD You'll find the  Fabrikam.xlsx file in the Sample Files section of the companion CD.

To arrive at the example shown in Figure 11-6, we did the following:

• In the left section, we added the date and changed the font to 10-point, italic Arial Black.

• In the center section, we inserted a picture; then we clicked Format Picture and reduced its size.

• In the right section, we added the time and changed the font to 10-point, italic Arial Black.

• We dismissed the Header dialog box and selected both the Vertically and Horizontally check boxes below Center On Page on the Margins tab in the Page Setup dialog box.

• We dismissed the Page Setup dialog box and dragged the top margin to accommodate the graphic in Page Layout view.

Changing Fonts in Headers and Footers

The Excel default font for headers and footers is 10-point Arial. To select a different font, point size, or font style, select the code or text in the section you want to change, and click the Format Text button to access the Font dialog box. Note that the font options you select apply only to the highlighted text or code in the section box. You can assign different font options to each section, even to individual elements within each section.

Setting Worksheet Options

Clicking the Dialog Box Launcher in the Page Setup group on the Page Layout tab displays the Page Setup dialog box. Click the Sheet tab, shown in Figure 11-7, to access settings specific to the active worksheet. You can specify different worksheet options for each worksheet in a workbook. (You can also display the Sheet tab by clicking the Print Titles button.)

Figure 11-7: Use the Sheet tab in the Page Setup dialog box to set the area to print and the row and column titles to print on each page of your worksheet.

On the CD You'll find the  2008 Projections.xlsx file in the Sample Files section of the companion CD.

Specifying the Area to Be Printed

The first item on the Sheet tab in the Page Setup dialog box is the Print Area text box, which you use to specify the exact cell range (or ranges) you want to print. If you do not specify an area to print, Excel prints the entire active area of the selected worksheet(s). If you expect to print the same area of a given worksheet repeatedly, you can save yourself some steps by defining the print area. To do so, click in the Print Area text box, and then drag to select the cells on the worksheet you want to include. When you do this, the dialog box collapses so you can see more of the worksheet, and Excel inserts the cell range reference of the area you selected in the Print Area text box, as shown in Figure 11-7. You can select multiple nonadjacent cell ranges by selecting a range, typing a comma, and then selecting the next range. Each range you select prints on a separate page.

You can also use the Print Area button on the Page Layout tab to specify the area or areas you want to print. To use this method, first select the range or ranges you want to print, click Print Area, and then click Set Print Area, which adds the range references to the Page Setup dialog box. The other option on the Print Area menu is Clear Print Area, which removes all the range references from the Print Area text box in the Page Setup dialog box.

Note 

To remove your print area definition, you can return to the Page Setup dialog box and delete the cell references. You can also use the Define Name dialog box by pressing Ctrl+F3 and deleting the name Print_Area. For more information, see "Naming Cells and Cell Ranges" on page 441.

Specifying Rows and Columns to Print on Every Page

On most worksheets, the column and row labels that identify information categories appear in only the first couple of columns and top few rows. When Excel breaks up a large report into pages, those important column and row labels might appear only on the first page of the printout. You can use the Sheet tab in the Page Setup dialog box to force Excel to print the contents of one or more columns, one or more rows, or a combination of columns and rows on every page of a report. You can display the Sheet tab by clicking the Print Titles button on the Page Layout tab on the Ribbon.

Suppose you want to print the contents of column A and rows 1, 2, and 3 on all the pages of a lengthy report. First, click in the Rows To Repeat At Top text box, and then select the headings for rows 1 through 3. (To select multiple contiguous row headings, drag through them. ) Click in the Columns To Repeat At Left text box, and then select the column A heading (or any cell in column A). Figure 11-8 shows the result in Page Layout view. (Note that to apply print titles, you need to click OK in the Page Setup dialog box. In Figure 11-8, we did this, and then we redisplayed the dialog box and dragged it out of the way for illustration purposes.)

Figure 11-8: We defined the print titles and used Page Layout view to see the results.

Notice in Figure 11-8 that the column containing the product numbers appears on both pages displayed in Page Layout view. Without using print titles, the first column on the second page of the printout would have displayed the August totals instead of the product numbers. You can specify separate print titles for each worksheet in your workbook. Excel remembers the titles for each worksheet.

Note 

To remove your print title definitions, you can return to the Page Setup dialog box and delete the cell references. You can also use the Define Name dialog box by pressing Ctrl+F3 and deleting the name Print_Titles. For more information, see "Naming Cells and Cell Ranges" on page 441.

Printing Gridlines and Headings

By default, Excel does not print gridlines or row and column headings, regardless of whether you have them displayed on your worksheet. If you want to print gridlines or headings, select the corresponding Print check box in the Sheet Options group on the Page Layout tab. You can also select the Gridlines or Row And Column Headings check box on the Sheet tab in the Page Setup dialog box.

Printing Comments and Errors

Comments are annotations you create by clicking New Comment on the Review tab on the Ribbon. To make sure the comments in your worksheet are included with your printout, select one of the Comments options on the Sheet tab in the Page Setup dialog box. If you select At End Of Sheet from the drop-down list, Excel adds a page to the end of the printout and prints all your notes together, starting on that new page. If you select As Displayed On Sheet, Excel prints the comments as pop-up windows wherever they are located on a worksheet. Note that the latter option may cause the comments to obscure worksheet data.

Note 

You can display all comments on the worksheet by clicking the Show All Comments button on the Page Layout tab. This gives you an idea of how the worksheet will look when printed if you select the As Displayed On Sheet option in the Page Setup dialog box.

The Cell Errors As drop-down list on the Sheet tab in the Page Setup dialog box gives you options for how error codes that are displayed on the worksheet should be printed. Ordinarily, error codes such as #NAME? are printed just as they appear on your screen, but you can change this so cells containing error codes print as blank cells or with a double hyphen (--) or #NA displayed instead of the error code.

For more about creating comments, see "Adding Comments to Cells" on page 251. For more about error codes, see "Understanding Error Values" on page 437.

Printing Drafts

If your printer offers a draft-quality mode, you can obtain a quicker, though less attractive, printout by selecting the Draft Quality check box on the Sheet tab in the Page Setup dialog box. This option has no effect if your printer has no draft-quality mode and is most useful for dot matrix or other slow printers.

Translating Screen Colors to Black and White

If you've assigned colors and patterns to your worksheet but you want to see what it will look like when printed on a black-and-white printer, select the Black And White check box on the Sheet tab in the Page Setup dialog box, which tells Excel to use only black and white when printing. If you are using a black-and-white printer, you probably won't need to worry about this option, but if you seem to be having trouble, try selecting it.

Setting the Printing Order of Large Print Ranges

When you print a large report, Excel breaks the report into page-sized sections based on the current margin and page-size settings. If the print range is both too wide and too deep to fit on a single page, Excel ordinarily works in "down and then over" order. For example, suppose your print range measures 120 rows by 20 columns and Excel can fit 40 rows and 10 columns on a page. Excel prints the first 40 rows and first 10 columns on page 1, the second 40 rows and first 10 columns on page 2, and the third 40 rows and first 10 columns on page 3. On page 4, Excel prints the first 40 rows and second 10 columns, and so on. If you prefer to have Excel print each horizontal chunk before moving to the next vertical chunk, select the Over, Then Down option on the Sheet tab in the Page Setup dialog box.