When printing a document, a page break controls where one page ends and a new one begins – Today we’ll go over How to Remove Page Breaks in Excel.
In Microsoft Excel, page breaks separate the worksheet into individual pages, ready for printing, and are automatically inserted by Excel, based on the selected paper size, margin settings, and scale options.
Page breaks can also be inserted by the user to indicate the end of one section and the start of another, such as when printing out a yearly report, where each month is started on a new page.
We will be using the above contact list worksheet, which comprises 12 columns and over 500 rows, a perfect candidate for page breaks.
At the moment, it seems that there are no page breaks, but that is not the case.
There are always page breaks inserted by Excel, they are just not displayed so that they are not a distraction while working with the worksheet.
Page breaks are also of no importance unless you intend printing your worksheet, so Excel hides them by default.
So, the first order of business is to display any page breaks. One way to do this, is to put Excel in Page Break Preview mode.
From the Excel menu, select the View tab, and from the Workbook Views group, select Page Break Preview.
The view should now look something like the above snapshot, where blue dashed lines define the outline of each page.
When printed, these dashed lines indicate the limits of each page and is where page breaks will occur.
Also, a watermark at the center of each page shows the page numbering.
This is the order in which the pages will be printed.
It should be understood that these limits (or page breaks) cannot be manually altered, and are automatically set by Excel, based on the page size and orientation selected.
The above snapshot shows that the page size selected for the current worksheet is Letter.
This is available from the Page Layout tab’s Size option. The dropdown list offers a number of predefined paper sizes.
For the current worksheet, page breaks occur across the worksheet at the start of columns E and K, and down the page at the start of row 52 (there are of course more page breaks further down the worksheet).
If we now select the A4 page size, our page breaks occur at the start of columns E and J across the worksheet, and the start of row 56 down.
Under the Page Layout tab and in the Page Setup group, we can further modify the printing layout by changing the orientation from portrait to landscape, as well as the printing margins.
Also in the group is the Breaks option from where we can set and remove our own page breaks, but we will get to this in a moment.
Before returning to the normal view, it is worth taking a look at the Page Layout view (View tab, Page Layout option from the Page Setup group), which shows how the printed pages will look.
This is another way of determining where page breaks occur.
We can return to the normal worksheet view by selecting the Normal option from the View tab, Page Setup group.
Excel also provides the three Workbook views (Normal, Page Break Preview, and Page Layout), as tool icons in the bottom right hand corner of the workbook.
You can change views from either the top menu or footer icons, the choice is up to you.
Now, if we change something in the layout, like the width of any of the columns, and wanted to see how it affects the page breaks, it can become tiring having to constantly switch between Normal and Page Break Preview, or even Page Layout view.
As it turns out, we can ask Excel to display page breaks in normal view.
One way to do this is through Excel’s options.
From the Excel menu, select File > Options to bring up the Excel Options dialog box.
Next, go to the Advanced side tab, scroll down to the Display options for this worksheet section, and make sure Show page breaks is ticked.
Click the OK button to return to the worksheet, where the page breaks will now be visible as dashed lines.
The page breaks are now visible as discreet dashed lines in the snapshot above, between columns D and E, and J and K, and also between rows 51 and 52.
So far, all the page breaks were automatically set by Excel, and are dependent on the current page settings (e.g. size, orientation, margins).
But what happens when page breaks are set by the user?
In the above snapshot, user page breaks have been inserted after every 3 columns, as well as after the 30th row.
They appear as solid lines (compare this to dashed lines for page breaks inserted by Excel) and slightly thicker than the standard gridlines that separate columns and rows.
Despite this difference, it can be hard to pick them out from the worksheet’s gridlines.
One way to make them more distinct, is to turn off gridlines altogether, by going to File > Options > Advanced >Display options for this worksheet, and unticking the Show gridlines option.
You can always enable gridlines again, once you have finished modifying your page breaks.
Your worksheet should look something like the above snapshot, where any page breaks (be they Excel or user inserted), are now clearly discernible.
Obviously, no one wants to continuously go through the arduous process of opening the Options dialog box just to disable and enable gridlines.
Excel also provides this option from the View tab’s Show group.
Of course, you can also revert to using the Page Break Preview mode which was described earlier (from the menu, select View > Page Break Preview from the Workbook views group).
Use whichever method works best for you.
Regardless of whatever method we use, now that we can see page breaks, we can go ahead and describe how to go about removing them.
How to Remove Page Breaks in Excel
Assuming we want to remove the user inserted page break between columns F and G, all we need to do is select any cell in column G (which is the column to the right of the page break), and from the menu, select Page Layout > Breaks > Remove Page Break.
The result is shown in the snapshot above. Notice that there is no longer a page break between columns F and G.
However, Excel now automatically adds a page break after column G.
This is because for the current page setting, from column D where a user page break has been set, the current printable page width will only fit up to column G.
Notice also, that our other page break between columns I and J is still as it was.
One final thing worth mentioning is that user inserted page breaks appear as solid lines, whereas Excel inserted page breaks appear as dashed lines.
The previous method described how to remove page breaks across the screen.
To remove the page break down the screen, and specifically between rows 30 and 31, we would follow a similar procedure, this time selecting any cell in row 31 (i.e. the row directly below the page break), and then selecting the Page Layout > Breaks > Remove Page Break menu option.
So it’s a fairly simple process to remove user inserted page breaks from our worksheet.
But what if someone had inserted numerous page breaks across and down the worksheet, and we wanted to clear all of them?
Surely there must be a quicker way than removing them one at a time.
Fortunately, there is.
From the same menu group where we removed a single page break, we have another option that will clear all user created page breaks.
Page Layout > Breaks > Reset All Page Breaks
After clearing all page breaks, we are back to where we started from, with the only page breaks being those that Excel generates automatically.
Conclusion
If you intend printing your Excel worksheets, page breaks can help make those printouts a little bit more organized.
Oftentimes, it takes a bit of experimenting to get everything fitting nicely, or you may have a worksheet that has been set for one paper size and you want to print on another.
In such situations, you will find yourself having to Remove page breaks and create new ones.
Excel gives you the choice of a few different views where you can see where page breaks have been set.
It’s a matter of personal preference which is best, but once you know where the page breaks are, it is a fairly simple process to remove them, either one at a time, or all at once.
We hope this tutorial on how to Remove Page Breaks in Excel has been useful for your current task.
