Excel Insert Page Break Every Row: How To

To insert a page break before every row in Excel, you can use the built-in “Insert Page Break” command row by row, or automate it with a simple VBA macro that loops through each row and adds a horizontal page break. This article explains both methods, how to remove unwanted breaks, and common pitfalls like breaks that shift after editing.

How Page Breaks Work in Excel

Excel separates a worksheet into pages for printing based on the paper size, margins, and scale settings. A manual page break is a divider you insert yourself to force the next row (or column) to start on a new page. Excel shows manual breaks as solid lines in Page Break Preview, while automatic breaks appear as dashed lines.

When you insert a page break above a specific row, that row becomes the first row of a new printed page. To insert a break above every row, you would need to repeat the action for each row—or use a macro to do it in one step.

Method 1: Manual Insertion (For a Few Rows)

If you only need breaks above a handful of rows, the manual method is quick:

1. Select the row below where you want the break (e.g., select row 5 to put a break above row 5).
2. Go to the Page Layout tab.
3. Click Breaks > Insert Page Break.

Repeat for each row. To insert breaks above every row, you’d have to do this for each row—tedious for large sheets, which is why a macro is better.

Method 2: VBA Macro to Insert a Break Above Every Row

For a sheet with many rows, a VBA macro can insert a horizontal page break above each row in a selected range. Here’s how to add it:

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the following code:

Sub InsertPageBreakEveryRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Long
    Set ws = ActiveSheet
    Set rng = Selection
    ' Clear existing manual breaks
    ws.ResetAllPageBreaks
    ' Loop through each row in the selection
    For r = rng.Rows(1).Row To rng.Rows(rng.Rows.Count).Row
        ws.HPageBreaks.Add Before:=ws.Rows(r)
    Next r
End Sub

4. Close the editor, select the rows where you want breaks (e.g., A1:A100), and run the macro (Alt+F8, choose it, Run).

This macro first clears all manual breaks, then adds a break above each row in the selected range. Note that Excel has a limit of 1026 horizontal page breaks per sheet, so this works only for up to 1026 rows.

Removing Page Breaks

To remove a single manual break, select the row below it, go to Page Layout > Breaks > Remove Page Break. To remove all manual breaks, click Breaks > Reset All Page Breaks. The VBA macro above uses ResetAllPageBreaks to clear existing breaks before adding new ones.

Common Problems and Pitfalls

• Breaks move when you insert/delete rows: Manual page breaks are attached to the row above them, so inserting a row above shifts the break. This can break your layout.
• Breaks don’t appear in Normal view: They only show as solid lines in Page Break Preview (View tab > Page Break Preview).
• Scaling overrides manual breaks: If you set the print scaling to “Fit Sheet on One Page”, Excel may ignore manual breaks. Set scaling to “No Scaling” or a specific percentage.
• Hidden rows and breaks: If you hide rows, page breaks may behave unexpectedly because they refer to row positions.
• Macro limit: The 1026 horizontal page break limit means you cannot have a break above every row in a sheet with more than 1026 rows.

Alternatives to Page Breaks per Row

If you want each row to print on its own page, consider these alternatives:

• Print Area per Row: Set a print area for each row, but that’s also tedious.
• Use a Report Layout: If your data is in a table, you can use the “Print Titles” to repeat header rows, but that doesn’t separate rows.
• Export to PDF with page breaks: Some PDF printers allow you to split pages by row count, but that’s outside Excel.
• Use a different tool: For a list that must print one item per page, consider a mail merge or a dedicated reporting tool.

In most cases, inserting a break above every row is only useful for specific forms or certificates where each record must be on its own sheet. For large datasets, it’s usually better to use the macro and then export to PDF.

Frequently Asked Questions

Can I insert page breaks above every row without VBA?

Yes, but only manually—select each row and insert a break. There’s no built-in command to do it for all rows at once. VBA is the only practical way for more than a few rows.

Why don’t my page breaks show up when I print?

Check that you’re not using “Fit to Page” scaling, which overrides manual breaks. Also, ensure you’re in Page Break Preview to see them. If breaks are there but not printing, check your print area—if a print area is set, only that range prints.

Does inserting a page break above every row affect the file size?

No, page breaks are stored as metadata and don’t significantly increase file size. However, having many breaks can slow down printing and page preview rendering.

Can I insert both horizontal and vertical page breaks?

Yes, you can insert horizontal (above rows) and vertical (left of columns) breaks. The macro above only adds horizontal breaks; you could adapt it to add vertical breaks as well.

What is the maximum number of page breaks in Excel?

Excel allows up to 1026 horizontal and 1026 vertical page breaks per worksheet. This is a hard limit, so you cannot have a break above every row in a sheet with more than 1026 rows.

How do I remove all page breaks at once?

Go to Page Layout > Breaks > Reset All Page Breaks. This removes all manual breaks, but automatic breaks will still appear based on your print settings.

Related

• Is Your Code Really Using a Design Pattern?
• Marshall Headphones Wireless Charging Support Explained
• Bonus Stages in Arcade History: A How-To Guide