Excel Page Break Based on Cell Value: How to Set It

Excel cannot create a page break directly from a cell value with a worksheet formula or built-in conditional rule. In Microsoft 365 for Windows, use a VBA macro to add a horizontal page break above each row where a chosen column’s value changes, then run the macro again when the data changes.

What Excel can and cannot do

Excel’s normal page breaks are based on the printable page size, margins, scaling, and the amount of worksheet content—not on the meaning of a cell’s value. There is no Page Setup option such as “start a new page when column A changes,” and a formula cannot insert a print break.

You can insert a manual break at a chosen row with Page Layout > Breaks > Insert Page Break. To make the location depend on values, you need to update those manual breaks yourself or automate the process with VBA. A macro is useful for reports where each customer, department, date, or other group should begin on a new printed page.

This approach creates breaks between worksheet rows. It does not guarantee that every group will fit on exactly one page: a long group may continue onto another page, while a short group may leave blank space at the bottom of its page. The break controls where a new page starts, not how much content fits after it.

How value-based page breaks work

The usual rule is to compare each row’s group value with the one directly above it. If the values differ, Excel inserts a horizontal break before the current row. For example, if column A contains Department and the rows are sorted into groups, a change from Sales to Support marks the start of a new printed section.

That means the data should be arranged in the order you want to print. If the same department appears in separate places, the macro treats each separate run as a new group. Sort the full table by the grouping column first if each department should appear together. A break is inserted at a row boundary, so it cannot split a row in half or begin halfway through a group.

Before using automation, check whether the report’s intended structure is actually “one page per group.” If you only need a new page every fixed number of rows, a value comparison is unnecessary; ordinary manual breaks or a different print layout may be simpler.

VBA macro for breaks when a value changes

The example below assumes the group labels are in column A, the first data row is row 2, and row 1 contains headings. It inserts a break before each nonblank label in column A that differs from the label in the row above. The first data group starts at row 2 and does not receive an extra break.

Sub AddBreaksWhenCategoryChanges()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Remove existing manual breaks before rebuilding them.
    ws.ResetAllPageBreaks

    ' Row 2 is the first data row; compare from row 3 onward.
    For rowNum = 3 To lastRow
        If ws.Cells(rowNum, "A").Value <> ws.Cells(rowNum - 1, "A").Value Then
            If Len(CStr(ws.Cells(rowNum, "A").Value)) > 0 Then
                ws.HPageBreaks.Add Before:=ws.Rows(rowNum)
            End If
        End If
    Next rowNum
End Sub

For example, if A2:A6 contains North, North, North, South, South, the macro adds one break before row 5, where South begins. If the data contains ordinary text or numbers, this comparison works as shown. The code assumes the compared cells do not contain Excel error values such as #N/A; handle those separately if they are possible in the group column.

The macro uses ResetAllPageBreaks before adding breaks, so it replaces existing manual page-break choices on the active worksheet. Do not run it on a sheet where you need to preserve manually placed breaks unless you modify the code to retain them. It does not delete worksheet data or change cell values.

Install and run the macro

On Windows, save a copy of the workbook before running a macro. Macros cannot be undone with Ctrl+Z, and this one resets existing page breaks on the active sheet.

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), so the VBA code can be retained.
Open the Visual Basic Editor with Alt+F11. If the Developer tab is visible, you can also choose Developer > Visual Basic.
In the editor, choose Insert > Module, paste the code into the new module, and close or return to Excel.
Select the worksheet you want to format. Run the macro with Alt+F8, choose AddBreaksWhenCategoryChanges, and select Run.
Check the result using View > Page Break Preview, or use File > Print to inspect the print preview.

If Developer is not on the ribbon, enable it through File > Options > Customize Ribbon and select Developer. On Excel for Mac, VBA is available in the desktop app; the Visual Basic Editor is commonly opened with Option+F11, though some keyboards require Fn as well. Excel for the web does not run VBA macros, so use the desktop application to create and run this solution.

Change the macro for your worksheet

If the grouping values are in another column, replace each occurrence of the column letter A in the code with that column’s letter. For example, for customer names in column C, use C in both the last-row calculation and the two cell references in the comparison. Keep the header and first data-row assumptions consistent: if data begins on row 5, the loop should start at row 6 so it compares each row with the preceding one.

The macro finds the last used row in the chosen grouping column. That is appropriate when every data row has a group label. If some records have a blank group cell, the example does not insert a break at a blank row. A later nonblank value will be compared with the blank immediately above it and may start a new page. Decide how blanks should behave before relying on the printed output.

For groups that must remain together, check whether each group’s full height fits on a printed page. If it does not, Excel will continue the group on another page after the inserted break. You can adjust scaling, margins, paper size, or the amount of information per page, but reducing the print scale may make text too small to read.

Refresh behavior and formula-driven values

Running the macro creates a snapshot of the current group boundaries. If you edit, sort, paste, or delete data afterward, the existing breaks do not automatically follow the new values. Run the macro again after the changes and review the print preview. Because the example clears existing manual breaks first, rerunning it rebuilds its own break pattern rather than stacking new breaks on top of old ones.

If the grouping cells contain formulas, the macro compares their current calculated results when it runs. Recalculate the workbook if necessary, then run the macro. A worksheet change event does not reliably solve every formula-driven case: changing a precedent cell can change a formula result without directly editing the formula cell. For that reason, an explicit rerun is often easier to understand and control than automatic event code.

Automatically rebuilding breaks whenever a user edits the sheet is possible with VBA events, but it needs extra safeguards. For example, event code must avoid repeatedly triggering itself and should target the correct sheet. For many print reports, a clearly labeled “rebuild page breaks” macro is less surprising.

Common print problems and checks

If a break appears in an unexpected place, first verify that the grouping column is sorted and that the values really differ. Values that look identical may contain leading or trailing spaces, or one may be stored as text while another is a number. Normalize the group labels or correct the source data before comparing them.

The break is not visible in Normal view: Switch to View > Page Break Preview. Excel may also show page boundaries as dashed lines in Normal view, depending on settings.
The page still splits a group: The macro starts a page at the group boundary, but it cannot keep an oversized group on a single page. Check the group’s printed height.
Breaks seem to move or disappear: Automatic pagination can change when you alter paper size, margins, scaling, or the print area. Recheck in Print Preview after changing those settings.
Headings are missing on later pages: Set repeating rows through Page Layout > Print Titles. Repeating headings is separate from inserting page breaks.

Also check the print area under Page Layout > Print Area. A break outside the area being printed may not affect the pages you expect. If the sheet is set to fit all columns or the entire sheet onto a fixed number of pages, Excel’s scaling can alter the apparent pagination, so inspect the final preview rather than relying only on the worksheet view.

Alternatives to VBA

For a one-time report, manual breaks may be quickest: find each row where the group changes and use Page Layout > Breaks > Insert Page Break. This takes more effort when the data is long or changes often, but it avoids enabling macros and gives you direct control over every break.

Another option is to filter or copy each group to its own worksheet or print job. That can be preferable when groups need different titles, page settings, or cover sheets. It also avoids one long worksheet accumulating many page-break decisions. For recurring reports, Power Query can help organize and reshape data, but it does not itself provide a rule that inserts worksheet page breaks based on a cell value; pagination still needs to be handled in Excel or another reporting workflow.

Choose based on how often the source changes and how exact the layout needs to be. A macro suits repeated, predictable group boundaries; manual breaks suit small, stable sheets; separate sheets or print jobs suit groups with different formatting requirements.

Frequently Asked Questions

Can I insert a page break with an Excel formula?
No. Worksheet formulas calculate values; they do not change print settings or insert page breaks. Use a manual break or VBA to make break locations respond to cell values.

Can Excel start a new page whenever a value in column A changes?
Not through a built-in Page Setup rule. A VBA macro can compare consecutive cells in column A and insert a horizontal break before each changed value, as in the example above.

Will the page breaks update automatically when I change a cell?
Not with the standard macro shown here. Run it again after editing, sorting, or recalculating the data. Formula results are compared when the macro runs.

Can I make every group print on exactly one page?
A break can start each group on a new page, but it cannot make a tall group fit on one page without changing the layout or scaling. Preview the report and adjust the content or print settings if needed.

Does the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop version of Excel for Windows or Mac to run the code.

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.