ExcelInsider Team
When we work with large datasets in Excel we often lead to formatting challenges. Particularly when we are preparing spreadsheets for printing. One common requirement is inserting multiple page breaks to control how data appears across printed pages. If you are handling sales reports, inventory records, or timesheets, strategically placed page breaks help you present data professionally.
To insert multiple page breaks in Excel, follow these steps:
➤ Select the row or column where you want the page to break.
➤ Go to the Page Layout tab.
➤ Click Breaks → Insert Page Break. Repeat as needed.
This article will walk you through four practical methods for inserting multiple page breaks in Excel: using the ribbon, right-click method, VBA macro and subtotal function.
What Is a Page Break in Excel?
A page break in Excel is a marker that defines where one printed page ends and the next one begins. These breaks are important for customizing print layouts for long sheets. Excel automatically inserts page breaks based on paper size and settings, but manual page breaks give users full control over the print output.
Applying Manual Insertion of Page Breaks (Repeatedly)
Manual page break insertion is a direct method to control how Excel prints your data. It is useful when you want to split data across different pages at specific rows or columns. For example, to separate content by category or region. This method is good when you are working with relatively small datasets or when exact control over printed output is needed.
We have a dataset where a logistics team wants to print delivery schedules region-wise on separate pages for distribution to different delivery centers. We will insert Manual page breaks before each region starts.
Steps:
➤ Open your Excel file with the data.
➤ Go to the View tab on the Ribbon and Click Page Break Preview. This view helps you see where current page breaks are and gives a better sense of your printing layout.
➤ Look for where you want the new page to start. For example, insert a page break before the “South” region (i.e., row 4).
➤ Click the row number (e.g., row 4 if you want the break between row 3 and 4).
➤ Go to the Page Layout tab → Click Breaks → Select Insert Page Break.
➤ A horizontal page break is added above the selected row (between 3 and 4)
➤ Repeat Steps the process for rows 6, 8, and 10 to insert more page breaks before each new region.
➤ Once you’ve inserted all breaks, you can go back to the View tab and click Normal to continue working with your spreadsheet normally.
Using Context Menu to Insert Multiple Page Break in Excel
This method allows you to insert page breaks quickly by right-clicking on a row or column. It is ideal when working with categorized data—such as department-wise reports, region-based data, or weekly tasks—and you want each section to start on a new printed page.
Steps:
➤ Open your Excel worksheet and review your data layout
➤ Select the row below where the first group ends. If your North region ends at Row 4, right-click on Row 5 (the row below the last North record) and search by typing “Page Break” in the search box.
➤ From the context menu, click on “Insert Page Break”.
➤ A blue horizontal line will appear indicating a page break.
➤ To preview the layout in Page Break Preview, Go to View → Page Break Preview
➤ Repeat the process for all required sections
Application of VBA Macro to Insert Multiple Page Breaks on Text or Value Change
This method uses a VBA macro to automatically insert page breaks every time a value (e.g., Department Name) changes in a specified column. It’s best when we are managing large grouped datasets and where printing needs to be segmented cleanly by group changes, such as departments, regions, or categories.
Steps:
➤ Open your Excel file and press Alt + F11 to open the VBA editor. This opens the Microsoft Visual Basic for Applications (VBA) editor.
➤ Go to Insert → Module. A blank module window will appear.
➤ Paste the following VBA code into the module
Sub InsertPageBreaksOnChange()
Dim i As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow - 1
If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Rows(i + 1)
End If
Next i
End SubHere,
➥ The macro checks Column A (departments) from row 2 downward.
➥ When a value differs from the next row, it inserts a horizontal page break.
➤ Close the VBA editor and return to Excel.
➤ Press Alt + F8 to run the macro. Select InsertPageBreaksOnChange and click Run.
➤ Go to View → Page Break Preview to confirm page breaks. You will see blue dashed lines after each group where the value changed.
➤ Save the workbook as a macro-enabled file (.xlsm). This preserves the macro for future use.
Note:
You can customize it for other triggers, like keywords or numeric changes.
Implementing Subtotal Feature with Page Breaks Between Groups
The Subtotal function in Excel is used to automatically group and summarize data by a specific column. When applying it, you can choose to insert page breaks between groups, making it ideal for printing structured reports like sales by region or departments by division. This method is best used when your dataset is sorted by category and you want each group to appear on a separate page in the printout.
Steps:
➤ Open your dataset where you want to insert Page Breaks.
➤ If your data is not sorted, sort it by the column you want to group by. For example, if your data includes a “Region” column in Column A, sort it in ascending order so that all entries for each region are grouped together. I have my data already sorted.
➤ Select the entire data range, including headers. For instance, select cells from A1 to C12 if your data spans across those rows and columns.
➤ Go to the Data tab on the ribbon.
➤ Click on the Subtotal option in the ‘Outline’ group.
➤ In the Subtotal dialog box:
- At each change in: Select the grouping column (e.g., Region)
- Use function: Select a function like Sum
- Add subtotal to: Choose one or more columns to summarize (e.g., Sales)
➤ Check the box “Page break between groups”
➤ Click OK.
➤ Excel will now add subtotals and insert page breaks between each group (e.g., between each region).
Note:
➥ If the Subtotal option is grayed out, ensure your data is not in a formal Excel Table (convert to range first).
Frequently Asked Questions (FAQs)
How do I add multiple page breaks in Excel?
Use the Page Layout > Breaks > Insert Page Break option. Select a row or column and insert breaks repeatedly wherever needed.
How do I add another page in Excel?
Insert a manual page break or expand your data beyond the print area.
What is page break preview in Excel?
Page Break Preview is a visual view in Excel. It shows where pages will break when printed. Access it th rough View > Page Break Preview.
Concluding Words
To manage large spreadsheets more effectively for printing, inserting multiple page breaks is a must. Whatever method you use- the Insert Page Break via Right-click, Ribbon option, or a dynamic VBA macro, each method will let you have precise control over your print layout. Choose the method that best suits your dataset and workflow and if you know something else, share with us.
