How to Insert or Remove Page Breaks in Excel
Excel has the functionality to print its data directly from the Excel sheet itself. But here comes the problem, if you have data of 300 rows, then you cannot print the entire data on a single sheet of paper, data needs to be divided among different sheets equivalently. Excel can do this automatically. Excel distributes table data into different sheets. But what if we need to distribute our data in a custom user-defined format? Here comes the role of Page Breaks in Excel. Excel provides functionality to design your page breaks, and when or where to start or end your printing. Let’s learn how to Insert, Move, or Delete Page Breaks in Excel.
Page Breaks in Excel
Page Breaks in Excel determine where a new page begins when printing a worksheet. They ensure that your data is neatly organized and fits within the confines of a printed page.
Representation of Automatic and Manual Page Breaks
By default, Excel provides some automatic page breaks. The default page breaks are represented by dotted lines. The Manual page breaks are shown by the solid lines.
Representations of Page Breaks
Normal View, Page Layout View, and Page Break View
Normal View
It’s the full-screen view and the most used view. You always edit your data in this view. We can use Page Breaks, in the Normal view also, but it’s never recommended. Using Page breaks, in the normal view won’t give you the desired level of results, as visualizing the page breaks in this view is very difficult.
Normal View
Page Layout View
The Page Layout View separates your pages in the way you want to print them. This tool is not used much and is not recommended use to apply page breaks. It divides the pages into a printable format.
Page Layout View
Page Break View
The page break view is itself made to apply your custom page breaks and to visualize them effectively. It’s one of the most used views to apply page breaks in your sheet before printing it.
Page Break View
Easy Access to All Views
There could be several ways to open all kinds of views. But, here, we will discuss two of the easiest methods to open any kind of View
Method 1: By using View Tab
Go to View Tab; under the Workbook Views section, you will all types of views available in Excel i.e. Normal View, Page Layout View, and Page Break View.
View Tab -> Page Break View
Method 2: In the Status Bar
In the right-most corner of the Status bar, there are three buttons, scheduled in the order Normal View, Page Layout View, and Page Break View. This is one of the easiest methods to access the different types of Views.
In the Status bar
Insert and Remove Page Breaks in Excel
Given Employee and Project table with their visualization chart. With the help of Page breaks, we have to divide the sheet into four equal halves. By default, a page break is there in between Employee and Project tables.
Automated generated Page Break
How to Insert a Page Break in Excel Manually
If you go to the Print pane and don’t like the way your Excel data is laid out for printing across several pages. You can manually insert page breaks where you need them. Below you’ll find the steps showing how to do it.
Step 1: Page Break View
Go to Page Break View, present in the right-most corner of your status bar.
Page Break View
Step 2: Select the Area
As you want to divide your worksheet into four parts. So, keep your active cell at G11. The page breaks are always created from the top left corner of your cell.
Select the Area
Step 3: Page Layout Tab
Go to the Page Layout tab. Under the Page Setup section, click on Breaks. A drop-down appears. Select Insert Page Break.
Page Layout Tab
Step 4: Page Break Appeared
Two-page breaks appear i.e. horizontal and vertical page breaks. This divides your sheet into four halves. You can observe that the default dotted line has disappeared automatically.
Page Break Appeared
Step 5: Select the Column
Let’s roll back to the original data set. What if you want only a vertical page break to be inserted in your sheet? Select the entire column G in your worksheet.
Select the Column
Step 6: Insert Page Break
Go to the Page Layout tab. Under the Page Setup section, click on Breaks. A drop-down appears. Select Insert Page Break.
Insert Page Break
Step 7: Page Divided
Your page gets, divided vertically into 2 sections i.e. Page 1 and Page 2.
Page break applied
Step 8: Select the Row
Let’s again roll back to our original data set. What if you want only a horizontal page break to be inserted in your sheet? Select the entire row 11 in your worksheet.
Select the Entire row
Step 9: Insert Page Break
Go to the Page Layout tab. Under the Page Setup section, click on Breaks. A drop-down appears. Select Insert Page Break.
Insert Page Break
Step 10: Page Divided
Your page gets, divided horizontally into 2 sections i.e. Page 1 and Page 2.
Page Break applied
Insert a Page Break in Excel by Condition
If you often print your data tables, you may want to learn how to automatically insert page breaks in Excel by condition.
Use Macros to Add the Marking Lines
Enhancing your experience often involves harnessing the power of macros to streamline intricate tasks. If you’re seeking to remove default page breaks and seamlessly introduce marking lines at strategic intervals within your table, the following macros are your solution. By incorporating these macros into your workflow, you’ll effortlessly achieve a refined and organized presentation of your data.
InsertPageBreaksIfValueChnaged Macro: This macro offers dynamic page break management by intelligently analyzing column values and inserting page breaks whenever a value changes. A visually appealing and logically segmented printout that showcases your data’s transitions seamlessly.
InsertPageBreaksByKeyphrase Macro: For those instances where a specific keyword serves as the ideal page break indicator, this macro shines. With a simple premise- insert a page break each time the designated keyword is encountered- your data table transforms into a neatly compartmentalized document, enhancing both readability and interpretation.
Page break using macro
Swift Implementation for Immediate Results
Implementing these macros is a straightforward process that catapults your data presentation prowess to new heights:
Select your data Range: Begin by selecting the range of cells where you intend to introduce page breaks or marking lines. Ensure you exclude any headers from your selection.
Access the Macros: Navigate to the “Developer” tab in Excel’s ribbon. If you don’t see this tab, you may need to enable it by going to Exccel options >Customize Ribbon >Developer.
Click on “Macros” to access the macro management window.
Insert the Macros: Within the macro window, you’ll find an option to create a new macro. Paste the code for the desired macro either InsertPageBreaksIfValueChanged or InsertPageBreaksByKeyphrase- into the editor.
Customize the Keyphrase(If Applicable): If you’re using the InsertPageBreaksByphrase macro, remember to replace “Cell Value” with your actual key phrase within the macro code.
Execute the Macro: Click “Run” to execute the Macro. Marvel as Excel efficiently removes default page breaks and inserts the marking lines precisely where you intended.
How to Remove Page Break in Excel
Step 1: Select the Cell
Considering you have created a page break as shown above. Now, you need to remove the page break as per your wish. Go back to cell, G11.
Select the cell
Step 2: Go to Page Layout Tab
Go to the Page Layout tab. Under the Page Setup section, click on Breaks. A drop-down appears. Select Remove Page Break.
Page Layout Tab
Step 3: The Page Break gets Removed
Page Break Removed
How to Move a Page Break in Excel
Step 1: Select the Page Break
Consider the Page break inserted in the sheet. Now, if you want to move your vertical page break to one position right, then you can use Move Page break. Put your cursor on the vertical page break line, until a double-sided arrow does not appear.
Select the Page Break
Step 2: Hold and Drag
Now, left-click your mouse, hold, and drag it to any position you want to have your vertical page break. For example, dragging my mouse to one position right.
Hold and drag the Mouse Left Click
Step 3: Leave your left click
Leave your left click, and your vertical page break has moved towards one step right.
Page Break Moved
FAQs on How to Insert and Remove Page Breaks in Excel
What are page breaks in Excel?
Page Breaks in Excel determine where a new page begins when printing a worksheet. They ensure that your data is neatly organized and fits within the confines of a printed page.
How to insert a manual page break in Excel?
Follow the below steps to insert a manual page break:
Step 1: Select the row or column where you want to insert the page break.
Step 2: Go to the “Page Layout” tab on the ribbon.
Step 3: Click on the “Breaks” dropdown in the “Page Setup” group.
Step 4: Choose “Insert Page Break” A dashed line will appear to indicate the page break.
How can I move a page break in Excel?
To move a page to a new location, follow these steps:
Step 1: Click on a Cell in the row or column next to the page break you want to move.
Step 2: Go to the “Page Layout” tab on the ribbon.
Step 3: Click on the “Breaks” dropdown in the “Page Setup” group.
Step 4: Select “Remove Page Breaks’.
Can I customize the appearance of page breaks?
Excel’s default page break lines are automatically inserted and cannot be customized directly. However, you can adjust row heights, column widths, and page margins to influence page break placement and appearance.
What should be done if the worksheet contains multiple page breaks?
If your worksheet contains multiple page breaks, you can manage and fine-tune their placements using the “Page Break Preview” mode. You can also adjust the layout and formatting of your data to ensure optimal page breaks.
