Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Conditional Page Breaks.

Conditional Page Breaks

by Allen Wyatt
(last updated June 26, 2017)

11

Excel is a handy tool for keeping track of all sorts of data. Many people use it at work to create ad-hoc reports for different departments or projects. As you work with your data, you may wonder how you can automatically insert page breaks when the contents of a certain column change. For instance, you might have a column that contains department names, and you may want each department to start on a new page.

This is rather easy to do with the built-in Subtotals feature of Excel. All you need to do is follow these general steps:

  1. Make sure your table contains column labels. For instance, if column A contains the department names, then cell A1 could contain a label such as "Department." Make sure all the columns have labels.
  2. Sort the data in your table, using the department column as the key.
  3. Display the Data tab of the ribbon.
  4. With any cell within the table still selected, click the Subtotal tool in the Outline group. Excel displays the Subtotal dialog box. (See Figure 1.)
  5. Figure 1. The Subtotal dialog box.

  6. Using the At Each Change In drop-down list, select Department.
  7. Using the Use Function drop-down list, select Count.
  8. Using the Add Subtotal To list, select the name of the column where you want your subtotal to appear.
  9. Make sure the Page Break Between Groups check box is selected.
  10. Click on OK. Excel adds the subtotals and the page counts, as directed.

If, for some reason, you don't want to use the Subtotals feature, you can always write a macro that will remove all the page breaks in your worksheet, then add new page breaks at the appropriate places. The following macro will do the trick:

Sub PageBreak()
    Dim CellRange As Range
    Dim TestCell As Range

    Set CellRange = Selection
    For Each TestCell In CellRange
        ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakNone
        If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
            ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual
        End If
    Next TestCell
End Sub

To use the macro, simply select the cells you want to use as your key for doing the splits, minus the top cell. For instance, if the departments are in column A, rows 2 through 37, you would select the range in A3 through A37. Run the macro, and any old page breaks are removed and new ones added.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7991) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Conditional Page Breaks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Doubling Your Money

Make your money last longer by using your head when printing labels. Here's a great example of how you can double the usage ...

Discover More

Days Left in the Year

Sometimes it is handy to know how many days are left in the current year. This tip provides a quick formula that indicates ...

Discover More

Finding the Directory Name

Need to know the directory (folder) in which a workbook was saved? You can create a formula that will return this information ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Conditionally Formatting for Multiple Date Comparisons

When you compare dates in a conditional formatting rule, you need to be careful how you put your comparisons together. Do it ...

Discover More

Detecting Errors in Conditional Formatting Formulas

If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to find ...

Discover More

Conditional Formatting Based on Date Proximity

Conditional formatting can be used to draw your attention to certain cells based on what is within those cells. This tip ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2017-10-03 13:24:08

R Poirier

I have a data base with department's name ( in column A ) and staff names ,local number and extension number and other information in other columns, this report can have up to 15000 lines

When I print it , I would like a page break to be inserted only before a new department .

I do not want to have one department per page , having 3 departments on one page is not an issue , but I would like the page break to
(as much as possible) always be inserted before a new department .
And for departments who will need more than one page , a page break should be inserted before it meets an content in column C

If anyone can help , that would be great !

Thanks in advance


2017-06-26 10:30:58

Gary Lundblad

How would I have a page break created automatically, if possible, when the number 13 appears in column K of a worksheet? That is, I want a page break after the row with a value of 13 in row k, or before the row with a 14, whichever is easier. I'd prefer not to have to run a macro each time, as I have a lot of worksheets to print. Basically, I want everything to fit on one page per worksheet, just so I don't wind up with one or two rows on a page 2, but I don't want it to squeeze everything onto one sheet if it's going to make the font too small. Is there an easy way to do this that doesn't require running a macro for each worksheet? If not, what would the VBA be to run a macro for each worksheet, or even one macro for all worksheets in a range?

Thank you!

Gary


2016-06-02 17:36:27

Mary

I need to insert page break before the word Carton No on the excel spreadsheet as below. Is there any function that I can use so I can automatically add page break before certain word?

Carton No : 00093453420068573061
Weight : 2.00

Style
ZR1601013F
ZR1601013F
ZR1601013F
ZR1601013F



Carton No : 00093453420068573207
Weight : 2.00

Style
ZR1601202F
ZR1601202F
ZR1601202F
ZR1601202F


2016-06-02 17:18:46

Mary

@Deanna : probably you could use Pivot Table first before Subtotal feature.


2015-12-23 22:58:16

Deanna

This has the potential to really help me with a project I'm working on. Instead of inserting a page break each time the contents of a column change, I need to insert a new page break before each occupied cell (or after the last blank cell).

For example, A1 is Person 1, A2 is blank, A3 is blank, A4 is Person 2, A5 is blank, A6 is Person 3, etc. with varying numbers of blanks between persons.

I would like to be able to print the data associated with each person individually.

Please let me know if you have any solutions or if you need more information. Thank you!


2015-10-19 13:56:03

Eugen Sumindan

I have done some digging and discovered that HPageBreak, HPageBreaks, VPageBreak, VPageBreaks can be used to manage the page breaks in VBA. The suggested PageBreaks property of the Range object cannot be used anymore for VBA controlled page break management. At least not for MS Office 2010. Here is the modified code that does the job:

Sub PageBreak101()
Dim CellRange As Range
Dim TestCell As Range

nCells = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count)
Set CellRange = Range(ActiveSheet.UsedRange.Cells(2), ActiveSheet.UsedRange.Cells(nCells))
For Each TestCell In CellRange
If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
ActiveSheet.HPageBreaks.Add ActiveSheet.Rows(TestCell.Row)
End If
Next TestCell
End Sub


2015-10-19 13:26:03

Eugen Sumindan

I receive the same error even at the first attempt to set the PageBreak property of the Range object. Has anything changed in MSOffice? I am using Excel 2010.


2014-12-02 09:51:00

Willy Vanhaelen

I don't see why in the macro the variable CellRange is declared, then set to Selection and finaly use it only once. These 3 lines:

Dim CellRange As Range
Set CellRange = Selection
For Each TestCell In CellRange

Can be replaced by this 1 line:

For Each TestCell In Selection

It's perhaps no big deal but it serves simplicity and readability.


2014-12-01 23:33:14

JohnB

Very VERY nice tip. I would suggest to leave the "Summary below data" box unchecked as, if checked, this creates an unnecessary line which shows that count is zero. Since we're really taking advantage of a ""side effect": of the subtotals routine, we don't need this summary line.


2013-10-03 05:34:42

Marakkar

I have found a Microsoft kb article which says Excel has limited the manual page break horizonally to 1026 ??? :(

http://support.microsoft.com/kb/284916

Regards,
Marakkar


2013-10-03 04:50:16

Marakkar

Excel 2010
Windows 2007

I have been using your VBA code in excel macro for sometime without any issue. All my use was to insert less than a 1000 of pagebreaks in a sheet.

Now I have worksheet of inventory of 8163 records that is for materials lised in 1132 boxes which need page breaks, i.e. each box will have less than 10 lines.

I have sorted the liste on box and selected the column applied the macro. Page break was inserted for 1023 changes of boxes and stopped to popup a Microsoft Visual Basic error message:
Runtime error '1004':
Unable to set the PageBreak property of Range class
with End / Debug button.

I even tried to select the remaining lines in the series still the same error appears. I am unable to debug and find the limitation in the code.

Kind help me to use this very valuble macro code.

Regards
Marakkar


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.