Printing Worksheets

Unlike Word documents, which typically are designed to fit on specific paper sizes, Excel worksheets are free-flowing environments that sprawl in every direction. If you click the Print button and leave the formatting to Excel, you'll end up with page breaks that appear at arbitrary locations in your worksheet, with no regard to content. To properly translate a large worksheet into printed output takes planning and a fair amount of creative formatting.

If you don't specifically define a print area, Excel assumes that you want to print all the data in the currently selected worksheet or worksheets, beginning with cell A1 and extending to the edge of the area that contains data or formatting. If necessary, you can divide a worksheet into smaller sections and print each region on its own page. As explained in this section, you can also shrink the print area to fit in a precise number of pages, and you can repeat row and column headings to make the display of data easier to follow.

Using Ranges to Define the Default Print Area

You can force Excel to use a defined print area as the default for a worksheet. (Excel bypasses all dialog boxes and uses this region when you click the Print button.) This technique is especially useful if you regularly print a complex worksheet that contains a number of nonprinting regions. On a worksheet that contains a list and a criteria range, for example, you'll typically want to print only the list. On a budget worksheet that includes monthly data by category and an executive summary region, you might want to define the summary as the default when you click the Print button.

Start by selecting the range you want to print. The range need not be contiguous, but if you select a noncontiguous range, keep in mind that each selection will print on its own page, and the results might not be what you intended. All parts of the range to be printed must be on the same worksheet; each worksheet in a workbook gets a separate print area.

To define the selection as the default print area, choose File, Print Area, Set Print Area. Excel creates a named range called Print_Area in the current worksheet.

To delete the current print area selection and start over, choose File, Print Area, Clear Print Area.

When you define a specific print area, Excel prints only that area when you click the Print button. If you define a print area and then add rows at the bottom or columns to the right of the data, the new data won't appear on the printed pages. Whenever you redesign a worksheet, make a special point to recheck the print area.

Inserting Your Own Page Breaks

When you attempt to print a worksheet, Excel automatically inserts page breaks to divide it into sections that will fit on the selected paper size. (To see a dashed line that represents each break as you edit a workbook, choose Tools, Options, click the View tab, and check the Page Breaks box.) Excel doesn't analyze the structure of your worksheet before inserting page breaks; it simply adds a page break at the point where each page runs out of printable area. To make multipage worksheets more readable, you can and should position page breaks by hand.

To insert a manual page break, select the cell below and to the right of the last cell you want on the page; then choose Insert, Page Break. To remove the page break, select the same cell and choose Insert, Remove Page Break. To remove all manual page breaks from the current worksheet, select the entire sheet, and then choose Insert, Reset All Page Breaks.

Excel includes an unusual view option called Page Break Preview that lets you see all page breaks and adjust them by clicking and dragging. To switch to this view from a worksheet-editing window, choose View, Page Break Preview; from the Print Preview window, click the Page Break Preview button on the toolbar. As Figure 21.13 shows, this view lets you see your entire worksheet, broken into pages exactly as Excel intends to print it, with oversize page numbers laid over each block. (The numbers and lines don't appear on printed pages, of course.)

Dashed lines represent automatic page breaks inserted by Excel; solid lines represent manual page breaks. To adjust page breaks in this view, point to the thick line between two pages and drag it in any direction. To adjust the print area, drag the solid lines on any edge of the print area; cells that are not in the print area appear gray in Page Break Preview.

When using Page Break Preview, you'll have best results if you start at the top of the worksheet and work in the order it will print—normally from top to bottom and left to right, unless you've used the Page Setup dialog box to specify that you want to go across the worksheet before you work your way down. Move page breaks up or to the left only; moving them down or to the right can cause unpredictable results if you drag past the size of the page. In that case, Excel adds its own page breaks, undoing the effects of your painstaking page-breaking efforts.

Extra Items You Can Print

Use Excel's Print dialog box to specify that you want to print additional parts of a worksheet, such as comments, gridlines, and row or column headings. You can also control the way Excel translates colors into shades of gray. To see these additional printing options, choose File, Page Setup, and click the Sheet tab.

Table 21.2 lists the options available for each worksheet.

Table 21.2. Worksheet Print Options

Print Option	What It Does
Gridlines	It's okay to show gridlines on draft worksheets; for final output, however, turn off gridlines and use borders to set off data areas.
Comments	By default, comments are not printed; check this box to print them on a separate sheet or as they appear onscreen.
Draft Quality	Prints cell contents but skips gridlines and graphics. This option is unnecessary when using a laser printer but might be useful for speeding up printing on color output devices or slow inkjet printers.
Black and White	Excel translates color backgrounds to shades of gray on the printed page. This option removes most gray shades; it can also speed up print jobs on color printers. Use Print Preview to print a small test page to check results before printing a large sheet with this option.
Row and Column Headings	Prints letters and numbers to help identify cell addresses. Use in combination with the option to view formulas (choose Tools, Options, click the View tab, and check the Formulas bar) when you want to print out the structure of a worksheet so you can study it.
Print Titles	If the data in your worksheet spans several pages, you might lose your points of reference, such as the headings above columns of data or to the left of each row. Identify the Rows to Repeat at Top of each page or the Columns to Repeat at Left of each page. (See the following section for more details.)
Page Order	The graphic to the right of this option shows whether your sheet will print sideways first, then down, or the other way around. Adjust this order if necessary to make page numbering work properly.

Labeling Printed Pages with Headers and Footers

Any worksheet that spans more than one page should include a header or footer (or both). An assortment of preconfigured headers and footers lets you number pages, identify the worksheet, specify the date it was created, list the author, and so on. Choose File, Page Setup, and then click the Header/Footer tab to add or edit a header and footer. Click the Custom Header or Custom Footer button to build either of these elements with text of your choosing. Buttons on both dialog boxes let you add fields, such as the name of the current workbook or sheet or the current date and time.

Unlike every previous version, Excel 2002 allows you to include graphics in a header or footer. This is a feature that Excel users have been clamoring about for years, and it's a welcome addition. The most common application is to add a company logo to the top or bottom of each printed page. Click the Insert Picture button and browse to any graphic file supported by Office. You can insert one and only one graphic in each section—left, center, and right. Click the Format Picture button to crop, compress, resize, or scale a picture file in a header or footer.

→ For general-purpose advice on how to work with graphics in Office, see "Importing, Exporting, and Compressing Graphics".

By default, Excel allows a half-inch for a worksheet's header or footer. If you want to maximize the amount of data on each page and you're not using a header or footer, open the Page Setup dialog box, click the Margins tab, and set the Header, Footer, Top, and Bottom boxes to 0. (On some printers, you might need to adjust the top and bottom margins to match the unprintable area on the page.)

If your custom header or footer doesn't look right on the page, see "Adjusting Header and Footer Margins" in the "Troubleshooting" section at the end of this chapter.

Using Repeating Titles for Multiple Page Printouts

For worksheets that span multiple pages, you can repeat one or more rows or columns (or both) as titles for the data on each new page. On a typical budget worksheet, for example, the first column might contain income and expense categories, with columns for each month's data extending to the right across several pages. In this case, follow these steps to repeat the entries in the first column as titles at the left of each page:

Forcing a Worksheet to Fit on a Specified Number of Pages

Just as you can use the Zoom control to change the size of cells in a worksheet window, you can also reduce the size of data on a printout. Making the scale smaller lets you squeeze more rows and columns onto each page. If you want your printed worksheet to fit in a specific number of pages, Excel can calculate the scaling percentage for you: