Section 7.3. Controlling Pagination | Excel 2007[c] The Missing Manual

7.3. Controlling Pagination

Sooner or later it will happen to youyou'll face an intimidatingly large worksheet that, when printed, is hacked into dozens of apparently unconnected pages. You could spend a lot of time assembling this jigsaw printout (using a bulletin board and lots of tape), or you could take control of the printing process and tell Excel exactly where to split your data into pages. In the following sections, you'll learn several techniques to do just that.

7.3.1. Page Breaks

One of Excel's often overlooked but surprisingly handy features is manual page breaks . The idea is you tell Excel explicitly where to start a new page. You can tell Excel to start a new page between subsequent tables on a worksheet (rather than print a page that has the end of the first one and the beginning of the next ).

To insert a page break, move to the leftmost column (column A), and then scroll down to the first cell that you want to appear on the new page. Then, choose Page Layout Page Setup Breaks Insert Page Break. You see a dotted line that indicates the dividing lines in between pages (Figure 7-19).

Figure 7-19. Using a page break, you can make sure the second table (2006 Purchases) always begins on a new page. The dotted line shows where one page ends and the new page starts. When you add a page break, you see a dotted line for it, and you see a dotted line that shows you where additional page breaks naturally fall, based on your margins, page orientation, and paper size settings.

Tip: There's no limit to how many page breaks you can add to a worksheetif you have a dozen tables that appear one after the other, you can place a page break after each one to make sure they all start on a new page.

You can also insert page breaks to split your worksheet vertically into pages. This is useful if your worksheet is too wide to fit on one page, but you want to control exactly where the page break will fall. To do so, move to the first row, scroll to the column where the new page should begin, and then choose Page Layout Page Setup Breaks Insert Page Break.

You can remove page breaks one at a time by moving to an adjacent cell and choosing Page Layout Page Setup Breaks Remove Page Break. Or you can clear them all using Page Layout Page Setup Breaks Reset All Page Breaks.

7.3.2. Scaling

Page breaks are a nifty feature for making sure your printouts are paginated just the way you want them. However, they can't help you fit more information on a page. They simply allow you to place page breaks earlier than they would ordinarily occur, so they fall in a more appropriate place.

If you want to fit more on a page, you need to shrink your information down to a smaller size. Excel includes a scaling feature that lets you take this step easily without forcing you to reformat your worksheet.

Scaling lets you fit more rows and columns on a page, by shrinking everything proportionally. For example, if you reduce scaling to 50 percent, you fit twice as many columns and rows on a page. (Keep in mind that the font size in the printout will be smaller, and it may be hard to read.) Conversely, you can use scaling to enlarge your data.

To change the scaling percentage, just type a new percentage into the Page Layout Scale to Fit Scale box. The data still appears just as big on your worksheet, but Excel shrinks or expands it in the printout. To gauge the effect, you can use the Page Layout view to preview your printout, as described in Section 7.2.2.

Rather than fiddling with the scaling percentage (and then seeing what its effect is on your worksheet by trial and error), you may want to force your data to fit into a fixed number of pages. To do this, you set the values in the Page Layout Scale to Fit Width box and the Page Layout Scale to Fit Height box. Excel performs a few behind-the-scenes calculations and adjusts the scaling percentage accordingly . For example, if you choose one page tall and one page wide, then Excel shrinks your entire worksheet so that everything fits into one page. This scaling is tricky to get right (and can lead to hopelessly small text), so make sure you review your worksheet in the Page Layout view before you print it.

Tip: Page Break Preview mode, described next, gives you yet another way to squeeze more data onto a single page.

7.3.3. Page Break Preview: A Bird's-Eye View of Your Worksheet

You don't have to be a tree-hugging environmentalist to want to minimize the number of pages you print out. Enter the Page Break Preview, which gives you a bird's-eye view of how an entire worksheet's going to print. Page Break Preview is particularly useful if your worksheet is made up of lots of columns. That's because Page Break Preview zooms out so you can see a large amount of data at once, and it uses thick blue dashed lines to show you where page breaks will occur, as shown in Figure 7-20. In addition, the Page Break Preview numbers every page, placing the label "Page X" (where "X" is the page number) in large gray lettering in the middle of each page.

Figure 7-20. This example shows a large worksheet in Page Break Preview mode. The worksheet is too wide to fit on one page (at least in portrait orientation), and the thick dotted line clearly indicates that the page breaks after column G and after row 47. (Excel never breaks a printout in the middle of a column or row.)

To preview the page breaks in your data, select View Workbook Views Page Break Preview, or use the tiny Page Break Preview button in the Status bar. A window appears, informing you that you can use Page Break Preview mode to move page breaks. You can choose whether you want to see this message again; if not, turn on the "Do not show this dialog again checkbox before clicking OK.

Once you're in Page Break Preview mode, you can do all of the things you do in Normal view mode, including editing data, formatting cells , and changing the zoom percentage to reveal more or fewer pages. You can also click the blue dashed lines that represent page breaks, and drag them to include more or less rows and columns in your page.

Excel lets you make two types of changes using page breaks:

You can make less data fit onto a page . To do so, drag the bottom page break up or the left-side page break to the right. Usually, you'll perform these steps if you notice that a page break occurs in an awkward place, like just before a row with some kind of summary or subtotal .
You can make more data fit onto a page . To do so, drag the bottom page break down or the left-side page break to the left.

Of course, everyone wants to fit more information onto their printouts, but there's only so much space on the page. So what does Excel do when you expand a page by dragging the page break? It simply adjusts the scaling setting you learned about earlier (in Section 7.3.2). The larger you make the page, the smaller the Scaling percentage setting becomes. That means your printed text may end up too tiny for you to read. (The text on your computer's display doesn't change, however, so you don't have any indication of just how small your text has become until you print out your data, or take a look at it in Page Layout view.)

Note: Scaling affects all the pages in your printout. That means when you drag one page break to expand a page, you actually end up compressing all the pages in your workbook. However, the page breaks don't change for other pages, which means you may end up with empty, unused space on some of the pages.The best advice: If your goal is merely to fit more information into an entire printout, change the scaling percentage manually (Section 7.3.2) instead of using the Page Break Preview. On the other hand, if you need to squeeze just a little bit more data onto a specific page, use the Page Break Preview.