in Software

Page Break on Value Change in Microsoft Excel

Ran into a tough time with having Excel performing automatic page breaks based on value changes in a cell. For example, it makes sense one would want to have a separate page for each grouping of data based on the same value in a column for a row. Looked around the internet for a solution on this and did not want to get into having to build macros or go crazy for something I knew was built-in to the program.

The simple way to accomplish this is to sort by column of your worksheet, then use subtotal and check the

Page Break Between Groups

checkbox. Seems easy enough and should work. Initially, it didn’t. The reason why it did not work was related to the fact that the page setup for the printer in Excel was set to ‘Fit to’ scaling. This is very common to do with Excel so you can get the width of the print area selected in the worksheet to fit on a printed page. Well, when you do that in conjunction with the subtotal page break, the scaling wins. You don’t get your automatic page breaks on value changes (subtotal counts) in Excel.

So, just make sure that scaling is off and you just have it set to 100% like this.

If you do the subtotal count (and you can even move the totals to a column outside the print area to get it out of printing with the rest of your data) and have your printing page setup set to 100% and off any type of ‘Fit to’ scaling, you should be able to get the automatic page breaking on Mac or Windows versions of Excel. I was working with Excel 2008 on the Mac and Excel 2007 on Windows in testing.