Excel Insert Page Break When Value Changes

Updated 5 November 2026 · Standard formula, shown below

Yes. Excel can insert a page break whenever a value changes, but it does not have a built-in automatic “break on value change” print setting. For a one-time report, add breaks manually; for repeatable reports, use a short VBA macro that detects changes in a sorted column and inserts horizontal page breaks.

How page breaks work in Excel

A horizontal page break tells Excel where to start printing the next page. It does not change the worksheet’s data or create a new sheet. In Page Break Preview, blue lines show the boundaries Excel will use; solid lines generally indicate manually set breaks, while dashed lines indicate automatic breaks.

Excel calculates automatic breaks from the paper size, orientation, margins, scaling, and print area. These breaks may shift when those settings change. A manually inserted break is more deliberate, but it still applies to the worksheet’s print layout rather than being a rule tied dynamically to a cell value.

For example, suppose a table has a Department column with several consecutive rows for each department. You may want each department to start on a new printed page. Excel can do that reliably with VBA, provided the rows are grouped together and the macro is run again after the data changes.

Choose a method

Manual breaks: Best for a short list or a report that rarely changes. You choose the rows and insert breaks directly.
VBA: Best when the same report is regenerated and breaks should follow changes in a particular column. The macro can clear old manual breaks and rebuild them.
Separate sheets or print jobs: Useful when groups need different headers, formatting, or print settings. It may be more work, but can be easier to maintain than many breaks.

Sorting is important for the VBA approach. If the same category appears in several separate parts of the list, the macro will add a break at every change, not gather identical categories together. Sort by the grouping column first if each category should print as one continuous section.

Insert breaks manually

To place a break before a particular row, select the row immediately below where the next printed page should begin. Then open Page Layout > Breaks > Insert Page Break. Excel inserts a horizontal break above the selected row. The same command can insert a vertical break if you select a column; selecting a cell can insert both a horizontal and vertical break.

Choose View > Page Break Preview to inspect the current page boundaries.
Find the first row of the next group and select its row number.
Choose Page Layout > Breaks > Insert Page Break.
Repeat at each group boundary, then check the printed layout with File > Print.

To remove one manually added break, select the row below it and use Page Layout > Breaks > Remove Page Break. To clear manual breaks on the sheet, use Page Layout > Breaks > Reset All Page Breaks. Resetting breaks can remove other manual page breaks too, so inspect the layout afterward.

Automate breaks with VBA

This macro checks a chosen column from top to bottom. When the value differs from the value in the row above, it adds a page break before that row. It assumes row 1 contains headers and the data begins in row 2. In the example, the grouping values are in column A; change the column reference if necessary.

Sub InsertBreaksWhenValueChanges()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim groupColumn As Long

    Set ws = ActiveSheet
    groupColumn = 1 'Column A
    lastRow = ws.Cells(ws.Rows.Count, groupColumn).End(xlUp).Row

    ws.ResetAllPageBreaks

    For r = 3 To lastRow
        If ws.Cells(r, groupColumn).Value <> ws.Cells(r - 1, groupColumn).Value Then
            ws.HPageBreaks.Add Before:=ws.Rows(r)
        End If
    Next r
End Sub

To run it, save a working copy of the workbook, open the Visual Basic Editor from Excel’s Developer tools, insert a standard module, paste the code, and run the macro while the intended worksheet is active. If the Developer tab is not visible, it can be enabled in Excel’s ribbon settings. Menu wording and the way the editor opens can vary by platform.

The loop starts at row 3 because it compares each row with the one immediately above it; row 2 is the first data row and has no preceding data row to compare. The macro clears all manual page breaks on the active sheet before adding new ones. Remove the ws.ResetAllPageBreaks line if you need to preserve existing breaks, but be aware that new breaks may then combine with old ones.

Macros require a workbook format that supports them, such as an Excel macro-enabled workbook. Excel for the web does not run VBA macros, so use desktop Excel for this method. As with any code, review what it changes and run it on a copy if the workbook’s existing print layout matters.

Check print settings and common problems

Page breaks mark boundaries, but they do not guarantee that every group fits on exactly one page. A long group can span multiple pages, and a wide table can also split across pages horizontally. Before printing, set the intended print area and check orientation, paper size, margins, and scaling under the page layout and print settings.

Breaks appear in the wrong places: Confirm the grouping column is correct and that there are no blank rows or inconsistent values splitting a group.
A group still spans pages: The group may be taller than the printable page. Reduce unnecessary row height or adjust scaling and margins; do not assume a break can make oversized content fit.
Breaks move after editing: Automatic breaks respond to layout changes. Rerun the macro after refreshing or rearranging the report.
All rows seem to print on one page: Check whether scaling is set to fit the sheet on one page. That setting can shrink a large report substantially.
The macro does not run: Check that it is running in desktop Excel, that macros are permitted for the workbook, and that the correct sheet is active.

Frequently Asked Questions

Can Excel automatically insert a page break when a cell value changes?
Not through a standard print-layout option. Use a VBA macro for an automatically rebuilt layout, or insert manual breaks at the required rows.

Does the macro work if the data is not sorted?
It still inserts a break at each adjacent change. It does not collect matching values from different parts of the list, so sort the data first if each group should be together.

Can I insert a page break when a formula’s result changes?
Yes. The comparison reads the cell’s value, so a formula result can trigger a break when it differs from the preceding row’s result. Rerun the macro after recalculating or updating the data.

Will a page break keep a whole group on one page?
No. It starts the group on a new page, but a group that is too long can continue onto another page. Excel does not automatically shrink or split rows to guarantee one page per group.

How do I remove the breaks created by the macro?
Run a reset of page breaks for the worksheet, or use Page Layout > Breaks > Reset All Page Breaks. This also removes other manual breaks on that sheet.

Can I do this in Excel for the web?
You can inspect and adjust print layout options there, but VBA macros are not run in Excel for the web. Use the desktop app to execute the macro.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.