Automatic page breaks are dotted vertical and horizontal lines that separate the spreadsheet into portions while working with a large amount of data in Excel. A common question appears how to remove these automatic page breaks in Excel. These lines are given by Excel itself and we can also insert them on our own. In any case, for viewing purposes, we may need to remove them. In this article, we will learn how to remove automatic page breaks in Excel.
We actually can not remove the automatic page break in Excel completely, in case of manual page breaks. But we can hide them for a particular worksheet and particular work time. There are multiple methods to do so. Each method has its own procedures and characteristics. All the methods are described below with step-by-step procedures.
These dotted lines are vertical and horizontal automatic page breaks.
1. Using Excel Built-in Option to Remove the Automatic Page Break
Excel itself has an option to remove automatic page breaks in Excel. To do so, we need to follow the steps.
Steps:
- At first, we need to open the worksheet and then go to Files in the top left corner.
- Then we need to go to Options. A dialog box named Excel Options will appear.
- Next in Excel Options, we need to go to Advanced and scroll down to the Display option for the worksheet.
- Furthermore, we need to untick the Show Page Breaks option and then click on OK.
- In the end, we will see there are no automatic page breaks like before.
Read More: How to Remove the Page Break Lines in Excel
2. Delete Automatic Page Break by Applying VBA Code
We can also apply VBA code to remove automatic page breaks in Excel. Here are the steps.
Steps:
- Firstly we need to press Alt+F11 to open Microsoft Visual Basic for Application. In the window, we need to select Insert and then select Module. A tab named General will appear.
- Secondly, we need to copy this code and paste it into the General tab.
Sub remove_automatic_pagebreak()
ActiveSheet.DisplayPageBreaks = False
End Sub
Sub show_automatic_pagebreak()
ActiveSheet.DisplayPageBreaks = True
End Sub
- Thirdly we will save it as a macro-enabled file or with an XLSM extension by pressing Ctrl+S and choosing XLSM as the file type.
- After that, we will go to the Developer tab in Ribbon and select Macros. A dialog box named Macro will pop up.
- Finally, by selecting remove_automatic_pagebreak and pressing Run, we can remove the automatic page break. Here we have included the code to show the page break again for better understanding.
Running the code will give us output like the below image.
Read More: How to Insert Page Break Based on Cell Value with Excel VBA
3. Use the Drag and Drop Option to Turn Off Automatic Page Break in Excel
This method is actually used to hide automatic page breaks in Excel. It does not remove the page break like the previous two methods but in a larger view, this method is better applicable. The steps are given below.
Steps:
- First, we need to go to the View tab in Ribbon and select Page Break Preview.
- So, we will get a view of our worksheet like in the image below.
Here we can see two vertical page breaks and a horizontal page break.
- Second, we need to select the horizontal page break and drag it down across the last border at the bottom like this image below.
- Thirdly we will get an image like the one below. In our case, the vertical automatic page break line disappeared after moving the horizontal automatic page break. But for larger amounts of data, the other automatic page breaks may need to be moved separately.
Read More: [Solved]: Page Break in Excel Not Working Error
Things to Remember
- We need to be in the normal view to apply methods 1 & 2.
- Method 1 & method 2 will still show automatic page breaks in Page Break Preview mode.
- We need to return to Normal View by selecting it in the View tab from the Ribbon to see the final result.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Excel gives automatic page breaks to separate pages while printing or making sheets. For larger amounts of data, we may need to remove them. This article was all about how to remove those automatic page breaks. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. Goodbye!
