Adjusting Page Breaks

Breaking pages across printed sheets of paper is often challenging using word-processing programs, but it can be even more challenging when planning the printing strategy for large spreadsheet models. Office Excel 2007 makes it easy to adjust the positions of page breaks by offering a couple of approaches: the Breaks button on the Page Layout tab on the Ribbon and the Page Break Preview button on the View tab.

Using Page Break Preview

Clicking the Page Break Preview button results in a view of your worksheet like the one shown in Figure 11-10. You can move page breaks by dragging them. You can even edit your worksheet while in Page Break Preview mode. Page Break Preview mode zooms out to give you a bird's-eye view of the entire worksheet, but you can use the Zoom controls in the lower-right corner of the screen to adjust the zoom percentage.

Figure 11-10: Page Break Preview shows default page breaks with heavy dashed lines, which you can reposition by dragging.

Using Page Break Preview lets you see both the positions of your page breaks and the page numbers Excel will use when you print. Default page breaks-the ones Excel proposes to use if you don't intervene-appear as heavy dashed lines. If you're not happy with the position of a default break, drag the line where you want it. Your page break then becomes a manual page break and appears as a solid line. To exit Page Break Preview, click either the Normal button or the Page Layout View button on the View tab.

Note 

After you apply any Page Setup options, Excel displays dashed lines in Normal view wherever a page break will occur. If you'd rather not see these lines, click the Microsoft Office Button, click Excel Options, select the Advanced category, and in the Display Options For This Worksheet section, clear the Show Page Breaks check box.

If you attempt to extend the dimensions of a page beyond the maximum width or depth according to the current page setup, Excel scales the worksheet to make it fit and displays an adjusted percentage value in the Scaling options shown on the Page tab in the Page Setup dialog box.

If, after trying different page breaks, you want to revert to the automatic page breaks (heavy dashed lines), drag the manual page breaks (solid lines) to the left past the row headers or up past the column headers. After you have removed all manual page breaks, the automatic breaks reappear in their default locations.

Inserting and Removing Manual Page Breaks

Spreadsheet pagination can be problematic, given that rows and columns of numbers often don't fit into the 8.5-by-11-inch world of printing. This is why Excel provides yet another method you can use to manually adjust page breaks. To add a page break in Normal view, select any cell of the row directly beneath or in the column directly to the right of where you want the break to occur, click the Breaks button on the Page Layout tab, and then click Insert Page Break. If you select a cell where data appears both below and to the right, Excel applies page breaks both horizontally and vertically. To remove a break, select a cell in the row below a horizontal break, or in the column to the right of a vertical break, click the Breaks button, and then click either Remove Page Break or Reset All Page Breaks.

TROUBLESHOOTING 

My manual page breaks don't work.

In the Page Setup dialog box, selecting the Fit To option on the Page tab can cause Excel to override manual page breaks. Fit To applies reduction sufficient to fit the entire print area into a specific number of pages. To derail this override, switch to the Adjust To option. This sets the worksheet to print according to your manual page breaks.

If you prefer the compressed Fit To "look" but still want to control page breaks, you can define the print area using multiple nonadjacent ranges-one range per page. Excel automatically prints each nonadjacent range as a separate page. Select the first range you want to print, hold down Ctrl, and select the next range. Select as many ranges (pages) as you want. Then click the Print Area button on the Page Layout tab, and click Set Print Area.