Tame your computer - make a break

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

• Quickly format and enable filtering of your data

• Calculating subtotals and grand totals in a list

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:

• Hints and Tips: Excel - next session 25 March

• Hints and Tips: Excel Further Use - next session 5 April

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