Breaking pages across printed sheets of paper is often challenging using word-processing programs, and it can be even more challenging when planning the printing strategy for large spreadsheet models. Excel 2010 helps you 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.
Clicking the Page Break Preview button results in a view of your worksheet like the one shown in Figure 11-14. 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.
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 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 File tab, click Options, select the Advanced category, and in the Display Options For This Worksheet section clear the Show Page Breaks check box. Note that you cannot change this option while Page Break Preview is active.
If you attempt to extend the dimensions of a page beyond the maximum width or depth specified for the current page setup, Excel scales the worksheet to make it fit. You can see the adjusted Scale percentage value in the Scale To Fit group on the Page Layout tab on the ribbon.
Figure 11-14. Page Break Preview shows default page breaks with heavy dashed lines that you can reposition by dragging.
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 remove all manual page breaks, the automatic breaks reappear in their default locations.
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 in 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. Excel applies page breaks both horizontally and vertically unless the selected cell is in the first row or column. 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 very well.
Selecting the Fit To option on the Page tab of the Page Setup dialog box can cause Excel to override manual page breaks. (Click the dialog box launcher button in the Page Setup group on the Page Layout tab to display the Page Setup dialog box.) The Fit To controls apply reduction sufficient to fit the entire print area onto 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 by 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. Click the Print Area button on the Page Layout tab, and then click Set Print Area.