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 need. The main objective of this article is to explain how to use page break in Excel.


Download Practice Workbook


7 Suitable Examples to Use Page Break in Excel

Here, I will explain 7 different uses of page break in Excel. I will use the following dataset to explain this article.

7 Suitable Examples to Use Page Break in Excel


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.

Inserting Page Break in Excel

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.

Inserting Page Break in Excel

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.

Inserting Page Break in Excel

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.

Inserting Page Break in Excel

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

Inserting Page Break in Excel


1.2. Inserting Horizontal Page Break

In this example, I will explain how you can insert a horizontal page break in Excel. 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.

Inserting Page Break in Excel

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.

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

Inserting Page Break in Excel

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.

Inserting Page Break in Excel


1.3. Inserting Vertical and Horizontal Page Break Simultaneously

In this example, I will explain how you can insert a vertical and horizontal page break simultaneously in Excel.

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.

Inserting Page Break in Excel

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.

Inserting Page Break in Excel

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 Ways)


2. Moving Page Break in Excel

In this example, I will explain how you can move page break in Excel.

Let’s see the steps.

Steps:

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

Moving Page Break in Excel

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.

Moving Page Break in Excel

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.

Moving Page Break in Excel

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

Moving Page Break in Excel


3. Removing Page Break in Excel

In this method, I will explain how you can remove page break 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.

  • Firstly, Right-click on a cell right to the vertical page break.
  • Secondly, 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 you can reset 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.

Removing Page Break in Excel

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.

Removing Page Break in Excel

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.

Removing Page Break in Excel

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

Removing Page Break in Excel

Read More: How to Remove Automatic Page Break in Excel (3 Easy Ways)


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.

Hiding Page Break in Excel

  • Secondly, select Options.

Hiding Page Break in Excel

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.

Hiding Page Break in Excel

  • 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. Inserting Page Break into a 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:

  • Firstly, select the dataset.
  • Secondly, go to the Insert
  • Thirdly, select Pivot Table.

Inserting Page Break into a Pivot Table

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

  • After that, select New Worksheet.
  • Next, 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.

  • Firstly, Right-click on any cell of the pivot table.
  • Secondly, select Field Settings.

Inserting Page Break into a Pivot Table

Now, a dialog box named Field Settings will appear.

  • After that, select Layout & Print.
  • Next, select Insert page break after each item.
  • Finally, select OK.

Inserting Page Break into a Pivot Table

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.

Inserting Page Break into a Pivot Table

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.

Using Subtotal to Insert Conditional Page Break in Excel

Let’s see the steps.

Steps:

  • Firstly, select the dataset.

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

Using Subtotal to Insert Conditional Page Break in Excel

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.

Using Subtotal to Insert Conditional Page Break in Excel

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.

Using VBA to Insert Conditional Page Break

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

Using VBA to Insert Conditional Page Break

Code Breakdown

  • Here, I have created a Sub procedure named InsertConditionalPageBreak.
  • Then, I declared a Variable named rangeSelected as Range, and another Variable named cellvalue as Range.
  • Next, I used the Set statement to assign Application.Selection to rangeSelected.
  • After that, I used an If statement, for each cellvalue in the rangeSelected the If statement will check if the Value in cellvalue is July. If the cellvalue matches July then it will return a Page Break in the next Row.
  • Then, I ended the If statement.
  • Finally, I ended the Sub procedure.

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.

Using VBA to Insert Conditional Page Break

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

Using VBA to Insert Conditional Page Break

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

Using VBA to Insert Conditional Page Break

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.


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. To get more articles like this visit ExcelDemy. If you have any questions please, let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo