Excel: Insert a Page Break Every X Rows

To insert a page break every X rows in Excel for Microsoft 365 on Windows, use a short VBA macro: it can place a horizontal break at a regular row interval. For just one break, select the row where the next printed page should start, then choose Page Layout > Breaks > Insert Page Break.

What a repeating page break does

A page break tells Excel where to start a new printed page. A repeating break pattern is useful for reports, invoices, rosters, or logs where each printed page should contain a predictable block of rows. Excel does not provide a standard ribbon setting that says “insert a break every 25 rows,” so a long repeating pattern is usually created with VBA or by inserting breaks manually.

A row interval is not the same as a guarantee that every printed page will have exactly that many visible rows. Paper size, margins, column widths, font size, print scaling, and the print area all affect what fits on a page. The macro sets break locations in the worksheet; print settings determine how those breaks appear on paper or in a PDF.

For example, if the data starts in row 2 and the interval is 25 data rows, the first break belongs before row 27. That puts rows 2 through 26 on the first page, then starts the next block at row 27. A header in row 1 can be repeated separately using Print Titles.

Insert one page break from the ribbon

For an occasional break, insert it directly rather than running a macro:

Open the worksheet and select the row number immediately below the last row you want on the current page. To end a page after row 25, select row 26.
Choose Page Layout > Breaks > Insert Page Break.
Open View > Page Break Preview, or use File > Print, to inspect where the page will divide.

Selecting the whole row makes the intended horizontal break clear. If you select an individual cell instead, Excel may add a vertical break as well, depending on the selection. To remove a particular break, select the row below it and choose Page Layout > Breaks > Remove Page Break. Choose Reset All Page Breaks to remove manual breaks on the worksheet and let Excel recalculate automatic ones.

This method works well for a few page boundaries, but it becomes tedious when a sheet has dozens of pages or the interval changes often. Excel for Mac has similar page-break controls on the Page Layout tab, although ribbon layout can vary by release. Excel for the web does not run VBA, so use the desktop app for the macro method.

Insert a break every X rows with VBA

Use the following macro when the same interval should be applied throughout one worksheet. This example assumes row 1 contains headings, data begins in row 2, and column A is filled on every data row. It asks for the number of data rows per page, then inserts breaks before the next block starts.

Sub InsertBreakEveryXRows()
    Dim ws As Worksheet
    Dim response As Variant
    Dim rowsPerPage As Long
    Dim firstDataRow As Long
    Dim lastRow As Long
    Dim breakRow As Long

    Set ws = ActiveSheet
    response = Application.InputBox( _
        Prompt:="Number of data rows per page:", _
        Title:="Insert Repeating Page Breaks", _
        Default:=25, Type:=1)

    If VarType(response) = vbBoolean Then Exit Sub
    If response < 1 Or response <> Fix(response) Then
        MsgBox "Enter a whole number greater than zero."
        Exit Sub
    End If

    rowsPerPage = CLng(response)
    firstDataRow = 2
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < firstDataRow Then Exit Sub

    ' Clears existing manual page breaks on this worksheet.
    ws.ResetAllPageBreaks

    For breakRow = firstDataRow + rowsPerPage To lastRow Step rowsPerPage
        ws.HPageBreaks.Add Before:=ws.Rows(breakRow)
    Next breakRow
End Sub

To run it in Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code into the module, and run InsertBreakEveryXRows. On Mac, the Visual Basic Editor can be opened from the Developer tab or Tools > Macro > Visual Basic Editor; keyboard shortcuts may depend on the keyboard and Excel version. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. If you keep the macro in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm).

The macro clears existing manual breaks on the active worksheet before adding the new pattern. That includes existing horizontal and vertical manual breaks, so do not run it on a sheet whose custom breaks you need to preserve. It identifies the final data row using column A; if another column is the reliable one, replace the A in the lastRow line with that column letter. It does not change other worksheets.

Choose the right starting row and interval

The interval should count the rows that belong to each printed block, not necessarily the worksheet’s row numbers. If row 1 is a heading and rows 2–101 hold records, a setting of 25 creates breaks before rows 27, 52, and 77. The page groups are rows 2–26, 27–51, 52–76, and 77–101. The final page can contain fewer than 25 records when the total is not an exact multiple of the interval.

If there is a title row plus a subheading row, and data starts in row 3, set firstDataRow to 3 in the code. With 25 data rows per page, the first break will then be before row 28. Consider whether the title and subheading should repeat on every page; if they should, configure them as print titles rather than treating them as fresh data rows in the interval.

For a table with one header row, set the first data row to 2.
For a report with two non-data rows above the records, set it to 3.
For a sheet with no heading row, set it to 1.

In the sample, the value entered in the prompt is a whole number of data rows. A zero, negative number, decimal, or canceled prompt does not create a pattern.

Check print scaling, paper, and repeated headings

After adding breaks, inspect the output in View > Page Break Preview and File > Print. Page Break Preview shows how Excel divides the sheet into pages; the displayed boundaries can shift when the print area or page setup changes. Choose the actual paper size and orientation, then check margins and scaling before relying on a page count.

In Page Layout, the Scale to Fit controls can shrink or expand a worksheet to fit a chosen number of pages. Fitting a sheet to one page tall, for example, conflicts with the goal of preserving a fixed number of rows per printed page: Excel may scale the output to satisfy the fit setting. For row-based page divisions, avoid forcing the entire sheet onto a fixed number of pages tall. Set the height to Automatic, then review the breaks and print preview. Scaling to one page wide can be useful for a wide table, but the reduced text may become difficult to read.

Use Page Layout > Print Titles to repeat heading rows at the top of each printed page. Repeated print titles do not insert page breaks and do not count as extra data rows in the macro’s interval. For example, you can print row 1 at the top of each page while keeping 25 records on each page. Set a print area if unrelated cells elsewhere on the sheet are appearing in the output.

When manual breaks or other options are better

Manual breaks are a sensible choice when page boundaries should follow meaningful sections rather than a fixed count. A subtotal, a new department, or a change of month may be a better place to begin a page than every 25th row. In that situation, insert breaks at those specific rows and check the print preview.

If your real requirement is “one department per page,” a fixed row interval is fragile because departments may have different numbers of records. You can sort or filter the data and add breaks where each group changes, or make separate print-ready worksheets for each group. If the requirement is only to keep column headings visible while scrolling, use View > Freeze Panes instead; freezing panes affects the on-screen view, not printed pages.

For a regularly refreshed report, the macro saves time, but it is not a live rule attached to the sheet. If rows are added, deleted, or reordered after the macro runs, the existing breaks stay at their worksheet row locations until you run the macro again. Review the resulting print layout after any structural change.

Common problems and fixes

Breaks appear at the wrong rows: Check firstDataRow and confirm that the interval counts records rather than heading rows. Also verify that the macro is running on the intended active worksheet.
The last rows are missing: The sample finds the last used row in column A. If column A has blanks near the end, choose a column populated on every record or revise the last-row detection.
Breaks seem to disappear or move: Review the Scale to Fit height setting, print area, and page setup. Automatic page boundaries are recalculated as layout changes; inspect Page Break Preview again after changing settings.
The output has too many pages: A narrow paper size, large margins, wide columns, or a small scale can affect the layout. Confirm whether you need a fixed row count or simply fewer printed pages before changing scaling.
The macro removes other breaks: The sample deliberately calls ResetAllPageBreaks. Preserve a backup and modify the routine if existing manual horizontal or vertical breaks must remain.

A useful final check is to print or export a small sample to PDF and confirm both the row boundaries and legibility. A page break can land at the desired row while a wide table still scales too small, so row placement and readable output should be checked separately.

Frequently Asked Questions

Can Excel automatically insert a page break every 10 rows?
Not through a built-in repeating-interval command in the standard ribbon. Insert breaks manually for a small number of pages or use a VBA macro such as the example above for a repeated interval.

Where should I place the break to put 25 rows on each page?
If data begins in row 2, insert the first break before row 27; the first block is rows 2–26. If data starts in a different row, add 25 to that starting row to find the first row of the next block.

Can I repeat the column headings on every page?
Yes. Use Page Layout > Print Titles and specify the heading row or rows. This repeats headings in print; it does not create page breaks or change the data-row interval.

Why does Excel print a different number of rows than I set?
The manual breaks establish boundaries, but scaling, paper dimensions, margins, print area, and other page setup choices affect the final pages. Check Page Break Preview and make sure the sheet is not being forced to fit a fixed number of pages tall.

Will the macro update breaks when I add rows?
No. Run it again after adding, deleting, or rearranging records. The sample also clears existing manual breaks on the active sheet before rebuilding the pattern, so save a copy and protect any custom breaks you want to keep.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.