Tip of the week # 643
26 February 2021

Automatically insert page breaks when the content of a certain column changes (Microsoft Excel) *

Before I fell in love with Tables and PivotTables I regularly used Excel's Outline functionality to group data and automatically insert subtotals and totals. OK, I might not use it for that purpose anymore, but if you've ever manually inserted page breaks when the content of a certain column changes, you might like to know you can do this automatically.

Here's how:

  1. Click any cell containing data you want to group and print on separate pages.
  2. On the Data tab, click on the Sort A to Z or Sort Z to A button in the Sort & Filter group.
  3. On the Data tab, click on the Subtotal button in the Outline group. (If the Subtotal button is greyed out you probably have your data formatted as a table. If so, click on the Convert to Range button in the Tools group on the Table Design tab to convert the table into a normal range of cells.)
  4. From the At each change in drop-down list select the field you want to use for the page break.
  5. Select the Page break between groups check box.
  6. Click OK.

Press CTRL + P to preview your print.

By the way, what I always liked about the Subtotal feature was that you can collapse and expand the worksheet to show various levels of your data. For example, click on the number 1 to the left of column A to only see the grand total, 2 for grand total as well as subtotals and 3 for everything.

To remove the grouping, simply click on the Subtotal button and click on Remove All. (No, it won't remove all; just the grouping. All of the data is preserved.)

Related tips

  1. Quickly format and enable filtering of your data - tip_429.php
  2. Calculating subtotals and grand totals in a list - tip_100.html

* Unless stated otherwise this tip is written for Microsoft 365 and Windows 10 users, but can also be useful in Office 2010, 2013 and 2016.