How to Use Page Break in Excel (7 Suitable Examples)

Page Break in Excel is used to separate a worksheet into different pages for printing. Excel automatically insert page break while printing. But it can also be inserted manually according to someone’s needs. The main objective of this article is to explain how to use page break in Excel.

How to Use Page Break in Excel: 7 Suitable Examples

Here, I will explain how to use page break in excel with 7 suitable examples. I will use the following dataset to explain this article.

1. Inserting Page Break in Excel

In this first type of use, I will explain how to insert Page Break in Excel. I will show you 3 different types of page breaks.

1.1. Inserting Vertical Page Break

In this example, I will explain how you can insert a vertical page break in Excel. To insert a vertical page break you must have to select an Entire Column.

Let’s see the steps.

Steps:

• Firstly, select the column where you want your page break.
• Secondly, go to the Page Layout tab.
• Thirdly, select Breaks.

Now, a drop-down menu will appear.

• Next, select Insert Page Break.

Here, you will see that you have inserted a vertical page break in your desired position.

Now, to get a better view you can go to the Page Break Preview.

• Firstly, go to the View tab.
• Secondly, select Page Break Preview from Workbook Views.

Here, in the following picture, you can see the Page Break Preview. The pages are shown clearly here.

After that, I will check the Print Preview to see how it will look after printing.

• Firstly, select the File tab.

• Secondly, select the Print option.

Now, you will be able to see how the Excel sheet will look after printing in the Print Preview.

1.2. Inserting Horizontal Page Break

In this example, I will explain how to use page break in excel by inserting horizontal page break. To insert a horizontal page break you must have to select a Row.

Let’s see the steps.

Steps:

• Firstly, select the row where you want your page break.
• Secondly, go to the Page Layout tab.
• Thirdly, select Breaks.

Now, a drop-down menu will appear.

• Next, select Insert Page Break.

Here, you will see that you have inserted a horizontal page break in your desired position.

Now, to get a better view you can go to the Page Break Preview.

• Go to the View tab -> Select Page Break Preview from Workbook Views.

Here, in the following picture, you can see the pages clearly in the Page Break Preview.

Finally, go to the Print Preview to check how it will look after printing.

1.3. Inserting Vertical and Horizontal Page Break Simultaneously

In this example, I will explain how to use page break in excel by inserting vertical and horizontal page break simultaneously.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want your page break.
• Secondly, go to the Page Layout tab.
• Thirdly, select Breaks.

Now, a drop-down menu will appear.

• Next, select Insert Page Break.

Here, you will see that you have inserted a vertical page break and a horizontal page break simultaneously in your desired position.

• Now, to get a better view go to the Page Break Preview following the procedure from Method-1.

Finally, go to the Print Preview to check how it will look after printing.

Read More: How to Insert Multiple Page Breaks in Excel

2. Moving Page Break in Excel

In this example, I will explain how to use page break in excel by moving page break.

Let’s see the steps.

Steps:

• Firstly, go to the View tab.
• Secondly, select Page Break Preview from Workbook Views.

Now, you will see the Page Break Preview. Here, you will be able to see the page breaks which were set automatically.

• After that, select the page break then move and drag it to your desired position.

In the following image, you can see that I have moved the horizontal page break to my desired position.

Now, in the same way, I have moved the vertical page break to my desired position.

Finally, go to the Print Preview to check how it will look after printing.

3. Removing Page Break in Excel

In this method, I will explain how you can remove page break lines in Excel. I will show you 2 different ways of doing it.

3.1. Removing Specific Page Break

In this example, I will show you how to remove a specific page break in Excel. In the following image, you can see the Excel sheet contains one horizontal page break and one vertical page break. Here, I will remove the vertical page break.

Let’s see the steps.

Steps:

• Firstly, go to the View tab.
• Secondly, select Page Break Preview from Workbook Views.

Now, Excel will show you the Page Break Preview.

• Right-click on a cell right to the vertical page break -> Select Remove Page Break.

Here, you will see the page break is removed.

Finally, go to the Print Preview to check how it will look after printing.

3.2. Resetting All Page Break

In this example, I will show you how to use page break in excel by reseting all page breaks in Excel. In the following image, you can see the Excel sheet contains one horizontal page break and one vertical page break. Here, I will remove all the page breaks at the same time.

Let’s see the steps.

Steps:

• Firstly,  go to the Page Layout tab.
• Secondly, select Breaks.

Now, a drop-down menu will appear.

• After that, select Reset All Page Breaks.

Here, in the following image, you can see that I have removed all the page breaks I inserted.

• Now, to get a better view go to the Page Break Preview following the procedure from Method-1. Here, you can see that the whole Excel sheet is on Page 1 now.

Finally, go to the Print Preview to check how it will look after printing.

Read More: How to Remove Automatic Page Break in Excel

4. Hiding Page Break in Excel

In this example, I will show you how you can hide page break from the Normal view in Excel. In the following image, you can see that page breaks are shown in the Normal view. Here, I am going to hide those.

Let’s see the steps.

Steps:

• Firstly, go to the File tab.

• Secondly, select Options.

Now, a dialog box named Excel Options will appear.

• Firstly, go to Advanced.
• Secondly, uncheck Show page breaks from Display options for this worksheet.
• Thirdly, select OK.

Now, you will see that the page breaks are not showing in the Normal view. But, they are still there.

• After that, to check if the page breaks are still there, go to the Page Break Preview following the procedure from Method-1.

Here, in the following image, you can see the page breaks in the Page Break Preview.

Finally, go to the Print Preview to check how it will look after printing.

5. How to use page break in excel with Pivot Table

In this example, I will show you how you can insert a page break in Excel into a pivot table. To explain this example I will use the following dataset.

Let’s see the steps.

Steps:

• Select the dataset -> Go to the Insert -> Select Pivot Table.

Now, a dialog box will appear. Which will automatically select your entire dataset.

• Select New Worksheet -> Select OK

Here, the PivotTable Fields list will appear on the right side of the screen.

• Firstly, select and drag the Sales column to the Values The PivotTable will display the sum of sales in the Sales column.
• Secondly, select and drag the State, and Month column to the Rows area. The PivotTable will display them as Row Labels.

Finally, you will get your Pivot Table.

• Right-click on any cell of the pivot table -> Select Field Settings.

Now, a dialog box named Field Settings will appear.

• Select Layout & Print -> Select Insert page break after each item -> Select OK.

Here, you will see that page breaks are inserted after each item.

• Now, to get a better view go to the Page Break Preview following the procedure from Method-1.

Finally, go to the Print Preview to check how it will look after printing.

6. Using Subtotal Command to Insert Conditional Page Break in Excel

In this example, I will explain how to insert a conditional page break in Excel using Subtotal. To explain this example, I will use the following dataset.

Let’s see the steps.

Steps:

• Firstly, select the dataset.

• Secondly, go to the Data tab.
• Thirdly, select Subtotal.

Now, the Subtotal dialog box will appear.

• Firstly, select where you want to add Subtotal. Here, I selected Sales.
• Secondly, select Page break between groups.
• Thirdly, select OK.

Here, you will see that page breaks are inserted after each group.

• Now, to get a better view go to the Page Break Preview following the procedure from Method-1.

Finally, go to the Print Preview to check how it will look after printing.

Read More: How to Insert Page Break in Excel Between Rows

7. Using VBA to Insert Conditional Page Break

I will explain how to insert conditional page break using VBA in this method.

Let’s see the steps.

Steps:

• Firstly, go to the Developer tab.
• Secondly, select Visual Basic.

Now, you will see the Visual Basic window has opened.

• After that, go to the Insert tab. 
• Now, select the Module option.

After that, you will see a Module has opened. In that Module type in the following code.

Sub InsertConditionalPageBreaks()
        Dim rangeSelected As Range
        Dim cellvalue As Range
        Set rangeSelected = Application.Selection
        ActiveSheet.ResetAllPageBreaks
        For Each cellvalue In rangeSelected
                If cellvalue.Value = "July" Then
                        ActiveSheet.Rows(cellvalue.Row + 1).PageBreak = _
                        xlPageBreakManual
                End If
        Next cellvalue
End Sub

Now, save the code and go back to your worksheet.

• Firstly, select the range of data where you want to apply your page break.
• Secondly, go to the Developer tab.
• Thirdly, select Macros.

Here, a dialog box will appear.

• Firstly, select the Sub procedure you defined in your VBA as the Macro name.
• Secondly, select Run.

Now, you will see that page breaks are inserted in your desired positions.

• Now, to get a better view go to the Page Break Preview following the procedure from Method-1.

Finally, go to the Print Preview to check how it will look after printing.

Read More: How to Insert Page Break Based on Cell Value with Excel VBA

Things to Remember

• It should be noted that whenever you are working with VBA in Excel, you must save the file as Excel Macro-Enabled Workbook. Otherwise, the file will not work.

Practice Section

Here, I have provided a practice sheet for you to practice how to use page break in Excel.

Download Practice Workbook

Conclusion

To conclude, in this article I have tried to explain how to use page break in Excel. I covered 7 different examples. I hope this was helpful for you. If you have any questions please, let me know in the comment section below.

Related Articles

• How to Insert Page Break Between Rows 39 and 40 in Excel
• [Solved]: Page Break in Excel Not Working Error
• How to Remove Print Lines in Excel
• How to Remove Blue Lines in Excel
• How to Print Borders at Page Break in Excel