How to Insert a Page Break in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with a big data set, users may need to separate their data into different segments for the purpose of individual analysis as well as better visualization. Often, users face difficulties in printing datasets because they do not know how to insert a page break in Excel. Consequently, they fail to print the dataset in the proper way. This article will allow them to insert a page break where necessary and print the dataset in the desired way.

Table of Contents hide

How to Insert a Page Break in Excel: 4 Handy Approaches

1. Utilizing Page Layout to Insert a Page Break in Excel

1.1 Horizontal Page Break

1.2 Vertical Page Break

2. Applying Subtotal Command to Insert a Page Break

3. Using Excel Options to Insert Page Break in Excel

3.1 Horizontal Page Break

3.2 Vertical Page Break

4. Applying VBA Code to Insert a Page Break

Conclusion

How to Insert a Page Break in Excel: 4 Handy Approaches

Excel users often need to insert manual page breaks in Excel. Here, we will learn 4 effective ways to insert a page break in Excel. Firstly, we will use the Page Layout tab to insert a page break both horizontally and vertically. Afterward, we will use the Excel Options customization box to customize the Quick Access Toolbar to insert a horizontal as well as a vertical page break. Then, we will also introduce the use of the Subtotal command to do the same thing. Finally, we will use a VBA code to insert a page break. These methods will allow the users to insert a page break according to their requirements. Thus, users can print their data in a more convenient way than before. Here is a sample dataset that we will use throughout the article to explain the methods.

1. Utilizing Page Layout to Insert a Page Break in Excel

In the beginning, this may have been the easiest way to insert a page break in Excel. Here, we will use the Page Layout tab to access the Page Break command. In this method, we will insert both horizontal and vertical page breaks.

1.1 Horizontal Page Break

In this method, we will insert a horizontal page break in our dataset. We will use the above-mentioned dataset to explain the method. Follow the ensuing steps to accomplish the task.

Step 1:

First, select the row where you want to have the page break.
In this case, we will choose row 8.
As a result, the page break will appear between row 7 and row 8.

Step 2:

Next, go to the Page Layout tab.
After that, click on the Break tab.
Finally, from the drop-down list, select the Insert Page Break command.

Step 3:

Therefore, a horizontal page break in between row 7 and row 8 is shown in the image below.

1.2 Vertical Page Break

In addition to the previous method, we will now insert a vertical page break in our dataset. We will use the same dataset that we used to illustrate our previous methods. Follow the steps that come sequentially below.

Step 1:

Firstly, we will select the column where we want to have the page break.
In this case, we will choose column D.
Eventually, the page break will appear between column C and column D.

Step 2:

Secondly, go to the Page Layout tab.
Thirdly, click on the Break tab.
At last, from the drop-down list select the Insert Page Break command.

Step 3:

Consequently, we can clearly see the vertical page break in between column C and column D.

2. Applying Subtotal Command to Insert a Page Break

In the following method, we will use the Subtotal command. We will use the previously mentioned dataset to illustrate this method. Follow the steps below to do the task.

Step 1:

First, select any of the cells containing the dataset.
Here, we have selected the B4 cell.

Step 2:

Second, go to the Data tab in the ribbon.
From there, go to the Outline group.
Finally, from the drop-down list select the Subtotal command.
Consequently, the Subtotal dialogue box will appear.

Step 3:

Firstly, in the Subtotal dialogue box, go to the At Each Change in section.
From the drop-down list, select the column that is sorted or arranged in the order in your dataset.
In our case, the Product column is clearly sorted with all the Laptop entries followed by Mobile entries. This column will provide our subtotal group.
If your dataset is not sorted, sort it using the Sort command.
Next, from the Function type box, select any of the functions.
In this instance, we have selected the SUM function.
From Add Subtotal to tab, we have selected the of Sales option to show the subtotal of the two distinct product sales.
After that, check the checkbox saying, Page break between groups.
Finally, select OK.

Step 4:

At last, we will vividly see the page break separating the two groups in our dataset.

3. Using Excel Options to Insert Page Break in Excel

In this method, we will use Excel options to insert a page break. Here, we will use the Quick Access Toolbar to customize the page break.

3.1 Horizontal Page Break

Using the Excel Options customization box, we will insert a horizontal page break into our data set. Follow the outlined steps below to accomplish the task.

Step 1:

First, click on the File tab in the ribbon.

Step 2:

Secondly, from the side ribbon select the Options tab.

Step 3:

Click on the Quick Access Toolbar option from the Excel Options dialogue box.

Step 4:

From the Quick Access Toolbar, go to the box under the Choose command from option.
Next, select the All Commands option.
After that, from the command list below select the Insert Page Break command.
Now, click on Add.
Therefore, the Insert Page Break command will be added under the Customize Quick Access Toolbar box.
Finally, click OK.

Step 5:

Next, select the row where you will insert the page break.
In this case, we will select row 8.
Then, go to the top right corner of your workbook where the Quick Access Toolbar is located
From the Quick Access Toolbar, click on the icon indicating Breaks.

Step 6:

As a result, our horizontal page break is right between row 7 and row 8.

3.2 Vertical Page Break

Here, we will insert a vertical page break by using the Excel Options. Follow the outlined steps below to accomplish the task.

Step 1:

Firstly, click on the File tab in the ribbon.

Step 2:

From the side ribbon select the Options tab.
The Excel Options dialogue box will be opened.

Step 3:

Now, from the Excel Options dialogue box click on the Quick Access Toolbar option.

Step 4:

After that, from the Quick Access Toolbar go to the box under the Choose command from option.
Select the All Commands option.
Then, from the command list below select the Insert Page Break command.
Next, click on Add.
Consequently, the Insert Page Break command will be added under the Customize the Quick Access Toolbar option.
At last, click OK.

Step 5:

Next, select the column where you will insert the page break.
In our case, we will select column D.
Now, go to the Quick Access Toolbar in the top right corner of your workbook.
From the Quick Access Toolbar, click on the marked icon that indicates Breaks.

Step 6:

Finally, we will see the vertical page break between column C and column D.

4. Applying VBA Code to Insert a Page Break

This is an advanced but effective way to insert a page break. Here, we will use the Developer tab to apply a VBA code to insert a page break. Therefore, follow the ensuing steps to apply a VBA code. If you don’t see the Developer tab, then follow this link to display the Developer tab on the ribbon.

Step 1:

First, select the row in your dataset where you want to have a page break.
In this instance, we have selected the row containing the cell range B8:D8.

Step 2:

Go to the Developer tab -> Click on the Visual Basic tab -> A command window will appear.

Step 3:

In the command window, click on the Insert tab -> From the drop-down list select the Module option.

Step 4:

Now, insert the VBA code into the Module.

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

VBA Code Breakdown

The name of the function that we will use here in VBA is InsertPageBreak.
Here, we will take two Range type variables namely: selectedrange and currentCellvalue .
Then, we will assign the range to the selected cells as selectedrange=Application.Selection.Columns(1).Cells
ActiveSheet.ResetAllPageBreaks, this will reset all the page breaks if they exist.
We will write If (currentCellvalue.Value <> currentCellvalue.Offset(-1, 0).Value) with If statement to meet the criteria for breaking the page according to our selected range.
Finally we will use xlPageBreakManual command to break the page in a manual way.

Step 5:

To run the program, click on the Run command or press F5.

Step 6:

Consequently, we will see a page break in between our desired rows.

Download Practice Workbook

You can download the Practice Workbook from here.

Insert a Page Break.xlsx

Conclusion

In this article, we have learned 4 effective ways to insert a page break in Excel. These methods will allow users to manage and print their datasets properly. After reading this article, users can manually insert a page break into their data. This will make printing their dataset easier. If you find it useful, please let us know in the comment section below and share any recommendations and thoughts regarding this or any other content of ours. Thank you for your time. Goodbye!

How to Insert a Page Break in Excel (4 Easy Ways)

How to Insert a Page Break in Excel: 4 Handy Approaches