Page breaks in Excel can sometimes be a source of frustration.
If they fall into awkward places in your spreadsheet, it might break it non-intuitively during printout into separate sheets.
Even if you don’t plan on printing the sheet, the presence of those dotted lines across your worksheets can cause quite an annoyance.
In this tutorial, we will explain why you’re seeing these dotted page break lines and how you can remove the page breaks (or at least adjust them).
What is a Page Break in Excel?
A page break is nothing by a divider that marks areas where your worksheet will break into separate pages when printed.
In Excel you can have two types of page breaks:
- Page breaks that are automatically added by Excel (marked as dashed lines)
- Page breaks that are manually added by the user (marked as solid lines)
The automatic page breaks are inserted based on different settings like margin, scale, and paper size to ensure that you get an optimal number of rows and columns per page.
Manual page breaks are usually inserted by the user from the Page Layout->Breaks->Insert Page break menu.
Sometimes they are inserted by dragging the automatic page breaks too.
How to See Page Breaks in Excel
In order to remove a page break, you need to first see it. Page breaks appear light grey in color if you are in Normal view.
They appear blue in color if you are in the Page Break Preview.
To make any changes to your page breaks, you need to first be in Page Break Preview.
For this, select ‘Page Break View’ from the View tab (under the ‘Workbook Views’ group).
Once you are in Page Break Preview, you can move your page breaks about as required.
You also get a bird’ eye view of how your individual pages are going to be distributed during printing.
Note: To return to Normal view, simply select Normal from the View tab.
How to Remove Page Breaks in Excel
To remove page breaks, you have to first make sure you are in the Page Break Preview.
Page breaks might be vertical or horizontal. Vertical page breaks cause column-wise separation, while horizontal page breaks cause row-wise separation.
In Excel’s page break view, you have the option to delete both types of page breaks, and you can choose which page breaks you want to keep and which ones you want to remove.
- To remove a vertical page break, select the column to the right of the page break that you want to remove.
- To remove a horizontal page break, select the row that is just below the page break that you want to remove.
- Next, from the Page Layout tab, select Breaks (from the Page Setup group). This will display a dropdown list with different page break options. Select ‘Remove Page Break’ from this list.
- An alternative way to remove a page break is by dragging it left, past the row headers or up, past the column headers, until it is outside the page break view area.
Note: You can only remove the manual page breaks. Excel does not allow you to remove the automatic ones.
Resetting all Manually Inserted Page Breaks
Instead of removing page breaks one by one, you could also reset all page breaks.
This will remove all the manually added page breaks from your worksheet in one go.
To reset all page breaks, select Breaks (under the Page Setup group) from the Page Layout tab.
This will display a dropdown list with different page break options. Select ‘Reset all Page Breaks’ from this list.
Alternatively, you can right-click on any cell in the worksheet (while you’re in Page Break Preview) and select ‘Reset all Page Breaks’ from the context menu that appears.
Note: When you reset all page breaks, your worksheet gets reset to display only the automatic page breaks.
Remove All Page Breaks Using VBA (from All Sheets)
If you’re more comfortable using VBA, below is the VBA code that will remove all the page breaks from the workbook in which the macro is run.
Sub RemovePageBreaksFromAllSheets() Dim ws As Worksheet ' Loop through each worksheet in the workbook For Each ws In ThisWorkbook.Worksheets ' Set the display of page breaks to False ws.DisplayPageBreaks = False ' Remove all manual page breaks ws.ResetAllPageBreaks Next ws End Sub
The above macro removes page breaks from all sheets by iterating through each worksheet and setting DisplayPageBreaks property to False to hide the page breaks, and then using the ResetAllPageBreaks method to remove manual page breaks.
While automatic page breaks aren’t removed, Excel recalculates them according to the worksheet’s current layout.
Below are the steps on how to use this code and where to place it in Excel:
- Open the Excel workbook in which you want to remove the page breaks.
- Press Alt + F11 to open the VBA Editor.
- In the Project Explorer, right-click on any existing module or your workbook name, choose Insert, and then click on Module.
- In the new module window, paste the VBA code.
- Close the VBA Editor.
- Press Alt + F8, select the macro name, and click Run.
Returning to Normal View from Page Break Preview
Once you’re done working with your page breaks from your Page Break Preview, you can return to Normal view by clicking on ‘Normal’ from the View tab (under the ‘Workbook Views’ group).
Alternatively, you can click on the Normal icon from the Excel status bar:
When you return to Normal view after working in Page Break Preview, you might still see the page breaks (in grey color this time). This is because the page breaks get turned on automatically.
If you don’t want to see these dotted lines, simply close and reopen your worksheet without saving.
If you still see the grey dotted lines, then follow the steps below to hide the page breaks:
- Select the File tab.
- Click on Options.
- This opens the Excel Options box. From the list to the left of the box, select the ‘Advanced’ category.
- Scroll down to the category ‘Display options for this worksheet’.
- Uncheck the box next to ‘Show Page Breaks’ under this category.
- Click OK to close the Excel Options box.
All the gray dotted lines should now disappear from the Normal view of your worksheet.
Note: The above steps apply to only the current workbook (in which you unchecked the ‘Show Page Breaks’ option. To apply this to other workbooks you will need to repeat the process each time.
In this tutorial, we showed you how to see, adjust and remove page breaks from your Excel worksheets.
Removing or adjusting page breaks from your worksheet can help give you a better-structured printout of your data.
Moreover, removing (or at least hiding) the page breaks will help get rid of the blue or gray dotted page break lines, thereby giving you a cleaner, neater canvas to continue processing your data.
Other articles you may also like:
- How to Fit to Page in Excel (Print on One Sheet)
- How to Remove Dotted Lines in Excel
- How to Remove Panes in Excel Worksheet (Shortcut)
- How to Set a Row to Print on Every Page in Excel
- How to Center the Worksheet Horizontally on the Page in Excel
- How to Make all Cells the Same Size in Excel (AutoFit Rows/Columns)
- How to Print Row Numbers in Excel
- How to Print Gridlines in Excel
- How to Remove Gridlines in Excel (Shortcut + VBA)
- How to Show Ruler in Excel? (Ruler Grayed Out)