How to Insert or Remove Page Breaks in Excel

Excel allows direct printing of data from a spreadsheet. However, printing large datasets, such as 300 rows, on a single sheet is often impractical, requiring the data to be split across multiple sheets. Excel can automatically distribute table data across sheets. But what if you want to control the data layout in a custom format? This is where page breaks in Excel come in. Excel offers tools to customize page breaks, enabling you to define where printing starts or ends. Let’s explore 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.

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. 

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 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. 

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. 

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. 

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. 

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. 

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. 

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. 

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. 

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. 

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. 

Step 7: Page Divided

Your page gets, divided vertically into 2 sections i.e. Page 1 and Page 2. 

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. 

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. 

Step 10: Page Divided

Your page gets, divided horizontally into 2 sections i.e. Page 1 and Page 2. 

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.

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. 

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.

Step 3: The Page Break gets 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. 

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. 

Step 3: Leave your left click

Leave your left click, and your vertical page break has moved towards one step right. 

gautamgoel962

Follow

Improve

Article Tags :

• Excel
• Excel - Advanced
• ExcelGuide