Can You Insert Conditional Page Breaks in Excel

Problem: My data is sorted by customer in cavalcade A. I desire to put each customer on a different folio.

Strategy: The easiest way to do this is to add together a subtotal by using the Data, Subtotals command. In the Subtotal dialog, you can cull to have a folio pause betwixt groups. For more about subtotals, see "œAdd together Subtotals to a Data set" on page 298.

Yet, permit's assume that you cannot use the automatic Subtotals feature for some reason. It helps to understand page breaks.

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

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

1. The dashed line is an automatic page break.

   You lot can add a manual page break to whatever row. You position the cell pointer in column A on the offset row for a new client then select Page Layout, Breaks, Insert Folio Pause. Excel volition draw in a dotted line above the cell arrow to betoken that at that place is a page break after row viii.

   

2. Slightly longer dashes indicate a manual page break.

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

   

   Because you've added a manual folio break afterwards row 8, Excel will automatically summate that information technology tin fit rows 9 through 54 on page two. The location for the next automatic folio break is now shown at row 55 instead of row 47.

   Automatic page breaks will motility effectually. Say that yous 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 automated folio breaks, manual page interruption will never movement.

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

   Additional Details: Selecting each new customer is wearisome. Microsoft provides a shortcut for finding the next cell in the electric current cavalcade that is different from the agile cell. Nonetheless, it is difficult to use this shortcut. You will accept to decide if it is worth the hassle. Yous kickoff with the jail cell pointer on a client. So you press Ctrl+Shift+Down Arrow to select all the cells beneath the current prison cell. You press the F5 key and so click the Special button. Finally, you select Column Differences and click OK. The cell pointer volition motion to the first row that contains a new customer. You lot tin can then utilize the Breaks, Insert Page Break control. Y'all can repeat this whole series of events past holding down the Alt key while you type EGSM. Release the Alt key and press Enter. Hold downwardly the Alt key while y'all type IB. If y'all take hundreds of folio breaks to add together, mastering this keystroke might be worth the fourth dimension.

   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. Printing F5, then click Special. In the Get To Special dialog, choose Formulas. Uncheck Numbers, Text, and Errors, leaving only Logicals selected. Click OK. Do Alt+I+B to insert a pause at the first customer. Press Enter to movement to the adjacent customer. Press F4 to repeat the last command (insert pause). Proceed pressing Enter, F4, Enter, F4 until you achieve the bottom. You tin can then delete column A.

   Additional Details: To remove a manual page interruption, you should put the cell pointer in the showtime cell under the transmission folio pause. When the jail cell pointer is in this location, the Breaks dropdown offers a Remove Page Interruption pick.

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

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

---

For more resources for Microsoft Excel

• Microsoft Excel 2022 VBA and Macros
• MrExcel 2022 - Unmasking Excel
• Power Excel With MrExcel - 2022 Edition

knutsonintentookey.blogspot.com

Source: https://www.excelarticles.com/excelbook/add_a_page_break_at_each_change_in_customer.html

Share this post