How to Remove Page Breaks in Excel?

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).

Click on Page Break Preview

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.

Page breaks are shown in dashed lines

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.
select the column to the right of the page break
  • To remove a horizontal page break, select the row that is just below the page break that you want to remove.
select the row below page break
  • 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.
Click on Remove Page breaks
  • 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.
Drag the page break lines

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.

Click on reset all page breaks

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.

Right-click and then click on reset all page breaks

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:

  1. Open the Excel workbook in which you want to remove the page breaks.
  2. Press Alt + F11 to open the VBA Editor.
  3. In the Project Explorer, right-click on any existing module or your workbook name, choose Insert, and then click on Module.
  4. In the new module window, paste the VBA code.
  5. Close the VBA Editor.
  6. 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).

Click on Normal

Alternatively, you can click on the Normal icon from the Excel status bar:

Click normal icon in 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. 

Dashed line that shows page breaks

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:

  1. Select the File tab.
Click on File
  1. Click on Options
Click on Options
  1. This opens the Excel Options box. From the list to the left of the box, select the ‘Advanced’ category.
Click on Advanced
  1. Scroll down to the category ‘Display options for this worksheet’.
Scroll to ‘Display options for this worksheet’.
  1. Uncheck the box next to ‘Show Page Breaks’ under this category.
Uncheck show page breaks
  1. Click OK to close the Excel Options box.

All the gray dotted lines should now disappear from the Normal view of your worksheet.

Dotted lines are not shown

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment