Excel tutorial: Insert and delete page breaks in a spreadsheet

This tutorial shows you different methods and techniques for inserting page breaks into an Excel spreadsheet. You will learn to do this manually and also in an automatic way based on conditions. The tutorial also explains how to remove page breaks, show or hide page border lines, and preview page breaks in a spreadsheet.

To define page breaks, we can say that they are separators that divide a spreadsheet into different individual pages. Indeed, this is very important for a good impression of your workbook. By default, Excel inserts page breaks automatically into a spreadsheet based on different parameters such as margin, scale and paper size. However, if you see that the default settings do not suit you, you can do it yourself and insert the page breaks manually where you want. This way, you can print your dataset in the number of pages you want.

You can also preview page breaks to see the changes you’re making, and before you print your workbook you can make the changes you want: show, hide, or remove page breaks. Follow the rest of the tutorial for more details.

Table of contents

Insert a manual page break in Excel

Insert horizontal page breaks

Insert vertical page breaks

Insert a horizontal and vertical page break at the same time

Insert a page break in Excel by condition

Insert page breaks in all X lines in Excel

Use a macro to add tagging lines after each change

Use subtotals to insert page breaks

Modify a page break

Delete a specific page break

Remove all page breaks from a spreadsheet at the same time

Hide or show page break marks

Move a page break in Excel

Insert a manual page break in Excel :

You have just created a spreadsheet (workbook) and you want to print it out. By default, Excel uses measures such as paper size, margin settings and scale to automatically insert page breaks. However, to get discrete and clean pages without moving lines or text, you can manually insert a page break into the Excel spreadsheet.
There are two type of page breaks:

• Solid lines that are manually added page breaks.
• Dotted lines that are automatically added by Excel.

If you are in « Normal » mode in Excel, you can create manual page breaks, but the « With page breaks » view allows you to see more details. For example, it will allow you to see the difference between an automatic page break and a manual page break.

To insert manual page break, follow the steps below:

1. Select the Excel spreadsheet in which you want to insert page breaks.
2. Go to the «View» tab in the ribbon and click on « Page breaks» icon in the «View modes » group. You can now easily view the location of page breaks in your spreadsheet :

Alternatively, you can see where page breaks will appear if you click on the page break preview button image in the Excel status bar as shown below:

Note:  If you get the « Welcome to page Break preview » dialog box, click on « OK ». Check the « Do not show this dialog box» to avoid reviewing this message.

Excel offers two types of page breaks: vertical page breaks and horizontal page breaks.

We will first see how to insert horizontal page breaks.

Insert horizontal page breaks

1. Select an entire line just below the line where you want to skip the page.
2. Go to the « Page layout» tab , click  the  « Page breaks» button and select « Insert page breaks » :

3. Repeat these two steps in each place where you want to have a horizontal page break. So we get a page break that goes through our dataset , separating the data into uniform sections :

Note:  If you use a mouse ,  you can right-click on the line below or the column to the right of where you want the page to be skipped , then select « Insert a page break  » from the pop-up menu .

Now we are going to see how to insert vertical page breaks.

Insert vertical page breaks :

1. Select an entire column just to the right of where you want to insert the page break.
2. Go to the « Page layout» tab , click  the « Page breaks » button , and then click  « Insert page breaks » :

3. Now, as you can see below, we have a page break that goes down in the middle of our dataset instead  of cutting it :

  Insert a horizontal and vertical page break at the same time

1. Select the cell that is just below where you want to insert the horizontal page break and just to the right of where you want the vertical page break.For example to insert a horizontal page break from row 10 and another vertical from column C, we will select cell D11.

2. Go to the « Page layout » tab, click the « page breaks » button ,and  then click on « Insert a page break  » :

Therefore, we created a horizontal and vertical page break at the same time:

After the above step, you may find that the table is separated into four parts by continuous lines. And the watermarks « Page 1 », « Page 2 », « Page 3 » et « Page 4 » are displayed. Then, when printing this spreadsheet, four pages will be printed.

  Insert a page break in Excel by condition

If you often print tables in Excel, you may want to know how to automatically insert page breaks by condition (e.g. when a value in some columns changes). For example, if you have a column named «Student» and you want each student’s data to be printed on a new page, you can do so by using it as a condition.

Below you will find two efficient useful macros and the steps to add page breaks using Excel’s built-in subtotal functionality.

Insert page breaks in all X lines in Excel

1. Go to the spreadsheet where you want to have page breaks.
2. Right-click on the sheet name tab. This will open the management menu of the spreadsheet.

3. Select « View code», the « Microsoft Visual Basic for Applications » appears. You can also access this window via the «Developer» tab by clicking on the «Visual Basic» button or by simultaneously pressing theAlt+F11 keys.

4. In the « Microsoft Visual Basic for Applications» window, click on  « Insertion » and select « Module » :

5. Enter the following code in the code editor :

Sub SautDePageChaqueXLigne()
Dim XligneFin As Long
Dim Xfeuille As Worksheet
Set Xfeuille = Application.ActiveSheet
Xligne = Application.InputBox("Entrer le nombre de lignes", Xid, "", Type: = 1)
Xfeuille.ResetAllPageBreaks
XligneFin = Xfeuille.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = Xligne + 1 To XligneFin Step Xligne
Xfeuille.HPageBreaks.Add Before: = Xfeuille.Cells(i, 1)
Next
End Sub

6. Leave the window « Microsoft Visual Basic for Applications».
7. Go to the « Developer» tab and click the « Macros» button in the « Code» group.
8. Select « SautDePageChaqueXLigne» and click on  « Run » :

9. The « Enter number of lines» dialog box appears .let’s suppose that we have entered 4 :

10. Click OK and check the result. As we entered 4, page breaks are inserted after each four lines :

Use a macro to add tagging lines after each change

Below is a really useful macro. You can use it to remove all default page breaks in your table and easily add new tagging lines to the appropriate locations.

1. First select the range of cells you want to use for fractionation and avoid the headers.
2. Next, open the « Microsoft Visual Basic for Applications» window by clicking on the « Visual Basic »  button on the «Developer »  tab:

3. In the « Microsoft Visual Basic for Applications» tab , click on « Insertion » then select « Module» :

4. Copy and paste the following code into Module :

Sub SautDePageSiValeurChange()
    Dim PlageSelectionnee As Range
    Dim CelluleCourante As Range
    Set PlageSelectionnee = Application.Selection.Columns(1).Cells
    ActiveSheet.ResetAllPageBreaks
    For Each CelluleCourante In PlageSelectionnee
        If (CelluleCourante.Row > 1) Then
            If (CelluleCourante.Value CelluleCourante.Offset(-1, 0).Value) Then
                ActiveSheet.Rows(CelluleCourante.Row).PageBreak = _
                xlPageBreakManual
            End If
        End If
    Next CelluleCourante
End Sub

5. Leave the « Microsoft Visual Basic pour Applications» window.
6. Go to the « Developer» tab and click the « Macros» button to run the macro.
7. Enter the name of the « SautDePageSiValeurChange» macro in the reserved area and click on « Run » 

As you can see below, whenever the student name changes we have a page break that is inserted:

  Use subtotals to insert page breaks

Did you know that you can use the « Subtotal » feature as an option to insert page breaks into Excel? Indeed, this option makes the process quite easy.

1. Before you start, make sure that your table has headings. For example, if column A contains student names, cell A1 must be labelled « Student». Make sure that all columns in your table contain headings.
2. Select your DataSet.
3. Go to « Data » tab and click « sort » button:

4. In the  « Sort » dialog box , select « Student» in the « Sort by » area :

5. Click on OK.
6. Select any cell in your table , go to the « Data » tab and click the « Subtotal» icon  :

7. This opens the « Subtotal» dialog box.
8. Select your key column from the «À chaque changement de: » drop-down list. In our Example, it’s «Student».
9. Select« Sum» from the « Use function» list.
10. Check the appropriate boxes in the « Add a subtotal to:» group.
11. Make sure that  « Page breaks between groups» box is selected  :

12. Click on « OK ».

You can now delete rows and cells with subtotals in case you do not need them and you will get a table with page breaks automatically inserted according to the selected parameters.

Modify a page break

Excel offers three ways to modify page breaks:

• Delete a specific page break.
• Delete all manual page breaks.
• Move a specific page break.

Delete a specific page break

To delete a specific page break in your spreadsheet, follow these steps:

1. Go to the spreadsheet where you want to remove the page break marks.
2. Go to the « View» tab and click the « With page breaks » button or click the « Page breaks preview » icon in the status bar.

3. Select the row below or the column to the right of the page break you want to delete:

4. Go to the ribbon and select « Layout» tab .
5. In the « Layout» group, click  the  « page breaks » button and select  « Delete page breaks » :

Thus, the selected page break is deleted:

You can also remove a page break by dragging it out of the page break preview area.

  Remove all page breaks from a spreadsheet at the same time

To remove all page breaks in a spreadsheet, follow the steps:

1. Place the cursor Inside the desired spreadsheet, the one you want to remove the page breaks from.
2. Go to the « Page layout» tab , click the « Page breaks » button  and then click « Reset all page breaks » :

You can also right-click any cell on the spreadsheet and select « Reset all page breaks » from the pop-up menu.

You have just removed all page breaks from your spreadsheet. But note that there is always a dotted line, indicating a page break. This means that Excel has set page breaks to the default setting.

  Hide or show page break marks

Sometimes your Microsoft Excel spreadsheet will show you a series of black dotted lines that indicate page breaks for that spreadsheet. These page breaks usually appear once you have changed your view and returned to normal mode. This can be distracting or confusing, which can lead you to look for ways to remove them.

Fortunately, you can control it via a parameter that you can modify on the basis of an individual spreadsheet. This will allow you to hide page breaks if you no longer want to display them.

Below are the steps to show or hide page breaks in the normal view:

1. Click on « Folder » tab .
2. In the left pane of the Excel Window, Click on « Options ».
3. In the « Excel Options»dialog box, select « Advanced Options ».
4. Scroll to the   « Worksheet Display options» and check or uncheck the « Show page breaks » box :

You now know how to easily turn page breaks on or off in the normal view.

Move a page break in Excel

Another option that may be useful to you is to drag a page break to another location in a spreadsheet.

1. Click on « Page breaks» in the « View» tab or click on the « Page breaks preview » icon in the status bar.
2. To move a page break, hover over it and you will see that the mouse becomes two arrows. Click and drag the jump to a new location as shown below:

When you are done, and all your page breaks have found the right location, you can return to the normal view. You can easily access it by clicking on the « Normal » icon under the « View » tab.

That’s it. In this tutorial, we covered how to use the page break option in Excel. We tried to cover all the questions that arise about this and now you know how to insert, display, hide, move and remove page breaks to adjust them before printing. You also have effective macros that you can use to add tag lines under conditions and you have learned to work in Excel Page Break Preview mode.