Tame your computer - make a break


01-03-2021
Roem tip time logo

In her regular series for Cambridge Network members - now in its 15th year - software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she explains how to 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. For example, Last Name or Product.
  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 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

Three months ago I started drip-feeding a shortcut of the week on LinkedIn. Follow #CleverClogsTipTime for more shortcuts, hints and tips.

To help you put these tips into practice I have also developed a series of 60-minute webinars, giving you the opportunity to see the hints, tips and time-saving shortcuts in action.

I am also running more comprehensive Excel courses in conjunction with Cambridge Network's Learning Collaboration:

 

I meticulously test every tip I write to make sure it is correct, easy to understand and time-saving. Let me know if something isn't clear or doesn't work.

Missed an issue? To view all tips created so far – including corrections, where necessary – go to http://roem.co.uk/hints.php

Unless stated otherwise, the steps are written about Microsoft 365 using Windows 10.

 

1 March 2021

Karen Roem offers software training and support through her company Roem Ltd.  Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk

 

Karen Roem
Microsoft Office-ionado

Roem Ltd – software training and support