How to insert page break every x rows in Excel?

When preparing large Excel worksheets for printing, you might find it necessary to insert page breaks at regular intervals—such as after every few rows—to improve readability and ensure each printed page contains consistent, well-organized data. While adding a single page break is straightforward in Excel, inserting breaks at repeating intervals (for example, every3 or10 rows) can quickly become a tedious task if done manually, especially for extensive datasets. In this article, you will learn several practical solutions for automatically inserting page breaks after every x rows, allowing for more professional and manageable printouts.

• Insert page break every X rows with VBA in Excel
• Batch insert page breaks after every x rows
• Batch insert page breaks after every x rows with adding row heading above every x rows
• Manually insert page breaks using Page Break Preview

Insert page break every X rows with VBA in Excel

If you need to insert page breaks after a specific number of rows in Excel, VBA provides an efficient automated solution, especially for large datasets. Even if you are new to working with macros, you can follow the process below. This example demonstrates how to insert a page break every 3 rows, but you can specify any interval you need during execution.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications (VBA) editor—this is Excel’s built-in tool for writing and running macro code.

2. In the VBA editor that appears, click Insert > Module to create a new module. Then copy and paste the following VBA code into the newly created module window.

VBA: Insert page break in every X rows in a worksheet.

Sub InsertPageBreaks()
'Updateby20140618
Dim xLastrow As Long
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
xRow = Application.InputBox("Row", xTitleId, "", Type:=1)
xWs.ResetAllPageBreaks
xLastrow = xWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = xRow + 1 To xLastrow Step xRow
    xWs.HPageBreaks.Add Before:=xWs.Cells(i, 1)
Next
End Sub

3. To run the code, click the Run button (or press F5). You will see a dialog box prompting you to enter the number of rows after which you want to insert page breaks. For example, entering "3" will add a page break after every third row.

4. After you click OK, the script will automatically place a page break after every specified interval of rows, throughout your active worksheet.

When using VBA for page breaks, make sure to check your worksheet for any pre-existing manual page breaks, as repeated script runs can create overlapping page breaks and produce unexpected print results. If you need to remove all breaks before rerunning the macro, use the Reset All Page Breaks option under the Page Layout tab beforehand. Remember to save your file before running VBA scripts, as macros can’t be undone using the regular Undo function.

This approach is best for users comfortable with basic editing in Excel and those dealing with evolving or large data ranges. However, if frequent adjustments or non-standard ranges are required, consider using more flexible or GUI-assisted solutions, such as the ones below.

Batch insert page breaks after every x rows with Kutools for Excel

If you have Kutools for Excel installed, you can significantly simplify batch insertion of page breaks. The Split to Columns utility in Kutools enables you to insert page breaks at regular intervals and keep the row headings visible at the top of each new page. This is especially useful for reports or tables where repeated headings boost clarity and professionalism, and where print layout consistency is a priority.

1. Click Split to Columns from the Kutools Plus tab to access the utility.

2. In the Split to Columns dialog box, configure the following settings for precise control:

(1) Use the button in the Titles range box to select the row containing your column headings. This ensures headers will repeat on each printed page.
(2) Click the button in the Date range box to select the main data range you wish to split.
(3) In the Rows per printed page box, enter the number of rows you want on each printed page (e.g., enter3 for a break every 3 rows; use 1 for a break after every row).
(4) Enter 1 in the Number of segments box to split the sheet accordingly.

3. Click Ok to process the data.

The utility will output your selected range to a new worksheet, automatically inserting page breaks as specified and repeating the header row. For verification, switch to the Page Break Preview mode by going to the View tab and selecting Page Break Preview—here, blue lines will clearly show page breaks. 

This method is suitable for users needing repeated headings and precise control over dataset segmentation for printing. Note that the original data is not altered; results appear in a new worksheet, which is helpful for preserving the source.

Insert page breaks after every x rows with Kutools for Excel

Kutools for Excel also offers a dedicated Insert Page Break Every Row utility, allowing you to insert page breaks after every specified number of rows quickly and easily within your selected range. This tool is convenient for both quick batch operations and situations that require flexible intervals without repetitive manual input. Particularly, it is helpful for data tables that don’t require repeated headings but do need uniform page partitioning before printing.

1. Select the data range for which you wish to insert page breaks, then navigate to Kutools Plus > Printing > Insert Page Break Every Row.

2. In the pop-up Insert Page Break Every Row dialog, enter your desired interval—for example, entering “1” will add a page break after every other row. Click Ok.

As soon as you confirm, all page breaks will be set according to your specified row interval. Refresh the view with Page Break Preview (from the View tab) if you want to visually confirm the breaks.

Manually insert page breaks using Page Break Preview

For users working with smaller datasets or requiring custom, non-uniform intervals for page breaks, Excel’s built-in Page Break Preview view allows direct manual control. This approach is ideal for cases in which automated solutions are unnecessary or offer too little flexibility.

1. Switch to View > Page Break Preview from the ribbon. This displays your worksheet with bold blue lines that represent existing page breaks.

2. To add a break, select the first cell of the row below where you want the new page to begin, then go to the Page Layout tab, click Breaks > Insert Page Break.

3. Repeat the process for each desired break. If you need to adjust a page break, simply drag the blue line to a new position.

This manual method is straightforward for small or infrequent tasks but not recommended for large volumes of pages, as it quickly becomes time-consuming. If many regular breaks are needed, automated VBA or Kutools solutions are much more efficient.

When inserting page breaks in Excel, consider the following tips and error reminders: Always preview your worksheet using the Page Break Preview mode to ensure page breaks appear exactly as desired before printing. If you encounter errors such as unexpected blank pages or misaligned breaks, double-check your selected range, ensure no overlapping page breaks exist, and remove any unnecessary manual breaks if needed. Remember, Excel has a maximum number of page breaks per worksheet—if your range is very large, you may need to adjust your approach or split printing tasks. If you need to undo inserted breaks, you can use the Reset All Page Breaks option under the Page Layout tab.

In summary, whether you’re working with a small table that requires precise manual tweaks or a large dataset where automation and repeated formatting are needed, Excel offers methods ranging from built-in manual controls, VBA automation, to friendly GUI solutions like those found in Kutools for Excel, each suitable for different printing and reporting scenarios.

Demo: insert page break every x rows in Excel

Related Articles:

• Insert or remove page break in Excel