Add a Page Break at Each Change in Customer

October 20, 2021 - by Bill Jelen

Problem: My data is sorted by customer in column A. I want to put each customer on a different page.

Strategy: The easiest way to do this is to add a subtotal by using the Data, Subtotals command. In the Subtotal dialog, you can choose to have a page break between groups. For more about subtotals, see "Add Subtotals to a Data set".

However, let’s assume that you cannot use the automatic Subtotals feature for some reason. It helps to understand page breaks.

Excel page breaks can either be automatic or manual. If you access Print Preview and then close Print Preview, Excel will draw in the automatic page breaks.

In this particular report, it turns out that with these margins and print size, Excel would normally offer an automatic page break after row 46. After you do a Print Preview, Excel draws in a dashed line after row 46 to indicate that this is an automatic page break.

A report is sorted by Customer in column A. You would like to insert a manual page break at the first row for each customer. — Figure 125. The dashed line is an automatic page break.

You can add a manual page break to any row. You position the cell pointer in column A on the first row for a new customer and then select Page Layout, Breaks, Insert Page Break. Excel will draw in a dotted line above the cell pointer to indicate that there is a page break after row 8.

The customer name changes after row 8. A page break makes sure that row 9 starts on a new page. — Figure 126. Slightly longer dashes indicate a manual page break.

Here is a zoomed-in view of the different dashes used for each break. I am not sure the difference will even show up in the book or e-book.

The dotted line for Automatic and Manual Page Breaks is slightly different. The Automatic Page Breaks have small dashes making up the dashed line. The Manual Page Breaks have longer dashes. Unless you look at them both side by size, and at a large zoom, it is really tough to tell one from the other. — Figure 127. The dashes used for Automatic page breaks differ from Manual.

Because you’ve added a manual page break after row 8, Excel will automatically calculate that it can fit rows 9 through 54 on page 2. The location for the next automatic page break is now shown at row 55 instead of row 47.

Automatic page breaks will move around. Say that you change the margins for the page, using Page Layout, Margins. Excel will now calculate that the end of the second page is at another row.

Unlike automatic page breaks, manual page break will never move.

To add the rest of the page breaks, you move the cell pointer to the next cell in column A that has a new customer and select Page Layout, Breaks, Insert Page Break. Because you have 50 of these to insert, you might want to use the keyboard shortcut: Alt+I+B or Alt+P+B+I.

Additional Details: Selecting each new customer is tedious. Microsoft provides a shortcut for finding the next cell in the current column that is different from the active cell. However, it is difficult to use this shortcut. You will have to decide if it is worth the hassle. You start with the cell pointer on a customer. Then you press Ctrl+Shift+Down Arrow to select all the cells below the current cell. You press the F5 key and then click the Special button. Finally, you select Column Differences and click OK. The cell pointer will move to the first row that contains a new customer. You can then use the Breaks, Insert Page Break command. You can repeat this whole series of events by holding down the Alt key while you type EGSM. Release the Alt key and press Enter. Hold down the Alt key while you type IB. If you have hundreds of page breaks to add, mastering this keystroke might be worth the time.

Additional Details: These steps might be easier than the above. Insert a new column A. The formula in A3 is =IF(B3=B2,1,True). Copy this formula down to all rows. Select column A. Press F5, then click Special. In the Go To Special dialog, choose Formulas. Uncheck Numbers, Text, and Errors, leaving only Logicals selected. Click OK. Do Alt+I+B to insert a break at the first customer. Press Enter to move to the next customer. Press F4 to repeat the last command (insert break). Continue pressing Enter, F4, Enter, F4 until you reach the bottom. You can then delete column A.

Additional Details: To remove a manual page break, you should put the cell pointer in the first cell under the manual page break. When the cell pointer is in this location, the Breaks dropdown offers a Remove Page Break option.

To remove all page breaks, you select all cells by using the box to the left of column A. The Breaks dropdown will now offer the option Reset All Page Breaks.

Gotcha: To insert a row page break, you must either select the entire row or have the cell pointer in column A. If you select Insert Page Break while in cell C9, Excel will insert a horizontal page break above row 9 and also a vertical page break to the left of column C. This is rarely what you want.

This article is an excerpt from Power Excel With MrExcel

Title photo by Josh Appel on Unsplash