Excel has been a top spreadsheet tool for managing data, performing calculations, and creating reports for the past few decades. Anyone, from beginners to advanced users, can use it to organize and print datasets.
However, as the spreadsheet size grows, it becomes challenging to organize and print it.
In that case, one essential feature that can help you manage large spreadsheets is the ability to control page breaks. Page breaks determine where your new page starts while you print a worksheet.
This is a quick guide on how to insert page break in Excel.
We will also discuss viewing, editing, resetting, and removing page breaks. So, make sure to read the article till the end.
Download the Example Excel Sheet
Please consider downloading the following Excel spreadsheet, which contains the example dataset used to demonstrate how to add page breaks in the following sections.
Practice is the key to success. It will surely help you strengthen your knowledge of the tools and features discussed in this article.
You can skip downloading the above file if you have a datasheet ready with the required dataset.
How to Add Page Breaks in Excel
Page breaks are separators that divide a worksheet into individual pages for printing.
They are inserted by default in Excel, depending on the page size, margin, and scale options. But if the default settings do not work, you can insert them manually.
This process involves a few steps and is pretty straightforward. Even a beginner can easily add page breaks to an Excel sheet without losing formatting while printing.
Consider the following dataset containing employee details, including their name, ID, and department.
Our task is to print employee details by their department on a separate page.
Let’s begin,
- Open the desired Excel spreadsheet
- Go to the “View” tab
- Click on the “Page Break Preview” in the “Workbook Views” section. Refer to the following image,
- You can easily view the location of page breaks indicated by blue dots in your workbook
- You can drag the dotted lines to adjust the page breaks to your desired locations
(Thick blue lines will indicate the manually adjusted page breaks) - You can also right-click on the selected row/column where you want to insert the page break and select the “Insert Page Break” option, as shown in the GIF below
That’s it! We have allocated different pages for employees of different departments.
How to Add a Vertical Page Break in a Workbook in Excel
Sometimes, large datasets with multiple columns need to be handled. Then, you can split the data into different columns and display it on different pages.
To understand this, we will consider the same example used in the above section.
Let’s begin,
- Open your desired Excel workbook
- Make sure you are in the page break preview mode
- Click on the column to the right where you want the vertical page break to appear
(For example, if you wish to add a page break between columns B and C, then click on column C) - Right-click on it and select “Insert Page Break”
After doing this, you will see a thick blue line between columns B and C, indicating that a page break has been inserted there.
How to Use Subtotals to Insert Page Breaks In Excel
One more option in Excel to insert page breaks is using the “Subtotals.”
This method automatically adds page breaks based on specific criteria, making it easier to manage and print large datasets.
Let’s understand this with a simple example. But before that, you must ensure the data is in tabular form.
Task: To print each department’s employee details on a separate page.
Note that the table is sorted by the third (Department) column.
STEP #1 – Add Subtotals
- Select the entire data range
- Go to the “Data” tab
- Click on the “Subtotal” button in the “Outline” group
- A new dialog box will appear on your screen, as shown below,
- In the Subtotal dialog box, choose the column you want to group by in the “At each change in” dropdown
(Since we have to sort by Department, choose “Department” in the dropdown) - Select the function you want to use for your subtotals
(We have selected the COUNT function here) - Tick the box before the column name you want to find the subtotal for
- Ensure the “Replace Current Subtotals” box is ticked if you are adding new subtotals
- Also, check the “Summary below data” box if you want the subtotals to appear below the data groups
- Click the “OK” button to add the subtotals
STEP #2 – Insert Page breaks with Subtotals
Now that we have added the subtotals, it is time to insert the page breaks so that the employee information of each department is displayed on a different page.
Let’s see how to do it,
- Open the Subtotals dialog box again
- In the “Subtotal” dialog box, ensure the tick before the “Page break between groups” option
- Click “OK” to apply the changes
Excel will automatically insert the page breaks between each group based on the selected subtotal criteria.
STEP #3 – Preview Page Breaks
Since we have added the page breaks, checking if the task is done correctly is essential. For that, Excel has a “Preview Page Breaks” feature to preview the employee information sorted by department. For that,
- Go to the “View” tab
- Click on the “Page Break Preview” in the “Workbook Views” group
(You will see the page breaks indicated by dotted lines) - Adjust the page breaks if necessary by dragging the blue dotted lines to the desired locations
After following the steps above, you can print your document by clicking “File > Print.” You can also make necessary adjustments to the page layout, margins, or scaling options to ensure your document is printed correctly.
How to Remove the Page Breaks in a Workbook in Excel
Note that you cannot automatically remove all the page breaks that Excel has inserted.
In this section, we will discuss removing the page breaks that are inserted manually.
Here are the steps,
- Open the Excel and go to the desired sheet
- Hover to the main menu and click on the “View” tab
- Click on the “Page Break Preview” option in the “Workbook Views” section
- Select the entire row below the Page break you need to remove
- Right-click and select “Remove Page Break” as shown below
To Summarize: Insert Page Break In Excel
Page breaks in Excel are essential to enhance the quality of your printed documents. It lets you customize the page layout and ensure desired data within printed pages.
I hope this quick guide taught you all the tricks of inserting and removing page breaks in Excel.
Feel free to comment below if you are stuck somewhere or having any particular issue while printing an Excel spreadsheet. I will try to answer all of your questions as soon as possible.
Make sure to explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.