Insert Page Breaks Between Subtotal Groups- Excel Training

Insert Page Breaks Between Subtotal Groups-  Excel Training

I received a request from a subscriber who had a document containing thousands of records sorted by date. She wanted to know if there was a way to print her report with the data for each day printed on a separate page.

What she actually needed was a Page Break at each change in date column. To manually insert all those page breaks on this document would be a lot of work, so she was looking for a faster and easier way.

Insert page breaks between groups This is not something I use a lot, but when you create subtotals (Date tab, Subtotal) there is a an option to insert a ‘Page break between groups’ (i.e. after each subtotal). Even if you don’t want subtotals in your report, this is probably the easiest way to insert page breaks between each group. We’ll deal with the unwanted subtotals later.

1) Once you have your data sorted on the column you want to subtotal by, click any cell in your data and from the Data tab click the Subtotal command in the Outline group;

2) In the Subtotal dialog, from the ‘At each change in’ dropdown, select the column you want your subtotals to be based on (not the column that contains the values you’re subtotaling);

3) From the ‘Use Function’ dropdown, select a function;

4) Select the ‘Page break between groups’ option and click OK. Subtotals will be added to your data and a page break will inserted below each subtotal;

If you don’t want your report to contain subtotals…

Remove subtotals but keep page breaks1) Highlight a column that has blank cells in each subtotal row;

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

3) Click the Special… button;

4) Select the Blanks option and click OK. Only the blank cells in this column will be selected;

5) Right click one of the selected blank cells, select Delete, Entire Row, OK. The subtotals will be deleted but the Page Breaks will remain.

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *