March 9, 2017
Hi fellow Excel Addict,
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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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.
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;
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.
5) If you want the Subtotals to print with your report, you can print it at this point.
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;
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.
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.
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.
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.
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.
|"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