8 Ways To Insert Page Break in Microsoft Excel

Are you wondering how to insert page breaks in Excel? Read on as I show you various methods to add page breaks in Excel.

Besides data analytics and visualization, Microsoft Excel also offers granular printing optimization options. One such feature is the page break. Often you might need to print Excel worksheet tables or charts for physical content circulation among employees, vendors, or customers.

Here, if you simply print the Excel worksheets, the output dataset may not be readable and understandable enough. However, if you appropriately add page breaks, your Excel reports will become more readable.

It’s okay not to know that Excel offers various important methods to add page breaks or workarounds. This article will show you all the methods you must learn to show off your Excel skills at work or school.

πŸ“’Also Read: how to add page numbers in Excel.

Reasons to Insert a Page Break in Excel

Utilizing page breaks in Excel is essential for maintaining a well-organized and clutter-free appearance in your reports. Consider a scenario where your worksheet contains 10 tables, and you opt for the default print setting to generate the report.

Inevitably, when you proceed with printing, you may encounter a common issueβ€”some tables being awkwardly split between two pages.

However, by strategically incorporating page breaks and taking advantage of features like Page Break Preview or Print Preview, you can ensure a seamless final output. This proactive approach prevents tables from being cut off unexpectedly, resulting in a more polished and professional presentation of your data.

Other major reasons to use page breaks are as follows:

  • Page breaks contribute to a more organized and structured document. By using it, you make the printed report easier for readers to follow and comprehend.
  • When creating reports or summaries in Excel, inserting page breaks allows you to segment data logically.
  • For presentations or sharing information externally, utilizing page breaks in Excel adds a professional touch.
  • When working with extensive spreadsheets, inserting page breaks aids in focused data analysis.
  • If you’re dealing with a form or structured data entry, you can use page breaks to delineate sections.
  • Page breaks enhance the navigability of your Excel workbook.

Now, discover the easy ways to add page breaks in Excel:

Page Break in Excel From Page Layout Tab

Page Layout tab’s Page Setup commands block contains the Breaks drop-down menu. You can use Breaks to add horizontal, vertical, and cross-section page breaks.

Page breaks from Page Layout Tab
Page breaks from Page Layout Tab

To add a page break to your worksheet, go to the Page Layout tab. Now, highlight a cell on which you want to add a page break.

Click on the Breaks menu and select Insert Page Break. Excel will add a cross-section page break above the cell you highlighted.

If you want to add a horizontal page break, click on a row number on the left border of the Excel app. Click on Breaks > Insert Page Break to add a horizontal page break above the selected row.

For a vertical page break, repeat the above steps after highlighting a column header letter instead of a row number.

πŸ“’Also Read: how to change page orientation in Excel.

Page Break in Excel Using the Right-Click Menu

The Page Break Preview ribbon command enables you to set the default page breaks based on the page setup of the Excel worksheet. Here, you can manually add new page breaks using the right-click menu. The default page breaks will be re-adjusted according to the manual page breaks.

Default page breaks
Default page breaks

Open your worksheet and go to the View tab. Click on the Page Break Preview button inside the Workbook Views commands block. Excel will show the default page breaks.

Right click manual page break
Right click manual page break

To add a manual page break horizontally, right-click on a row number. When the contextual menu appears, click the Insert Page Break option. A solid blue line will show up. The solid blue lines are manual page breaks and dotted blue lines are default page breaks.

Vertical page break
Vertical page break

For a vertical page break, repeat the above steps by selecting a column header letter.

Manual cross section page break
Manual cross-section page break

If you want to set up a cross-section page break, highlight a cell, right-click, and choose Insert Page Break on the context menu.

Also read: How to Use Headers and Footers in Microsoft Excel

Page Break in Excel Using Subtotal

Example of grouped data
Example of grouped data

If you’ve got an Excel dataset containing group entries as shown in the above image, you can use the Subtotal command in Excel to add a page break.

Outline subtotal in Excel
Outline subtotal in Excel

Simply, highlight the dataset and go to the Data tab > Outline commands block.

Page break between groups
Page break between groups

There, click on the Subtotal command button to show the Subtotal dialog box.

Grouped dataset
Grouped dataset

On the dialog, checkmark the checkbox for the Page break between groups option. Click OK to apply the changes you’ve made.

Now, you can follow the page break insertion steps mentioned previously to add page breaks.

Page Break in Excel Using Print Selection

Highlight print selection
Highlight print selection

Open your Excel worksheet report you want to print with strategically placed page breaks. Highlight the dataset or cell ranges you want to print on a paper sheet. Now, press Ctrl + P to open the Print menu.

Printing selection
Printing selection

Under the Settings section, click on the Print Active Sheets drop-down menu and choose Print Selection.

You should now see a print preview on the right side. If the preview looks okay, you may click the Print button on the Print menu. This is a workaround to page breaks in Excel.

Page Break in Excel Using Scale to Fit

Another workaround to page breaks is Scale to Fit.

Scale to fit menu
Scale to fit menu

You need to go to Page Layout > Scale to Fit commands block.

Click the Width drop-down menu and choose 1 page. Now, do the same thing for the Height option.

This method is beneficial for you when your worksheet contains a lot of columns and you want to minimize the worksheet size to best fit the printed paper sheet.

Page Break in Excel Using Print Area

Suppose, your Excel worksheet report is quite large and you’re unable to set an appropriate page break using the conventional methods.

In this situation, you can use the Print Area feature of Excel. Let’s explore below how it works:

Set print area
Set print area

Simply, highlight the Excel report content that you want to print on a sheet of paper. You can use a mouse or keyboard navigation to do this.

Then, go to the Page Layout tab and look for the Print Area command button inside the Page Setup commands block.

Click on the Print Area drop-down menu and choose Set Print Area. You should see a set of cross-section page break lines showing up around the content you have highlighted.

Print preview of selected range
Print preview of the selected range

If you go to the print preview screen, you’ll find only the selected cell range inside the page to be printed.

Page Break in Excel Using the Page Setup Dialog

Open your worksheet and press Alt + P, SP to bring up the Page Setup dialog box.

Print area field
Print area field

Go to the Sheet tab of the Page Setup dialog. There, you should see the Print area field. Click on the up arrow in the field to activate the range selection on your worksheet. Select the cell range you want in the printed document.

Page setup print area
Page setup print area

On the Page Setup – Print area: dialog click on the down arrow to add the range selection to the Print area field on the Page Setup dialog.

Added print area to page setup
Added print area to page setup

Click OK on the Page Setup dialog to confirm the Print area for printing.

Page Break in Excel Using Excel VBA

If you find the above methods too manual to add page breaks on Excel, you can use Excel VBA macro to automate the whole process.

Horizontal page break
Horizontal page break

Find below an Excel VBA script that shows input boxes for the rows and columns to add page breaks. To add a horizontal page break, enter a row number in the designated dialog box and Excel will add a page break before the selected row.

vertical page break input
Vertical page break input

Similarly, to add a vertical page break, enter the column number in the designated input box. Excel will instantly add a page break before the column number you’ve entered. The column number must be entered as 1 for column A, 4 for column D, etc.

Excel VBA macro for page break
Excel VBA macro for page break
Sub AddPageBreaks()

    Dim rowNumber As Long
    Dim columnNumber As Long

    ' Get row number for horizontal page break
    rowNumber = InputBox("Enter the row number for the horizontal page break:", "Horizontal Page Break")

    ' Get column header number for vertical page break
    columnNumber = InputBox("Enter the column header number for the vertical page break (e.g., for column C, enter 3):", "Vertical Page Break")

    ' Apply page breaks
    With ActiveSheet
      .Rows(rowNumber).PageBreak = xlPageBreakManual
      .Columns(columnNumber).PageBreak = xlPageBreakManual
    End With

End Sub

Don’t know how to create a macro using a VBA script? No worries! Read the following article to learn the process in easy language:

πŸ“’Also Read: How To Use The VBA Code You Find Online.

How to Remove Page Breaks in Excel

Removing manual page breaks is quite easy.

Open your Excel worksheet that contains manual page breaks.

Reset page breaks button
Reset page breaks button

Now, go to the Page Layout tab. Click on the Breaks drop-down and choose the Reset All Page Breaks option from the context menu.

Remove page breaks using Breaks button
Remove page breaks using the Breaks button

The above actions shall remove all the manual page breaks instantly.

Conclusions

So far, you’ve learned eight different ways to insert a page break in Excel. Moreover, you’ve also learned how to remove the manual page breaks when needed.

Try the above methods on your Excel worksheet and comment below how was your experience in following the instructions. If you know a better method to add a page break in Excel, do mention that in your comment.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos πŸ˜ƒ

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos πŸ˜ƒ