Chapter 9: Printing Your Work

Adjusting Common Page Setup Settings

Clicking the Quick Print button (or choosing File Print Print) may produce acceptable results in many cases, but a little tweaking of the print settings can often improve your printed reports. You can adjust print settings in three places:

•The Print screen in Backstage View, displayed when you choose File Print

•The Page Layout tab of the Ribbon

•The Page Setup dialog box, displayed when you click the dialog launcher in the bottomright corner of the Page Layout Page Setup group on the Ribbon

Table 9.1 summarizes the locations where you can make various types of print adjustments in Excel 2010.

TABLE 9.1

Where to Change Printer Settings

177

Part I: Getting Started with Excel

Choosing your printer

To switch to a different printer, choose File Print, and use the drop-down control in the Printer section to select a different installed printer.

Note

To adjust printer settings, click the Printer Properties link to display a property box for the selected printer. The exact dialog box that you see depends on the printer. The Properties dialog box lets you adjust printer-specific settings, such as the print quality and the paper source. In most cases, you won’t have to change any of these settings, but if you’re having print-related problems, you may want to check the settings. n

Specifying what you want to print

Sometimes you may want to print only a part of the worksheet rather than the entire active area. Or you may want to reprint selected pages of a report without printing all the pages. Choose File Print, and use the controls in the Settings section to specify what to print.

You have several options:

•Active Sheets: Prints the active sheet or sheets that you selected. (This option is the default.) You can select multiple sheets to print by pressing Ctrl and clicking the sheet tabs. If you select multiple sheets, Excel begins printing each sheet on a new page.

•Entire Workbook: Prints the entire workbook, including chart sheets.

•Selection: Prints only the range that you selected before choosing File Print.

•Selected Table: Appears only if the cell pointer is within a table (created with

Insert Tables Table) when the Print Setting screen is displayed. If selected, only the table will be printed.

Tip

You can also choose Page Layout Page Setup Print Area Set Print Area to specify the range or ranges to print. Before you choose this command, select the range or ranges that you want to print. To clear the print area, choose Page Layout Page Setup Print Area Clear Print Area. To override the print area, select the Ignore Print Areas check box in the list of Print What options. n

178

Chapter 9: Printing Your Work

Is Printing Getting More Complicated?

If you’re new to Excel, the information in Table 9.1 might be a bit overwhelming. Why does Excel provide three ways to adjust printing options? After all, before Excel 2007, Excel provided the Page Setup dialog box, plus a Print dialog box. Everything you needed was in these two dialog boxes. It was fairly simple.

With the introduction of the Ribbon in Excel 2007, though, things got a bit more complicated. Some of the more common print settings were in the Page Layout Page Setup Ribbon group, where they are easily seen. These are also the settings that determine how the Page Layout View is displayed. The Page Setup and Print dialog boxes were still used.

New Feature

In Excel 2010, the Print tab in Backstage View replaces the Print dialog box. The Page Layout Page

Setup Ribbon group remains unchanged. And users still need to use the Page Setup dialog box to make some changes. n

Table 9.1 might make printing seem more complicated than it really is. The key point to remember is this: If you can’t find a way to make a particular adjustment, it’s probably available from the Page Setup dialog box.

Note

The print area does not have to be a single range. You make a multiple selection before you set the print area. Each area will print on a separate page. n

If your printed output uses multiple pages, you can select which pages to print by indicating the number of the first and last pages to print by using Pages controls in the Settings section. You can either use the spinner controls or type the page numbers in the edit boxes.

Changing page orientation

Page orientation refers to how output is printed on the page. Choose Page Layout Page Setup Orientation Portrait to print tall pages (the default) or Page Layout Page Setup Orientation Landscape to print wide pages. Landscape orientation is useful when you have a wide range that doesn’t fit on a vertically oriented page.

If you change the orientation, the onscreen page breaks adjust automatically to accommodate the new paper orientation.

Page orientation settings are also available when you choose File Print.

Specifying paper size

Choose Page Layout Page Setup Size to specify the paper size you’re using. The paper size settings are also available when you choose File Print.

179

Part I: Getting Started with Excel

Note

Even though Excel displays a variety of paper sizes, your printer may not be capable of using them. n

Printing multiple copies of a report

Use the Copies control at the top of the Print tab in Backstage View to specify the number of copies to print. Just enter the number of copies you want and then click Print.

Tip

If you’re printing multiple copies of a report, make certain that the Collated option is selected so that Excel prints the pages in order for each set of output. If you’re printing only one page, Excel ignores the Collated setting. n

Adjusting page margins

Margins are the unprinted areas along the sides, top, and bottom of a printed page. Excel provides four “quick margin” settings, and you can also specify the exact margin size you require. All printed pages have the same margins. You can’t specify different margins for different pages.

In Page Layout view, a ruler is displayed above the column header and to the left of the row header. Use your mouse to drag the margins in the ruler. Excel adjusts the page display immediately. Use the horizontal ruler to adjust the left and right margins, and use the vertical ruler to adjust the top and bottom margins.

From the Page Layout Page Setup Margins drop-down list, you can select Normal, Wide, Narrow, or the Last custom Setting. These options are also available when you choose File Print. If none of these settings does the job, choose Custom Margins to display the Margins tab of the Page Setup dialog box, shown in Figure 9.4.

To change a margin, click the appropriate spinner (or you can enter a value directly). The margin settings that you specify in the Page Setup dialog box will then be available in the Page

Layout Page Setup Margins drop-down list, referred to as Last Custom Setting.

Note

The Preview box in the center of the Page Setup dialog box is a bit deceiving because it doesn’t really show you how your changes look in relation to the page; rather, it displays a darker line to let you know which margin you’re adjusting. n

In addition to the page margins, you can adjust the distance of the header from the top of the page and the distance of the footer from the bottom of the page. These settings should be less than the corresponding margin; otherwise, the header or footer may overlap with the printed output.

By default, Excel aligns the printed page at the top and left margins. If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab.

180

Chapter 9: Printing Your Work

FIGURE 9.4

The Margins tab of the Page Setup dialog box.

Understanding page breaks

When printing lengthy reports, controlling where pages break is often important. For example, you probably don’t want a row to print on a page by itself, nor do you want a table header row to be the last line on a page. Fortunately, Excel gives you precise control over page breaks.

Excel handles page breaks automatically, but sometimes you may want to force a page break — either a vertical or a horizontal one — so that the report prints the way you want. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.

Inserting a page break

To insert a horizontal page-break line, move the cell pointer to the cell that will begin the new page. Just make sure that you place the pointer in column A, though; otherwise, you’ll insert a vertical page break and a horizontal page break. For example, if you want row 14 to be the first row of a new page, select cell A14. Then choose Page Layout Page Setup Breaks Insert Page Break.

Note

Page breaks are visualized differently, depending on which view mode you’re using. See “Changing Your Page View,” earlier in this chapter. n

181

Part I: Getting Started with Excel

To insert a vertical page-break line, move the cell pointer to the cell that will begin the new page. In this case, though, make sure to place the pointer in row 1. Choose Page Layout Page

Setup Breaks Insert Page Break to create the page break.

Removing manual page breaks

To remove a page break you’ve added, move the cell pointer to the first row beneath (or the first column to the right) of the manual page break and then choose Page Layout Page Setup Breaks Remove Page Break.

To remove all manual page breaks in the worksheet, choose Page Layout Page Setup Breaks Reset All Page Breaks.

Printing row and column titles

If your worksheet is set up with titles in the first row and descriptive names in the first column, it can be difficult to identify data that appears on printed pages where those titles do not appear. To resolve this problem, you can choose to print selected rows or columns as titles on each page of the printout.

Cross-Reference

Row and column titles serve pretty much the same purpose on a printout as frozen panes do in navigating within a worksheet. See Chapter 3 for more information on freezing panes. Keep in mind, however, that these features are independent of each other. In other words, freezing panes does not affect the printed output. n

Caution

Don’t confuse print titles with headers; these are two different concepts. Headers appear at the top of each page and contain information, such as the worksheet name, date, or page number. Row and column titles describe the data being printed, such as field names in a database table or list. n

You can specify particular rows to repeat at the top of every printed page or particular columns to repeat at the left of every printed page. To do so, choose Page Layout Page Setup Print Titles. Excel displays the Sheet tab of the Page Setup dialog box, shown in Figure 9.5.

Activate the appropriate box (either Rows To Repeat At Top or Columns To Repeat At Left) and then select the rows or columns in the worksheet. Or you can enter these references manually. For example, to specify rows 1 and 2 as repeating rows, enter 1:2.

Note

When you specify row and column titles and use Page Layout view, these titles will repeat on every page (just as when the document is printed). However, the cells used in the title can be selected only on the page in which they first appear. n

182

Chapter 9: Printing Your Work

FIGURE 9.5

Use the Sheet tab of the Page Setup dialog box to specify rows or columns that will appear on each printed page.

Scaling printed output

In some cases, you may need to force your printed output to fit on a specific number of pages. You can do so by enlarging or reducing the size. To enter a scaling factor, choose Page Layout Scale to Fit Scale. You can scale the output from 10% up to 400%. To return to normal scaling, enter

100%.

To force Excel to print using a specific number of pages, choose Page Layout Scale to Fit Width and Page Layout Scale to Fit Height. When you change either one of these settings, the corresponding scale factor is displayed in the Scale control.

Caution

Excel doesn’t care about legibility, however. It will gladly scale your output to be so small that no one can read it. n

Printing cell gridlines

Typically, cell gridlines aren’t printed. If you want your printout to include the gridlines, choose Page Layout Sheet Options Gridlines Print.

Alternatively, you can insert borders around some cells to simulate gridlines. See Chapter 6 for information about borders.

183

Part I: Getting Started with Excel

Inserting a Watermark

A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn’t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. Here’s how:

1.Locate an image on your hard drive that you want to use for the watermark.

2.Choose View Workbook Views Page Layout View.

3.Click the center section of the header.

4.Choose Header & Footer Tools Header & Footer Elements Picture.

5.Using the Insert Picture dialog box, locate the image from Step 1.

6.Click outside the header to see your image.

7.To center the image in the middle of the page, click the center section of the header and add some carriage returns before the &[Picture] code. You’ll need to experiment to determine the number of carriage returns required to push the image into the body of the document.

8.If you need to adjust the image (for example, make it lighter), click the center section of the header and then choose Header & Footer Tools Header & Footer Elements Format

Picture. Use the Image controls in the Picture tab of the Format Picture dialog box to adjust the image. You may need to experiment with the settings to make sure that the worksheet text is legible.

The accompanying figure shows an example of a header image (a globe) used as a watermark. You can do a similar thing with text, of course.

184