This tutorial will demonstrate how to insert a page break in excel between rows. Page breaks are very helpful in case of breaking a worksheet into different portions for printing. It helps the viewer or the reader to understand the full report. Sometimes if a whole worksheet is put on just one page then it looks too messy and difficult to understand. So our goal is to learn how to insert page breaks in excel between rows.
Download Practice Workbook
You can download the practice workbook from here.
3 Easy Methods to Insert Page Break in Excel Between Rows
Our goal is to insert a page break between rows in a certain worksheet. For that, we can use any of the three methods described below:
1. Insert Page Break Between Rows Manually in Excel
In our first method, we can insert a page break between rows manually. It is the quickest and easiest way to insert a page break. So, follow the below steps to learn this method.
- First, select the proper dataset you want to use.
- After that, you will get the below result at first.
- Next, choose the desired row you want to insert the page break.
- Afterward, go to the Page Layout option and select the Breaks option.
- Then choose the Insert Page Break option to get the desired result.
- Finally, you will get results like the below image.
2. Apply Subtotal Command to Insert Page Break
Our goal is to use Subtotal Command to insert a page break between rows in a excel worksheet. This process is very useful if a conditional page break is needed. The method can be done by following the below steps.
- Initially, select the whole data table.
- Then, go to the Data tab and select the Sort option.
- Furthermore, the Sort window will open on the screen.
- Next, select the Sort by Name option and press OK.
- Afterward, go to the Subtotal option in the same Data tab as the previous step.
- After clicking OK, the Subtotal window will come onto the display.
- Then go to the ‘Add subtotal to’ option and tick the Name option.
- Subsequently, select the Page break between groups option and press OK.
- At last, you will get the result below.
3. Using VBA Code to Add Page Break
This time our goal is to add a page break in excel using VBA code. The process is a bit lengthy but if you are comfortable with codes then it is the best one. The steps are described below:
- Firstly, press Alt+F11 to open the VBA window.
- Secondly, press the Insert option and select Module to get the desired window.
- Thirdly, input the following VBA code
Sub InsertPageBreak() 'Declaring the data types of the variables' Dim selectionedrange As Range Dim currentCellvalue As Range 'Assigning value to the variables' Set selectionedrange = Application.Selection.Columns(1).Cells ActiveSheet.ResetAllPageBreaks 'Running the for loop' For Each currentCellvalue In selectionedrange If (currentCellvalue.Row > 1) Then If (currentCellvalue.Value <> currentCellvalue.Offset(-1, 0).Value) Then ActiveSheet.Rows(currentCellvalue.Row).PageBreak = _ xlPageBreakManual End If End If Next currentCellvalue End Sub
- Next, go to the Run option and select the Run Sub/UseForm F5 to run the VBA code.
- Lastly, you will get the result like the below image.
Remove Page Break in Excel
After our printing is done for a certain report then we can remove the page break to use the worksheet comfortably. So our goal is to remove the page break in excel by following the below steps.
- At first, select the table that already has page breaks inserted.
- Then, go to the Page Layout option and select the Breaks option.
- Afterward, choose the Reset All Page Breaks option.
- Finally, you will get the result below.
Things to Remember
- In the second method, the heading of each column must be marked. Otherwise, the Subtotal Command won’t work.
- In the case of using VBA code, it sometimes doesn’t work perfectly. So, we will recommend not to use it.
- In case of removing a page break, if you want just a one-page break then you should select the row and click on Remove Page Break option.
Henceforth, follow the above-described methods. Thus, you can learn how to insert a page break in excel between rows. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.