Page Break in Excel (Insert, Move, Remove and Fix)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to learn everything about Excel page break. We’ll focus on how to insert page breaks manually as well as based on conditions. Also, we’ll cover how to move or even delete page breaks if the need arises.

Hiding or showing page breaks is also on our list. We will demonstrate the method in detail. Note that we need to use page breaks to break a worksheet into separate pages. The picture below gives us an overview of how our worksheet will look after printing.

Download Practice Workbook

You can download the workbook from here and practice yourself.

Use of Page Break.xlsm

Table of Contents Expand

Automatic vs Manual Page Break in Excel

There are two types of page breaks: automatic (Excel applies these page breaks automatically) and manual (users can set the page break according to their needs). Automatic page breaks have dotted lines whereas manual page breaks have solid lines.

To view page breaks, we need to navigate to View >> Page Break Preview.

How to Insert Page Break Manually in Excel

We can insert two types of page breaks: horizontal and vertical page breaks. You can learn how to insert them from the following segment.

1. Insert Horizontal Page Break

Select the row below which you need to add the page break. We have selected row 12 in this example.
Navigate to Page Layout >> Breaks and select Insert Page Break.

As a result, we can see a page break above row 12.

2. Add Vertical Page Break

Select the column before which you want to add the vertical page break. We’ve selected column E in this example.
Navigate to Page Layout >> Breaks and select Insert Page Break.

As a result, a page break is inserted between columns D and E.

How to Insert Page Break Based on Condition

1. Use Macro to Insert a Conditional Page Break

Right-click on the worksheet and click on the View Code option.

Type the following VBA code in the code window.

Sub ConditionalPageBreakInsertion()
    Dim selectedRange As Range
    Dim valueOfCell As Range
    Dim searchTerm As String
    searchTerm = InputBox("Enter the last month name:")
    Set selectedRange = Application.Selection
    ActiveSheet.ResetAllPageBreaks
    For Each valueOfCell In selectedRange
        If valueOfCell.Value = searchTerm Then
            ActiveSheet.Rows(valueOfCell.Row + 1).PageBreak = xlPageBreakManual
        End If
    Next valueOfCell
End Sub

Code Breakdown

We’ve used a sub-procedure named ConditionalPageBreakInsertion.
We’ve declared selectedRange to store the selected range, valueOfCell to iterate through each cell in the selected range and searchTerm variable to store the search term that the user inputs.
The InputBox takes input from the user and stores it in the searchTerm variable.
The selectedRange variable stores the selection made by the user in the worksheet.
The ResetAllPageBreaks method removes any existing page break.
Then, the For Each statement and If statement searches the searchTerm in the selection and adds a manual page break after the row containing the matching cell.
The End Sub statement ends the subroutine.
Now, select the dataset without the headers and run the macro to insert the page break.

An InputBox will show up where we will type May as the last month.
Note that we want to add a page break after every May in our dataset.

We can see a page break after every May present in the worksheet.

2. Use Subtotal Feature to Add Page Break

Select the entire dataset (B5:D25) first.
Navigate to Data>Outline>Subtotal.

In the Add subtotal to: option, select Sales.
Enable Page break between groups and click on OK.

Navigate to View >> Page Break Preview to check out the page breaks.

How to Move Page Break in Excel

First, navigate to View >Page Break Preview to check out the page breaks. We can see page breaks between columns H and I and rows 21 and 22.

Now we’ll Left click and drag the page breaks to move them between columns G and H and rows 19 and 20 respectively.

We can see that the page breaks are moved accordingly.

Read More: How to Move Page Breaks in Excel

How to Delete Page Break in Excel

1. Delete a Single Manual Page Break

We need to remove the highlighted page break between columns H and I.

Select a cell (I1) and Right-click on the cell.
Select the Remove Page Break option to remove the page break.

As a result, we can see that the vertical page break is removed.

2. Reset All Manual Page Breaks

We can reset all the manual page breaks with a single click.

To check the page breaks, navigate to View >> Page Break Preview.

Now, to reset all the page breaks, go to Page Layout >> Breaks >> Reset All Page Breaks.

As a result, we can see that all the page breaks are reset.

How to Hide or Show Page Break in Normal View

To get a preview of the page breaks, navigate to View > Page Break Preview. We can see the page break in the following image.

Now click on the File tab to open the options.

Select Options.

Now Excel Options will pop up. Navigate to Advanced >> uncheck Show page breaks and click on OK to hide the page break between rows 11 and 12.

We can see from the following image that the page break is now hidden.

Things to Remember

Page break is inserted above the selected row and to the left of the selected column.
By default, Excel adds automatic page breaks. You can always adjust them later according to your needs.
We can use the ResetAllPageBreaks method to reset all page breaks in a worksheet.

Conclusion

In this article, we’ve demonstrated all the possible aspects of Excel Page Break. We’ve covered the use of Page Break, and how to insert, move or remove them. We’ve also covered how to add Page Breaks in rows. You may apply this in your own situations according to your need. I’ve focused on describing all the procedures as simply as possible. Being said that, if you face any problem implementing these methods, feel free to let me know in the comment section. I’ll try to solve your problem. Have a good day!

Frequently Asked Questions

1. What is the page break limit in Excel?

You can add up to 1026 horizontal page breaks in a single worksheet.

2. What is the shortcut to insert a page break in Excel?

Ctrl+Shift+Enter is the shortcut to insert a page break in Excel.

3. What is the print area in Excel?

The print area is the range of cells that we select to print when we don’t want to print the entire worksheet.

Excel Page Break: Knowledge Hub

<< Go Back to Page Setup | Print in Excel | Learn Excel