If you're using Excel to calculate subtotals for a large data set, you may have noticed that the program refuses to put page breaks between the groups of subtotals. This can be extremely frustrating, especially if you're trying to print out the results of your calculations. Luckily, there is a workaround that will allow you to force Excel to put page breaks between the groups of subtotals.
First, you need to select the entire data set that you want to subtotal. Then, go to the Data tab on the ribbon and click Subtotal. In the At each change in: drop-down menu, select the column that contains the values that you want to use to break up the groups of subtotals. For example, if you're subtotaling sales data by region, you would select the Region column. Make sure the Use function: drop-down menu is set to Sum. Then, click OK.
Excel will now insert subtotals and grand totals into your data set. However, there will be no page breaks between the groups of subtotals. To insert page breaks, select the entire data set again (including the row with the grand total). Then, go to the Page Layout tab on the ribbon and click Breaks. In the Insert Page Break section, click Before Each Change in:. In the drop-down menu that appears, select the same column that you used to break up the groups of subtotals in the previous step. For example, if you're subtotaling sales data by region, you would select the Region column. Then, click OK.
Excel will now insert page breaks before each change in the selected column. This means that there will be a page break between each group of subtotals. You can now print out your data set without having to worry about Excel's refusal to put page breaks between groups of subtotals.