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).
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.
- Click any cell containing data you want to group and print on separate pages.
- On the Data tab, click on the Sort A to Z or Sort Z to A button in the Sort & Filter group.
- 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.)
- 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.
- Select the Page break between groups check box.
- 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.)
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.
Highly respected training company, specialising in Excel, Word, PowerPoint and Outlook. Since 2001 we have successfully developed and delivered training courses for public and private clients, large and small, across 23 countries around the world. We feel passionate about sharing the best of what we know with others and strive to help as many people as we can.