Click here to get my tips every week

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003


March 9, 2017
 
Greetings from The Excel Addict

Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)You've heard me talk in the past about the seemingly endless number of hidden time-saving capabilities the we often do not notice even though we may be using Excel every day.

Just this week I learned of two new features that were actually improvements on a couple of my recent tips. Keep reading for details.

In today's 'Excel in Minutes' tip, I'm going to show you a neat trick you can use to 'Print Grouped Data on Separate Pages'.

Did you read my 
'Excel in Seconds' newsletter on Tuesday? I showed how to 'Jump to The Current Workbook's Folder'. *** TIP UPDATE *** Shortly after I published this tip, thanks to Moshe Morgenstern, I learned an even easier way to open the current workbook's folder in Excel 2013. Just click the File tab and on the bottom right corner of the Info screen you will see an 'Open File Location' option. Click it and you will instantly be taken to the current workbook's folder in File Explorer. You can read it here.

Another hidden Excel gem uncovered
*** TIP UPDATE *** Thanks to reader Frank Tonsen I have an even better solution for my 'Open One or More Specific Workbooks Every Time Excel Starts' tip from my February 7th 'Excel in Seconds' tip. It turns out that there is an option that I was not aware of for designating an 'alternate startup folder' rather than using the XLSTART folder. I have updated my tip in the online version of that newsletter. You can click here to read it.

I hope you'll have a great weekend and keep on Excelling,

Keep on
Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)
 


If you missed my last newsletter, you can click here to view it online.
 

 
Quote of the Day

"Success is the ability to go from failure to failure
without losing your enthusiasm."

-- Winston Churchill --
 
If you have a favourite quote, send it to me and I may post it in my newsletter.

Excel in Minutes with TheExcelAddict.com

Print Grouped Data on Separate Pages

If you work with large lists of grouped data and want to print each group on a separate page or separate report, you'll have to insert a Page Break between each group. For a large list consisting of dozens or hundreds of groups, manually inserting all those page breaks would be quite time consuming.

Page Break Between Groups in Microsoft Excel 2007 2010 2013 2016 365
Fortunately there is an easy trick you can use to do this in no time.

Say for example you have a large list of inventory data broken down by category and you want to print each category as a separate report.

Rather than manually inserting all those page breaks after each category, there is an option in Excel's Subtotals feature that lets you insert them all in one step.

Even if you don't want subtotals in your reports, this is the fastest way to insert page breaks between a large number of groups in your list. I'll show you how to deal with the unwanted subtotals later.

Add the Page Breaks
1) Once you have your data sorted on the column you want your reports to print by (e.g. Category), click any cell in your data and from the Data tab click the Subtotal command;

Insert Page Break After Each Subtotal in Microsoft Excel 2007 2010 2013 2016 365
2) In the Subtotal dialog, from the 'At each change in' dropdown, select the column you want your report to print by;

3) From the 'Use Function' dropdown, select a function (e.g. Sum). If you're not printing the subtotals it doesn't matter which function you choose;

4) Select the 'Page break between groups' option and click OK. Subtotals will be added and a page break will inserted below each subtotal.

Page Break After Each Subtotal in Microsoft Excel 2007 2010 2013 2016 365

5)
If you want the Subtotals to print with your report, you can print it at this point.

Print On Separate Pages in Microsoft Excel 2007 2010 2013 2016 365
Remove the Subtotals and Keep the Page Breaks
If you don't want the subtotals to print on your report, here's how to remove them.
 
1) Select all of the cells in one of the columns that has blank cells in each subtotal row;

2) Press the F5 key on your keyboard to open the Go To dialog;

3) Click the Special... button;

4) In the Go To Special dialog, select the Blanks option and click OK. A cell in each of the subtotal rows is now selected;

Select Only Blank Cells in Microsoft Excel 2007 2010 2013 2016 365
5) Right click on one of the selected blank cells and choose Delete, Entire Row, OK. All of the subtotals will be deleted but the Page Breaks will remain.

Delete All Subtotal Rows in Microsoft Excel 2007 2010 2013 2016 365

Remove the Outline Buttons
To get rid of the Outline buttons on the left of the Excel window, click any single cell in your data and click Data, Subtotal, Remove All.

Outline Buttons in Microsoft Excel 2007 2010 2013 2016 365

Remove the Page Breaks
After you've printed your report, if you want to remove the page breaks, click Page Layout tab, Breaks, Reset All Page Breaks.

Remove All Page Breaks in Microsoft Excel 2007 2010 2013 2016 365
Print Headings at Top of Each Page  
If you don't already have your headings set to print at the top of each page, from the Page Layout tab click the Print Titles command, click in the 'Rows to repeat at top' field, then click in your worksheet and select the rows you want to print at the top of each page.

Print Rows At Top Of Each Page in Microsoft Excel 2007 2010 2013 2016 365

What about Excel tables?
Unfortunately this trick won't work on Excel tables because you cannot insert subtotals into an Excel table. To make this work you would first have to convert your Excel table to a regular range.

Cannot Add Subtotals To Excel Table in Microsoft Excel 2007 2010 2013 2016 365




If you've found this tip helpful, please share it.



Click here to get my tips every week


Thanks for supporting this newsletter and website



Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630