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.

Image1-Excel Page Break


Download Practice Workbook

You can download the workbook from here and practice yourself.


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.

Image2-Automatic vs Manual Page Break


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.

Image3-Inserting Horizontal Page Break

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

Image4-Horizontal Page Break Added


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.

Image5-Inserting Vertical Page Break

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

Image6-Adding Vertical Page Break

Read More: How to Insert a Page Break in Excel


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.

Image7-Adding Code

  • 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

Image8-Image of Code

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.

Image9-Running Code

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

Image10.1-Input the month name below which a page break will be inserted

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

Image11-View of page break


2. Use Subtotal Feature to Add Page Break

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

Image12-Inserting Page Break Using Subtotal

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

Image13-Setting Page Break Option in Subtotal

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

Image14-Page Break View Using Subtotal

Read More: How to Insert Multiple Page Breaks in Excel


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.

Image15-Preview of Page Break

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

Image16-Moving Page Break

  • We can see that the page breaks are moved accordingly.

Image17-Page Break is Moved

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.

Image18-Position of a vertical Page Break

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

Image19-Removing a Vertical Page Break

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

Image20-A single 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.

Image21-Preview of multiple-page break

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

Image22-Resetting all page breaks

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

Image23-All manual page break is reset

Read More: How to Remove Automatic Page Break in Excel


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.

Image23-Preview of page break

  • Now click on the File tab to open the options.

Image24-Navigating to File

  • Select Options.

Image25-Option selection

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

Image26-Hiding Page Break

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

Image27-Page Break is 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 SetupPrint in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo