MICROSOFT Excel 2010 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 options used most often to affect 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 page layout, 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.

The new File tab in Excel 2010, aka Backstage view, now includes the most-often-used options as well and provides some visual feedback about what all these things actually do. Plus, a print preview is displayed on the right side of the screen and dynamically displays the settings you choose from the options on the left side. Click the File tab, and then click Print to display a screen similar to the one shown in Figure 11-2.

Setting Page Options

The settings you use most frequently are duplicated in several places. Most of what you need is on the Page Layout tab of the ribbon, in the Print category on the File tab, and, of course, in the Page Setup dialog box. These settings control page orientation, scaling, paper size, print quality, and page numbering.

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

The Width and Height controls on the Page Layout tab, shown in Figure 11-1, normally display Automatic, which means the worksheet prints at full size on as many pages as necessary. In these drop-down lists, choose a number of pages to constrain the printout. For example, choosing 2 Pages in the Width list (and leaving Height set to Automatic) scales the print area of the worksheet so that it spans two pages at its widest point, filling as many pages in length as necessary. Choosing 2 Pages in the Height list (and leaving Width set to Automatic) scales the print area of the worksheet so that it is only two pages long, filling as many pages in width as necessary.

The Fit To options in the Page Setup dialog box give you more precise control over scaling. These mirror the Height and Width controls on the Page Layout tab. To return to a full-size printout, select the Adjust To option and type 100 in the % Normal Size box.

You can also use the Scaling options in the File tab’s Print category. The Print category, shown in Figure 11-3, allows you to specify scaling and other options on the fly just before you send the worksheet to the printer.

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.

Figure 11-3. Click the File tab, click Print, and use the Scaling options to quickly tailor your printouts.

Working in Page Layout View

Page Layout view represents a major upgrade to the worksheet-printing workflow in comparison with the “old” ways of doing things. Not only can you see the page as it will print, as you can with Print Preview, but Excel is fully functional in Page Layout view, so you can make changes and see the results immediately. Click the Page Layout button on the View tab, shown in Figure 11-4. This might become your preferred working environment, if you don’t mind the slight slowdown in performance that comes with a more graphically intensive interface.

In Page Layout view, you can do the following:

Drag the edge between the shaded and white areas on the rulers to adjust margins.
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.
Click the Page Layout tab and change settings in the Page Setup group to see the changes immediately reflected 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. Note, however, that Page Layout view can be set separately on each sheet, so switching to another sheet might also switch you back to Normal view.

Figure 11-4. Page Layout view is not just a preview; it’s a fully functional working environment.

In fact, we couldn’t find anything you can’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.

Note

You can also use the three tiny buttons at the bottom of the screen next to the Zoom slider to change the view. The first button activates Normal view, the second activates Page Layout view, and the third activates Page Break Preview. For more about Page Break Preview, see Adjusting Page Breaks on page 457.

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, company logos, and so on. The Margins button on the Page Layout tab, shown in Figure 11-5, provides three settings that should 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 on the Margins menu, as Figure 11-5 shows. This command does not appear unless you specify your own margin settings. These commands also appear in the Print category on the File tab.

Figure 11-5. 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, shown in Figure 11-6, by clicking the Margins button and then clicking Custom Margins.

When you click in any of the text boxes on the Margins tab, the corresponding margin line is highlighted 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 below.

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

Creating a Header and Footer

The easiest way to create headers and footers is to click the Header & Footer button on the Insert tab, which simultaneously displays the Header & Footer Tools Design tab, switches the worksheet to Page Layout view, and activates the header for editing, as shown in Figure 11-7.

Figure 11-7. Click the Header & Footer button on the Insert tab to display the Header & Footer Tools Design tab.

Both the Header and Footer areas in Page Layout view consist of edit boxes in three sections—left, center, and right—that are formatted with the corresponding justification (that is, the contents of the box on the right are right-justified). Use these edit boxes to insert and format headers and footers using buttons on the Header & Footer Tools Design tab:

Header & Footer group These buttons display menus of predesigned headers and footers, shown as they appear when printed. For example, if you click Page 1 Of ? on the Header menu, the proper code is inserted in the header. If you then click the inserted information in the header, you can see the underlying code—much like a formula in a cell—that produces the result: Page &[Page] of &[Pages]. You don’t really need to know these codes; buttons are available to create them for you.
Page Number Inserts the page number in the selected section.
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.
Current Date Inserts the date of printing.
Current Time Inserts the time of printing.
File Path Inserts the folder path and file name of the workbook.
File Name Inserts only the file name of the current workbook.
Sheet Name Inserts the name of the current worksheet.
Picture Displays the Insert Picture dialog box. (See Adding Pictures to Headers and Footers on page 449.)
Format Picture Displays the Format Picture dialog box, letting you adjust the settings of an inserted picture.
Navigation group The Go To Header and Go To Footer buttons are simply quick ways to jump between the corresponding edit boxes at the top and bottom of the page.
Different First Page Specifies a different header and footer for the first page only.
Different Odd & Even Pages Specifies different headers and footers for even and odd pages; typically used to create balanced two-page spreads when you print a worksheet double-sided.
Scale With Document Selected by default; clear this check box if you want headers and footers to remain unchanged even if you enlarge or shrink the rest of the document.
Align With Page Margins Selected by default; clear this check box if you want the headers and footers to remain fixed, unaffected by changes to the margins.

Note

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.

Excel uses codes to represent dynamic data in your headers and footers, such as the current time represented by &[Time]. Fortunately, you don’t have to learn these codes to create headers and footers. Click the edit box where you want the information to appear, and then click the appropriate buttons to add the information to your header or footer. Here are some things to remember about editing headers and footers:

To include text in a header or footer, just click a text box and start typing. You need to add spaces between text and inserted code elements, as well as between adjacent code elements.
An ampersand (&) always precedes a code element, so never insert anything between the ampersand and its code. To include an actual ampersand in your header or footer, type two ampersands.
To apply formatting, use the small, ghostly toolbar that appears when you select text in an edit box. Or, because the worksheet is in Page Layout view, you can click the Home tab and use the formatting tools in the Font group as well as the editing tools in the Clipboard group.
As in cells, if you enter more information in any of the header or footer edit boxes than can be displayed, the information spills over into adjacent edit boxes. Yes, you can enter too much data, actually causing text to overlap, but you’ll see it immediately because you are working in Page Layout view.
To remove header or footer information, click the None option at the top of the Header or Footer button menu, or simply delete the contents of the corresponding edit boxes in Page Layout view.

Using the Header/Footer Tab

The Page Setup dialog box contains the old-school interface for Excel’s header and footer features. You can still use it, but it affords little advantage over the Header & Footer Tools Design tab. Click the Page Layout tab, click the dialog box launcher in the Page Setup group to display the Page Setup dialog box, and then click the Header/Footer tab, as shown in Figure 11-8.

The drop-down lists that appear immediately under the words Header and Footer offer the same lists of predefined options as the eponymous buttons on the Header & Footer Tools Design tab. And the check box options in the dialog box mirror the ones in the Options group on the tab. The three buttons at the bottom of the dialog box are really the only things that offer a little added value here. The Print button takes you directly to the Print screen in Backstage view, and the Print Preview button actually takes you to the same place, since Print Preview now appears in Backstage view as well. The Options button takes you directly to your printer’s Properties dialog box. Click the Custom Header button to display a dialog box similar to the one shown in Figure 11-9.

The Header (and identical Footer) dialog box does almost all the things that the Header & Footer Tools Design tab does. The only difference here is the addition of the Format Text button (the first button on the left), which displays the Font dialog box shown in Figure 11-9.

Figure 11-8. The Header/Footer tab contains controls similar to those on the Header & Footer Tools Design tab.

Figure 11-9. The Custom Header button displays the Header dialog box; the Format Text (A) button displays the Font dialog box.

Adding Pictures to Headers and Footers

You can add pictures to custom headers and footers by using the Picture and Format Picture buttons (refer to Figure 11-7). For example, you can insert pictures to add company logos or banners to your documents. Click the Picture button to access the Insert Picture dialog box (a version of the standard Open dialog box), which you use to locate the picture you want to insert. When you insert the picture, Excel includes the code &[Picture], and the image is displayed in the edit box. (Unlike with other header and footer codes, you can’t just type this code—you have to use the Picture button.)

After you insert a 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-10 shows a sample of a picture used in a header, with the worksheet displayed in Page Layout view.

Note

You’ll find the Fabrikam.xlsx file with the other examples on the companion Web site.

To arrive at the example shown in Figure 11-10, 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 clicked the Page Layout tab and clicked the dialog box launcher button in the Page Setup group to display the Page Setup dialog box.
We selected both the Vertically and Horizontally check boxes below Center On Page on the Margins tab in the Page Setup dialog box.
We dragged the top margin down using the side ruler in Page Layout view enough to accommodate the graphic.

Figure 11-10. You can insert pictures from disk into headers and footers.

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-11, 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 on the Page Layout tab.)

Note

You’ll find the 2011 Projections.xlsx file with the other examples on the companion Web site.

Figure 11-11. The Sheet tab in the Page Setup dialog box stores the print area and print titles data.

Specifying the Area to Be Printed

If you do not specify an area to print, Excel prints the entire active area of the selected worksheet(s). If you don’t want to print the entire worksheet, you can specify an area to print by using the Print Area button on the Page Layout tab. First, select the range or ranges you want to print, click Print Area on the Page Layout tab, and then click Set Print Area. To clear this setting, click the Print Area button and click Clear Print Area. You can specify a print area setting for each worksheet, and the settings are saved with the workbook.

The print area settings are stored in the first box on the Sheet tab in the Page Setup dialog box, which you can also use to set or edit the print area. To do so, click the Print Titles button on the Page Layout tab (a quick way to display the Page Setup dialog box). Click in the Print Area 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 select in the Print Area box, as shown in Figure 11-11. 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.

Note

To remove a 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 483.

Specifying Rows and Columns to Print on Every Page

On most worksheets, the column and row labels that identify your data 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 use the Print Titles feature 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.

Suppose you want to print the contents of column A and rows 1, 2, and 3 on all the pages of the report shown in Figure 11-11:

Click the Print Titles button on the Page Layout tab to display the Page Setup dialog box, open to the Sheet tab.
Click in the Rows To Repeat At Top text box, and then select the headings for the first three rows. (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).
Click OK.

Figure 11-12 shows the result in Page Layout view. Notice that the column containing the product numbers appears on both pages displayed in Page Layout view. If you did not use print titles, the first column on the second page of the printout would display the June totals column instead of the product numbers. You can specify separate print titles for each worksheet in your workbook. Excel remembers the titles for each worksheet.