Everything you must be aware of when inserting, changing the size, viewing, hiding and removing automatic and manual breakpoints in Excel.
Page Breaks are grey line or dots that divide or divide a worksheet into different pages to print. The worksheet is divided and indicates the amount of data that needs needed to print on one page, and how much is printed on the next page, and the next. Also, it highlights the edges of each page.
There are two kinds of page breaks available in Excel manual breakpages (solid lines) and automated breakpages (dashed lines). When you view the worksheet in its normal view your worksheet, these will appear as vertical and horizontal lines. You can add manually page breaks to determine the point at which data breaks and continue on to the next page. Or, Excel can automatically include page break whenever you open your worksheet in the ‘Print Preview’ or ‘Page Break Preview’.
If you aren’t planning to print the worksheet or removing the document from visual clutter, you can simply eliminate page breaks from the Excel document. In this article we will provide step-by-step directions for inserting or modify, view, conceal and erase page breaks within Excel.
Table of Contents
Insert Page Breaks in Excel
Excel automatically adds page breaks based on the dimensions of the paper and its scale when creating the Excel spreadsheet. You can recognize the automatic page breakers using their drawn lines that are dashed or dotted in the cell’s border.
Sometimes, you might need to manually add page breakers in case you are not happy with the way Excel assigns page breaks or if you require specific rows or columns of data on every printing page. There are two types of page breaks : horizontal and vertical. In a spreadsheet you can add horizontal page breaks, vertical page breaks or both. This article will show you how to add manual page breaks in Excel.
Insert Vertical Page Breaks in Excel
Imagine that you have an employee list in various columns, such as the First and Last names, Company Name, Address City, State, email, phone number and so on. If you create a worksheet to print, at a minimum the first six columns are printed onto the same sheet. However, you should only make the four first columns (First Name Last Name, First Name, the Company’s Name, and Last Name) in the initial page, and the remaining columns on the following page. This is done by adding a vertical gap between columns 4 and 5.
Then, choose the column that is to the right of the spot you wish to place your page break. We want to create an extra pagination break in between column D as well as E so we chose the column called E. To select the entire column, click on to select the letter of E in column.
Then, click the tab ‘Page Layout’ and select the drop-down arrow ‘Breaks’ within the page setup section. Select the option to insert a Page Break’ option. Alternately, you can insert page breaks using keys Alt + P + B +.
After this, Excel will divide the page and add a page break line just to the right of the cell you have selected. The line of break will appear slightly darker than gridlines for cells as illustrated below.
Page breaks only marginally darker than gridlines. If they’re not apparent, you can take out the gridlines in cells to view them clearly. To eliminate the gridlines you must click on the tab ‘View’ and remove the option ‘Gridlines’ in the Show section.
This will eliminate all of the gridlines of the cell, leaving the page break behind.
Insert Horizontal Page Breaks in Excel
Let’s say that we have employees’ information in a spreadsheet, however we want to print only the details of 11 employees on one page. In order to do this, we must create an extra pagination break in between row 12 and 13 (because the upper row has headers). It is possible to use the same steps to create pages breaks in between rows.
Then, choose the row which is below the point where you would like for the break in your page to be. Contrary to the column in which it appears on the left of column you have selected the page break is displayed above the row you have selected. Be sure to select the entire row by clicking the row’s number. We will need to insert the break in row 12 between 13 and 12, therefore we chose row 13.
Go to the tab ‘Page Layout’ and select the “Breaks” drop-down arrow within the page setup section. Select the option to insert a Page Break’ option. Alternately, you can insert page breaks using keys Alt + P + B +.
When you select the “Insert Page Break” option and you’ll see page breaks appearing in a more dark gray line, as illustrated below.
If the break in the page isn’t visible clearly You can take off the gridlines so that you can see them.
Insert Both Horizontal and Vertical Page Breaks Simultaneously
In addition to horizontal and vertical break in the page, you could also include a crisscross break at a particular location within your spreadsheet. In order to insert vertical and horizontal page breaks at the same time it is necessary to choose one cells instead of the complete column or row.
The first step is to select the cell just below the area you would like to insert to insert the horizontal page break, and just to the right of where you’d like to place the vertical page break. We chose cell E14 in the following example. The vertical and horizontal page breaks will meet in the upper left-hand corner in the cell we have selected.
Follow the same steps to add page breaks. Go to the tab ‘Page Layout and click on the drop-down menu ‘Breaks and then select the “Insert Page Break’ option.
This adds both vertical and horizontal page break, that is a cross-line to the left and over the choice as illustrated below.
See/View Page Breaks in Excel
The Page Break is a gray line which is slightly darker than gridlines. Sometimes, it’s difficult to spot page breaks, particularly in spreadsheets that are stuffed with information. To adjust, move or eliminate breaks in a page you must be able to view them. To see or alter the breaks on your page, you have to turn on the ‘Page Break Preview mode. This Page Break Preview can be helpful in the event that you need to know the impact of any modifications that you’ve made (formatting and page layout) impact the page breaks either manually or automatically.
Open the spreadsheet , and click the View menu on the ribbon. After that, select the “Page Break Preview” option within the Workbook Views group.
You will now be able to see all pages and page breaks in the worksheet. You can also see the view of the pages within the Print Preview window However, it will only display the information of one page.
Alternately, you could make use of the status bar in order to turn on Page Break Preview in Excel. Select the rightmost icon adjacent to the Zoom controls to see the Page Break Preview. It is also possible to use an alternative keyboard shortcut, Alt+Win.
As you can see below, pages will be divided in blue lines, and the divided pages are identified by numbers.
The Zoom level on the webpage will automatically decrease to 60% in order to display as many pages that break as is possible. You can also alter the zoom to the desired degree.
Automatic page break (if they exist) will appear as dashed lines, while the manually-generated page break will be shown in solid lines.
To return to normal view to return to the normal view, click on the ‘Normal View’ button on the ribbon.
Move Page Breaks in Excel
After you have placed the page breaks within your worksheet you are able to manually move them into the preferred location. You can change the position of vertical or horizontal page breaks by moving them to an alternate place. Additionally, the page break can’t be moved into the middle of cells. It is only possible to move it between gridlines and gridlines (cell border).
Move a Manual Page Break
To alter a page break, first you must be in the Page Break view mode. Click on the View Tab on the Ribbon and then click on the “Page Break Preview” button or click the “Page Break Preview” icon in the Status Bar.
After that, you can hover your mouse over the page break you wish to move. After this, your cursor will transform into an arrow with two edges that indicates the direction that the page break is able to be moved.
You can now move the blue page break into the desired spot.
The break in the page is moved from between columns E and D to between columns C and D.
Move an Automatic Page Break
Like manually-generated page break, automated page breaks are also able to be relocated to a different location. Once moved, an automatic break is now an manual page break. It is easy to spot automatic page breaks by their drawn lines.
The first step is to look at the worksheet using “page break preview”.
After that, you can hover over the automatic breaks in the page (dashed lines) you wish to move, and drag the double-headed arrow the spot you’d like it to be placed.
When the automatic page break is moved, it will transform into manual page break i.e., the blue dashed line will transform into an actual blue line.
Hide or Show Page Breaks Marks
After the page breaks have been added, they will not be removed until after you save the document and then reopen the document. If you don’t want the page breaks to show up in the spreadsheet, you are able to remove them at any time. Here’s how to conceal or hide break marks on pages in Excel:
Start the File menu, then click on “Options” in Excel’s Excel Backstage View.
Within the Excel Options window, select Advanced in the pane to your left, and then scroll down until you reach the “Display Options available for this worksheet section to the right. After that, uncheck the “Show page break” checkbox. Then you click OK, you can apply the modifications.
NOTE: Make sure, you’re in the normal view’ and not in the ‘Page Break Preview’. If not, the option above will not be available.
These steps only conceal page breaks for the currently working book. If you wish to conceal page breaks in other workbooks, you must follow the same procedure for each workbook.
Remove Page Breaks in Excel
If you aren’t happy with the page breaks in your Excel workbook, you can easily eliminate the breaks. While removing or eliminating manually-created page breaks in Excel is simple however, eliminating automatic page breaks can be difficult. It is possible to remove one page break at a single time (horizontal and vertical) or horizontal and vertical page breaks simultaneously (crisscross breaks in the page) or remove all page breaks at the same time.
Remove the Manually Inserted Horizontal Page Breaks
If you’ve manually added vertical page break, you can quickly eliminate or remove them using these steps:
The first step is to open the spreadsheet where you wish to eliminate vertical page break. Next, go to the tab ‘View’ and choose the ‘Page Break Preview’ option. This will make each page break into a visible.
Then, choose the row that is below the page break that you wish to eliminate.
After choosing the row, click the tab ‘Page Layout and then click on the ‘Breaks’ tab and choose “Remove Breaks from Page’. Break’.
Alternately, you can choose the row you want to edit and then click on the tab ‘Page Layout and click on the button ‘Breaks’ and then select ‘Remove Page Break’.
Remove Manually Inserted Vertical Page Breaks
If you wish to eliminate horizontal page break, you have select the column just to the right of the page break.
Then, open the spreadsheet using the preview of page breaks. To delete a vertical page break, click the column that is to left of the break that you wish to remove.
Then, select the menu ‘Breaks’ and choose “Remove Page Break Under the tab ‘Page Layout.
You will now be able to see that the vertical break has been removed.
Remove Vertical and Horizontal Page Breaks at Once (Crisscross Page Break)
We’ve discovered how to insert horizontal and vertical page breaks simultaneously. You can also eliminate the 2 page break (vertical as well as horizontal) simultaneously. To eliminate the two page breaks simultaneously you must select the cell in which horizontal and vertical page breaks meet. Let’s look at how to eliminate both horizontal and vertical page breaks simultaneously:
For instance, we would like to eliminate the page breaks in row 17 and column F as well as row 17. To eliminate this crisscross break, first start the spreadsheet. It is also possible to switch into the Page Break preview mode so that you can quickly identify the lines of breakage.
Then, choose the cell that is to the right and below the point where horizontal and vertical page break gray lines intersect. In this case the cell is F17.
Then, go to the tab ‘Page Layout’ within the Excel ribbon, then click the menu ‘Breaks’ in the Page Setup group , and then click ‘Remove Page Break’.
It is evident that both horizontal and vertical breaks on pages have been eliminated in one go.
Reset all Manual Page Breaks
Excel offers only the option to eliminate manual page breaks but not automatically-generated page breaks. If you aren’t able to eliminate each page break one at a time You could reset the entire page break, which will eliminate all manual page breaks from the worksheet currently in use. But, if the worksheet is equipped with automated page breaks, they’ll remain on the worksheet.
Open the spreadsheet in which you wish to reset all page breaks , and change to preview of page breaks mode.
Then, click on the tab ‘Page Layout’ within the Page Setup group and click Breaks. Select “Reset All Page Breaks” choice from the menu dropdown.
This will remove any page breaks on the worksheet.
How to Remove Automatic Page Breaks in Excel
Excel will automatically add page break lines (dashed lines) during printing, or switching to the Page Break Preview mode, based on the size of the page, its margin size, scale, and the position of any other manually-created page break lines that you have inserted. Page Breaks that are automatic Page Breaks are not able to be removed by using the “Remove Page Breaks’ or Reset all Page Breaks’ option. There are two methods to eliminate automatically-generated page breaks in Excel by employing VBA code or Drag and Drop method.
Use VBA Code to Remove any Page Breaks in Excel
It is possible to apply a basic VBA script to eliminate any page breaks within Excel. Apply these instructions to this VBA code:
The first step is to open the workbook in which you wish to eliminate automatically-generated page break, manually page breaks, or both. Go to the tab ‘Developer’ and select the ‘Visual Basic option on the menu or press Alt+F11 to start Microsoft Visual Basic for Applications.
It will start Microsoft Visual Basic for Applications in an additional window. Within the VBA window, select the Insert menu and choose the Module . Alternatively, you can right-click on Microsoft Excel Objects on the menu bar to the left side, select “Insert”, and select ‘Module’ in the menu that is below.
Copy and paste this VBA code into your new module:
Sub remove_automatic_pagebreak() ActiveSheet.DisplayPageBreaks = False End Sub Sub show_automatic_pagebreak() ActiveSheet.DisplayPageBreaks = True End Sub The code above allows you to eliminate breaks in pages or display page breaks once more.
After you’ve pasted the script, select “File” and then select the option ‘Save XXXXX (filename)’ to save the module as a macro.
The VB script must be saved as the macro-enabled type of file. When you click Save and you will be presented with an option box that asks you if you would like to save the file as a macro-free or a macro-enabled type of file.
Select ‘No’ to select the type of file that is macro-enabled.
When you are in the Save As window, select Excel Macro-Enabled Workbook (*.xlsm) Format from the drop-down menu titled ‘Save As option’, and then select ‘Save’.
You can now use the macro to erase or display page breaks.
Return to the Excel worksheet, then go to the tab ‘Developer’ under ‘Ribbon’, and then select the ‘Macros’ option or press the keys ALT+F8.
A dialog box titled Macro will pop up. Below the Macro name, you’ll find two names for macros. Now, select ‘remove_automatic_pagebreak’ macro and click ‘Run’.
To show all the page breaks again, open the macro window again, select the ‘show_automatic_pagebreak’ macro and click ‘Run’.
Use Drag and Drop Option to Remove Manual Page Break
Another method to eliminate automated page break is to employ Drag and Drop to conceal or eliminate automatically-generated page break. You can drag your automatic break into a different place to make it manually-created page breaks, and then follow the method above to remove this page break.
Then, click on the tab ‘View’ in Ribbon and then select “Page Break Preview” to open the spreadsheet in page break preview mode.
When we’re inside the Page Break Preview, click the page break line that automatically appears you wish to eliminate and drag it over the border that is last The line of the page break will be gone.
If you take out the horizontal or vertical page break, a second horizontal or vertical line that is perpendicular to the line that was deleted will be eliminated as well. Repeat the process to eliminate as many page breaks that are automatically generated as you like.
Alternately, you could move your automatic break into a different place, and it will transform the automatic page break into an manual page break. After that, you can select the “Remove Page Break’ option under “Breaks” in the Page Layout tab to delete it.
That’s it. Nowyou’re aware of all you need to learn regarding page break in Excel including adding or adjusting them, to hiding them, or even removing the breaks.