Letting Excel decide where to insert page breaks can leave you with large gaps on pages. Here is how to view and change the page breaks before you print a spreadsheet.
Printing a multiple-page Microsoft Excel spreadsheet can be frustrating when the pages do not break where you want them to. While it is easy to manually insert a page break, it can often affect how the remaining pages flow. You can be left with large gaps on your pages, especially if the height of your rows varies.
Fortunately, Excel has the Page Break Preview feature, which lets you view how the pages in a spreadsheet will break before you print it. The preview screen gives you a much more detailed view than the one you would see using print preview. Plus, you can adjust any page breaks that are causing problems while in the preview screen.
The Page Break Preview feature is available in Excel 2007 and later versions. To access it, follow these steps:
- Open the spreadsheet you want to print.
- Select the “View” tab.
- Click the “Page Break Preview” button on the left side of the ribbon.
In the Page Break Preview screen, you can see how the rows and columns will break across the pages. Dashed lines indicate page breaks that Excel automatically added. Solid lines signify the page breaks manually added by users. If you do not like how the spreadsheet is breaking across the pages, you can move, add, or delete page breaks.
Moving Page Breaks
To move a page break, you simply drag its line to the desired location. You might notice that, when you move a dashed line, it becomes a solid one in its new location. This change is not a glitch. When you move a page break that Excel had inserted (i.e., an automatic page break), it becomes a manual one. This subtle difference is important if you decide to reset a spreadsheet’s page breaks, which will be discussed later.
To move page breaks in this manner, Excel’s drag-and-drop functionality needs to be working. This functionality is enabled by default. However, if you find that you cannot drag a page break line to a new location, you should do the following:
- Select the “File” tab. (In Excel 2007, click the Microsoft Office button.)
- Choose “Options” at the bottom of the left pane.
- Click “Advanced” in the left pane.
- Find the “Enable fill handle and cell drag-and-drop” check box in the “Editing options” section.
- If the box is not checked, click it to enable Excel’s drag-and-drop functionality.
Adding Page Breaks
Sometimes you might want to insert a page break into your spreadsheet. To do so, follow these steps:
- Highlight the row where you want to insert the page break. This row will be at the top of the new page. (If you want to insert a vertical page break, select the column to the right of where you want to insert the break.)
- Right-click.
- Select the “Insert Page Break” option.
Deleting Page Breaks
If you discover that a page break you added is hurting rather than helping the spreadsheet’s layout, you can delete it. The easiest method is to drag the page break line outside the preview area. You cannot delete page breaks that Excel has automatically added.
Occasionally, you might find that a spreadsheet has so many page breaks that the easiest way to get it to flow properly is to start from scratch. You can use the reset functionality to delete all the manual page breaks at once. Here are the steps:
- Right-click any cell in the spreadsheet.
- Select the “Reset All Page Breaks” option.
The spreadsheet will then go back to using to the page breaks that Excel added.
A Persistent View
Although you turn on the Page Break Preview feature on the “View” tab, the preview screen will remain displayed even if you go to different tabs, such as “Insert” and “Page Layout”. So, if you are more comfortable with inserting and removing page breaks using the traditional “Breaks” button on the “Page Layout” tab, you can use that option yet still see how the changes will affect the spreadsheet in the preview screen.
When you are done adjusting the page breaks, you can click the “Normal” button on the “View” tab to get back to the default spreadsheet view.
