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

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.


Download Practice Workbook

You can download the Practice Workbook from here.


4 Handy Approaches to Insert a Page Break in Excel

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, the 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.

Sample Data


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 in between row 7 and row 8.

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 3:

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

4 Handy Approaches Ways to Insert a Page Break in Excel

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 in between column C and column D.

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 3:

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

4 Handy Approaches Ways to Insert a Page Break in Excel

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


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.

4 Handy Approaches Ways to Insert a Page Break in Excel

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
  • From the drop-down list, select the column that is sorted or arranged in 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.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 4:

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

4 Handy Approaches Ways to Insert a Page Break in Excel

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


3. Using Excel Options to a 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

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

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 2:

  • Secondly , from the side ribbon select the Option tab.

4 Handy Approaches Ways to Insert a Page Break in Excel4 Handy Approaches Ways to Insert a Page Break in Excel

Step 3:

  • From the Excel Options dialogue box, click on the Quick Access Toolbar option.

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 6:

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

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 2:

  • Secondly , from the side ribbon select the Option tab.
  • Consequently, Excel Options dialogue box will be opened.

4 Handy Approaches Ways to Insert a Page Break in Excel

Step 3:

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

4 Handy Approaches Ways to Insert a Page Break in Excel

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.

Sample Data

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.

Sample Data

Step 6:

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

Sample Data

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


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.

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.

Sample Data

Step 2:

  • Secondly, go to the Developer tab.
  • Next, click on the Visual Basic tab.
  • Consequently, a command window will appear.

Sample Data

Step 3:

  • Thirdly, in the command window, click on the Insert tab.
  • Next, from the drop-down list select the Module option.

Sample Data

Step 4:

  • Now, insert the VBA code into the Module.

Sample Data

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.

Sample Data

Step 6:

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

Sample Data

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


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 dataset properly. After going through 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. Follow ExcelDemy.com to for more articles like this.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo