How to insert and remove page breaks in Excel

The Excel page break option helps you see where page breaks will appear when your worksheet is printed. In this article I'll show you several ways to insert them manually or by condition. You'll also learn how to remove page breaks in Excel 2010 - 2016, where to find the Page Break Preview, hide and show the marking lines.

Page breaks are separators that divide a worksheet into individual pages for printing. In Excel, page break marks are inserted automatically according to the paper size, margin and scale options. If the default settings don't work for you, you can easily insert page breaks in Excel manually. It's really helpful for printing a table with the exact number of pages you want.

In this post, I'll show you how to use the Excel Page Break Preview to easily see the changes you make. Also, you'll see how you can adjust the page breaks in the worksheet before printing, how to remove, hide or show page breaks.

How to insert a page break in Excel manually

If you go to the Print Preview pane and don't like the way your Excel data is laid out for printing across several pages, you can manually insert page breaks where you need them. Below you'll find the steps showing how to do it.

  1. Pick your Excel worksheet where you need to insert page breaks.
  2. Go to the View tab in Excel and click on the Page Break Preview icon in the Workbook Views group.
    Go to the View tab and click on the Page Break Preview icon in the Workbook Views group

    Tip. You can also see where page breaks will appear if you click Page Break Preview Button image on the Excel status bar.
    Click Page Break Preview Button image on the status bar
    Note. If you get the Welcome to Page Break Preview dialog box, click OK. Tick the Do not show this dialog again check box to avoid seeing this message again.
  3. Now you can easily view the location of page breaks in your worksheet.
    See the location of page breaks in your worksheet

    • To add a horizontal page break, select the row where the marking line will appear. Right-click on this row and select the Insert Page Break option from the menu list.
      Select the Insert Page Break option from the menu list
    • If you need to insert a vertical page break, pick the necessary column to the right. Right-click on it and pick Insert Page Break.
      Pick the needed columns and select the Insert Page Break option from the menu list
    Tip. On more way of inserting page break in Excel is to go to the Page Layout tab, click Breaks in the Page Setup group and select the corresponding option from the drop-down list.
    Go to the Page Layout tab, click Breaks in the Page Setup group and select the option Insert Page Break
Note. If manual page breaks that you add don't work, you may have the Fit To scaling option selected (Page Layout tab -> Page Setup group -> click Dialog Box Launcher Button image -> Page). Change the scaling to Adjust to instead.

On the picture below, you can see 3 horizontal page breaks added. Therefore, if you go to Print Preview, you'll see different parts of data on separate sheets.
See 3 horizontal page breaks inserted

Insert a page break in Excel by condition

If you often print your data tables, you may want to learn how to automatically insert page breaks in Excel by condition, for example when a value in certain column changes. Say you have column named Category and you want each category to be printed on a new page.
See a table where you can automatically insert page breaks

Below, you'll find several helpful macros and the steps how to add page breaks using the Excel built-in Subtotal functionality.

Use macros to add the marking lines

Below you can find two really useful macros. They will remove all default page breaks in your table and will easily add new marking lines at the appropriate locations.

Just select the range of cells you want to use for splitting and avoid the headers.

  • InsertPageBreaksIfValueChanged - inserts page breaks if the value in the column changes.
  • InsertPageBreaksByKeyphrase - adds a page break each time it finds a cell that contains "CELL VALUE" (it's the entire cell, not part of it, don't forged to replace "CELL VALUE" in macro with your actual key phrase).

If you are novice in VBA, feel free to read How to insert and run VBA code in Excel 2010, 2013 - tutorial for beginners.

Sub InsertPageBreaksIfValueChanged()
    Dim rangeSelection As Range
    Dim cellCurrent As Range

    Set rangeSelection = Application.Selection.Columns(1).Cells
    ActiveSheet.ResetAllPageBreaks

    For Each cellCurrent In rangeSelection
        If (cellCurrent.Row > 1) Then
            If (cellCurrent.Value <> cellCurrent.Offset(-1, 0).Value) Then
                ActiveSheet.Rows(cellCurrent.Row).PageBreak = _ 
                	xlPageBreakManual
            End If
        End If
    Next cellCurrent

End Sub

Sub InsertPageBreaksByKeyphrase()
    Dim rangeSelection As Range
    Dim cellCurrent As Range

    Set rangeSelection = Application.Selection
    ActiveSheet.ResetAllPageBreaks

    For Each cellCurrent In rangeSelection
        If cellCurrent.Value = "CELL VALUE" Then
            ActiveSheet.Rows(cellCurrent.Row + 1).PageBreak = _
            	xlPageBreakManual
        End If
    Next cellCurrent
End Sub

Use subtotals to insert page breaks

Have you ever thought of Subtotal as of an option for inserting page breaks in Excel? This feature actually makes the process rather easy.

  1. Make sure your table has headers. For example, if column A contains category names, then cell A1 should have the label "Category." Make sure all columns in your table contain headers.
  2. Select the range with your data. Go to Data -> Sort -> Sort by Category. Click OK to see your data parts ordered:
    Go to Data -> Sort -> Sort by Category
  3. Select any cell within your table, go to the Data tab and click on the Subtotal icon.
  4. You will see the Subtotal dialog box.
    • Select your key column from the At each change in: drop-down list. In my table, it's Category.
    • Pick Count from the Use function list.
    • Select the correct checkbox in the Add subtotal to: group.
    • Make sure the Page break between groups check box is selected.
    • Click on OK.

    Make sure the Page break between groups check box is selected

You can delete the rows and cells with totals if you don't need them and get your table with page breaks automatically inserted according to the selected settings.

How to remove page breaks in Excel

Though it's not possible to remove page breaks that Excel adds automatically, you can easily delete those you inserted manually. You can choose to remove certain marking line or remove all manually inserted page breaks.

Delete a page break

Please follow the steps below to remove a page break in Excel.

  1. Select the worksheet where you want to delete the page break mark.
  2. Click on the Page Break Preview icon under the View tab or click Page Break Preview Button image on the status bar.
  3. Now select the page break you need to remove:
    • To delete a vertical break, select the column to the right of the line. Then right-click on it and select the option Remove Page Break.
    • To remove a horizontal page break, select the row below the line that you want to delete. Right-click on this row and pick the Remove Page Break option from the list.

    Pick the needed column or row and select the Remove Page Break option from the menu list

Tip. You can also delete a page break by dragging it outside the page break preview area.

Remove all inserted page breaks

If you need to delete all page breaks, you can use the Reset All Page Breaks functionality.

  1. Open the worksheet that you want to modify.
  2. Click on the Page Break Preview icon under the View tab or click Page Break Preview Button image on the status bar.
  3. Go to the Page Layout tab in the Page Setup group and click Breaks.

    Go to the Page Layout tab in the Page Setup group and click Breaks

  4. Pick the Reset All Page Breaks option.
Tip. You can also right-click any cell on the worksheet and select Reset All Page Breaks from the menu list.

Move a page break in Excel

One more option you may find helpful is dragging a page break to another location in a worksheet.

  1. Click Page Break Preview on the View tab or click Page Break Preview Button image on the status bar.
  2. To move a page break, just drag it to a new location.
    Drag the page break to a new location
Note. After you move an automatic page break, it becomes a manual one.

Hide or show page break marks

Below you'll find how to display or hide page breaks in the Normal view

  1. Click the File tab.
  2. Go to Options -> Advanced.
  3. Scroll down to the Display options for this worksheet group and tick or clear the Show page breaks check box.
    Tick or clear the Show page breaks check box

Now you know how to easily turn page breaks on or off in the Normal view.

Reset back to the Normal view

Now that all your page breaks found the correct location, you can return to Normal view. It's as simple as clicking on the Normal icon under the View tab in Excel.
Click on the Normal icon under the View tab in Excel

You can also click Normal Button image on the status bar.
Click Normal Button image on the status bar

That's it. In this article I showed how to use the Excel page break option. I tried to cover all its options and now you know how to insert, remove, show, hide and move page breaks to adjust them before printing. You've also got several helpful macros to add marking lines by condition and learnt to work in the Excel Page Break Preview mode.

Please let me know if you have any questions. Be happy and excel in Excel!

23 Responses to "How to insert and remove page breaks in Excel"

  1. baby says:

    Hi am sorry I followed everything in your instruction. But I cant removed the page break in my work book.
    There is anyway that I can removed the page break?

    Thank,

  2. Marisa says:

    yeah, these instructions are pretty much what I tried before searching for help. but no "remove page break" option offered when I right click

    • Hello, Marisa,

      Please try the following:
      Select the worksheet that you want to modify.
      On the View tab, in the Workbook Views group, click Page Break Preview. ...
      Do one of the following: ...
      On the Page Layout tab, in the Page Setup group, click Breaks.
      Click Remove Page Break.

    • Hamee says:

      Go to Page layout tab then

      Scale to fit Option

      Width 1 Page

      Height 1 Page
      (It Choose all in page one)

      Thank You

  3. me says:

    I know how to remove/add page breaks, however, for some reason I cannot move mine on one of my spread sheets. It is usually as easy as drag and drop, but does not seem to work on one particular sheet. Any ideas?

  4. sohail says:

    how to make same page break as one of above page in excel 2013 ?

  5. martin cygan says:

    i trying to insert page break and not working

    i right clicked liner where i wanted tab and then went to page layout and hit insert page break and did not work

    what am i doing wrong?

  6. Jikku says:

    I need excel work sheet page break short key.

  7. Abraham says:

    I need excel work sheet filter working, filtered condents not see another persons

  8. Michelle says:

    I want to know if there is a way to set row height to only columns A through J?
    Tried looking for a vertical column break, but do not know if excel has that option. I am not looking for a page break. I have data that I want to print pass column J.

  9. Alex says:

    I cant seem to insert a page break in excel 2013. I have tried going to Page layout>Page Setup>Breaks>Insert Page Break but it doesnt work. Please assist

  10. DRose says:

    Hello. I am attempting to manually move a page break, as I've always done, but instead of the page accommodating with the move by scaling down a bit, as it usually does, I get about 100 individual page breaks on my page. This has never happened before. The page automatically scales down to 10% and it has like I said about 100 page breaks. I have to undo to remove or scale the page back to 100%. I am not sure if I accidently changed something in my settings but I don't recall doing anything differently with the document. Is there anything I can do to avoid this? Thank you.

  11. Yasmin says:

    Hello,
    Microsoft Office version 2013.
    A worksheet, within a workbook, it's showing multiple page breaks. Every row has a page break. I have tried to use the menu to remove the breaks by reset, also by moving the blue line directly, but when I go to print, it goes back to the original form... showing page breaks on every row.

    I tried a macro too, didn't do anything.

    Thanks

  12. Anish Malhotra says:

    Hi,

    I was wondering if it was possible to apply multiple print formats (port/land) on the single worksheet and save that as a single PDF document.

    eg. I have a report with 210 rows and this is what I am trying to achieve.

    - Save Range (A1:L58) - as Landscape
    - Save range (A51:H150) as Portrait
    - Save range (A154:L200) as Landscape again.

    Can you please help??

    Cheers
    Anish

  13. Sandy says:

    Hello Maria

    I am facing an issue with the pivot tables. I have to draw some lines, format the numbers & their styles each time after refreshing it. I also tried Preserve the Format option but still after Refresh the lines I have drawn disappeared. Please suggest what should i do?

  14. Shawn Johnson says:

    I cant get delete a page break on my pivot table. Its there after each section on my pivot table.

  15. Ranjitha says:

    I have disable the display of page breaks ,but when I go to print and back to sheet page breaks are displayed.how can I permanently hide the page breaks?

    • Dot9000 says:

      I'm not sure what you mean by 'disable the display'. Are you in Normal view? If you are in Page Break Preview, it will show the breaks. Go to Normal view.

  16. shila says:

    how to filter data in excellent sheet

  17. ReMind says:

    Hello!
    Your blog is awesome and in the past I helped some people here.
    But, I can't found indications or a macro for establish break pages after vertically merged cells, to prevent page break across merged cells.
    Can you help me please?
    Thank in advance!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard