By default, Excel does this by inserting page breaks at regular intervals in the worksheet while printing. This is done based on the paper size, margins, and scale options. But in case you need more control and want to decide how much content should be printed on Page 1 and where it should break and go to the next page, then you can manually insert a page break in between the pages. This way Excel would know that as soon as a page break comes, it needs to stop printing on the current page and print the remaining on the next page. In this tutorial, I will show you how to manually insert page breaks in Excel (and how to remove page breaks as well)
Manually Inserting Page Breaks in Excel (Horizontal/Vertical)
Suppose I have a data set as shown below where I have the sales numbers for different people working for different regions. In this data, I want to print all the records for the US on the first phase and then all the remaining records on the second page. If I go ahead and print the data as of now, everything would be printed on a single page because the data is not big enough to be split into multiple pages. So to do this, I’ll have to manually insert a page break after the data for the US. Below are the steps to do this: The above steps would insert the page break right above the selected row. Excel also shows you the positions of the page breaks by adding a light gray dotted line between the two pages. You may not be able to see this line if you already have the gridlines visible, but if you hide the gridlines you would be able to see this as shown below. Just like the way I have inserted a horizontal page break in Excel, you can also insert a vertical page break. Simply select the column B for which you want the page break to be added and go to the ‘Page Layout’ tab and click on the ‘Insert Page Break’ option Note that you can insert as many page breaks as you want. For example, if you want to print the data for each region on a different page, you can insert multiple horizontal page breaks by selecting the row right below where the data for that region ends and inserting a page break there.
Keyboard Shortcut to Insert Page Break
Check Page Break Insertion from the Page Break Preview
Once you have inserted the page breaks in Excel, you can quickly check what all would be printed on each page by going to the page break preview mode. Below are the steps to do this: The above steps would change the view of your worksheet and you will be able to see how much content would be printed on each page. Every page break is showed as a thick blue line, and pages are numbered which makes it easy to understand what content would go on what printed page. To go back to the regular worksheet view, click on the Normal option in the View tab.
Removing the Page Breaks
Just like you can add a page break in Excel, you can also easily remove a page break. In our example, suppose I want to remove the page break that I inserted after the data for the US region. Below other steps to do this: The above steps would remove the page break above the selected row. In case you have inserted multiple praise breaks that you want to remove, you’ll have to repeat this process one by one. Note: you cannot remove multiple page breaks in one go by selecting multiple rows or columns. if you try and do that, Excel would only consider the last selected row and remove the page break above it.
Keyboard Shortcut to Remove the Page Break
Reset All Page Breaks
If you want to remove all the page breaks that you manually inserting and reset them to the default ones, there’s an option for that too. Below are the steps to reset a page break in Excel: Be above steps would remove all the manually inserted page breaks and place the page breaks based on the page size, margin, and scale (as it does by default)
Keyboard Shortcut to Remove the Page Break
Moving the Page Break Manually
Once you have inserted the page breaks in the worksheet, you can make minor adjustments by dragging the horizontal or vertical page break lines. For this to work, you need to be in the Page Break Preview mode. Below are the steps that you can use to move the page break by dragging it:
Hide or Show Page Break Dotted Line
While big breaks are quite useful if you print your data, one thing that really bugs me is the dotted gray line that remains in the worksheet after you have applied the page breaks. Some people find it useful as it tells them where the page would break when printed, I on the other hand find it annoying. And once you have applied the page breaks, that gray line would not go away till you save your workbook and restart it again. So if you’re like me, you may want to hide the page break line forever. Below are the steps to hide the page break line in Excel:
Click the File tabClick on OptionsIn the Excel Options dialog box that opens up, click on the ‘Advanced’ option in the left paneScroll down to the ‘Display options for this worksheet’Uncheck the ‘Show page breaks’ option
You can enable this setting to remove the page break gray line on specific worksheets or the entire workbook, but you cannot apply this setting to all the Excel workbooks. If you want to do this for another workbook you open, you will have to follow the same steps again.
Add Page Break After Every Subtotal
If you add subtotal to your data set, then there is an option that allows you to automatically add page breaks whenever the data category changes. Below I have this data set where I have the regions sorted in descending order (with subtotal applied). For such a data set, it would make sense to add a subtotal that would show the target sales and the actual sales for each region, and the Subtotal would be added whenever the region changes (i.e., subtotal for US below the US regions of the total for the Middle-East below the Middle East region). And when this data is printed, it may help to print all the regions in separate worksheets (which can be done by adding page breaks whenever the region changes). Below other steps to add the subtotal and add the page break whenever the region changes: The above steps would add the subtotal to the data as shown below, and at the same time also add a page break whenever the region changes. This could be useful when you want to print data for each country in a separate sheet. You can see where the page breaks are added by clicking on the View tab and then clicking on the Page Break Preview option So, this is how you can easily insert a page break in Excel. I also covered how you can delete some of the page breaks, remove them completely. or reset it back to the default setting. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Print Excel Sheet on One Page (Fit to One Page)How to Print Multiple Sheets (or All Sheets) in Excel in One GoHow to Set the Print Area in Excel WorksheetsHow to Print the Top Row on Every Page in Excel (Repeat Row/Column Headers)How to Insert Page Numbers in Excel Worksheets
