Contents
Excel is a professional program that allows you to perform a huge number of tasks, from automated data processing to creating beautiful spreadsheets that can be printed on a sheet and shown to clients. Or make a schedule that can be translated into paper format and hung on the wall in order to look into it more often and not forget about the things that need to be done.
And from time to time, an Excel user is faced with the need to print a table. And at this stage, there are many problems associated with the fact that the document is not in all cases displayed the way you want on a sheet of paper. To solve this problem, there is a page break, which makes it possible to break the entire table into separate parts, each of which will be on a different page. There is a similar thing in another popular office application – Microsoft Word. Only there is a page separator in almost any viewing mode, since a person often needs to immediately visualize what the printed text will ultimately be.
In the case of Excel, the situation is much more complicated, because page breaks are not displayed by default. And not in all situations you need to print a document, so a large number of users do not need this function at all. Therefore, it was hidden, and some functions, and at all, appear only when they are specifically turned on in the settings.
As a rule, the program inserts some page breaks on its own, taking into account the selected sheet options, margin settings, and scaling. True, to do this, you need to activate a certain view mode. The user can also manually define where the breaks should be. To print a document on a specific number of pages, you need to do it yourself. Today we will talk in more detail about what is needed for this.
What is a page break in Excel
So, a page break means a separator of an Excel sheet into several parts. They are always set by default, and in order to understand where the separator is at standard settings, you need to enable a special mode. However, working with page separators is also possible in the standard one, but it will not be as convenient as we would like. Using page mode, the user can understand how the final result will look on print, as well as analyze a huge number of other points. Page mode allows you to:
- See how the information you enter will change the automatic breaks. For example, the user may change the formatting settings or the page orientation, which will change the break line. In addition, it is affected by editing the row height and column width.
- Change the page breaks set automatically to the ones the user desires. To do this, you need to make the appropriate changes in this mode: change their position or remove them altogether. After the page breaks are done, the user can go back to the standard view.
You can understand that in a particular place there is an automatic page break, you can use the characteristic dotted line that goes from one edge to the other. If you add your own break, it will appear as a straight line.
How to insert a page break in an Excel spreadsheet
To add a page break to an Excel spreadsheet, you need to open the corresponding document and go to the “View” tab. After that, perform the following steps:
- Find the Book Views tool group. You need to switch to page mode to activate the view of the individual pages of the table.
- Also on the status bar there is a corresponding button, by clicking on which you can also switch to page mode. Which specific method to use – each user decides for himself, depending on what is convenient for him at a particular moment, and also in what place on the screen the mouse is located. In general, page-by-page display is much more convenient to turn on from the status bar, but the situations are different, and in some cases it is more comfortable to use the main menu (or ribbon, as they say).
- Next, you need to decide what type of break you want to add. If it is vertical, then you must first select the line under which it will be inserted. If you want to set a horizontal break, then you need to select the column that will be located to the right of the place where it will be added.
- Find the Page Setup group on the Page Layout tab. There will be a button we need. We click on it.
- After that, insert a page break by clicking on the appropriate button.
The user can also right-click on a row or column that meets the above criteria and select Insert Page Break.
Sometimes there are situations when a gap cannot be inserted in the manner described above. This is because the “Fit to Page” option is selected. To solve this problem, you need to open the “Page Setup” dialog box and there go to the “Page” tab and select the “Set” scaling option. After that, we confirm our actions and enjoy the result.
How to move a page break to another location
Suppose you inserted a page break in the wrong place. Or the requirements for the final sheet after printing have changed. Or there was any other reason why the page break needed to be moved to a different location. How to do it?
First you need to make sure that the cell dragging feature is enabled. If not, you won’t be able to make changes to the page break. Therefore, you must first activate this setting before changing the location of the page break directly. To do this, you must perform the following sequence of actions:
- Go to the “File” menu. There we look for the item “Settings”. If you are using an older version of Excel (for example, 2007), then you need to click on the “Office” button, and then select the item called “Excel Options”. The location of both buttons is the same, just the appearance is slightly different.
- Open the “Advanced” category, and before that go to the “Editing Options” section. And in this category you need to find the checkbox “Allow fill handles and cell dragging.” Then we confirm our actions.
- Then we click on the sheet in which we need to make changes to the page breaks.
- After that, open the “View” tab and turn on page mode in the same way as described earlier. Or click on the icon in the status bar. Choose the most convenient method for you.
- Next, simply drag the page break to the correct place with the mouse.
You can move and automatic page break. In this case, it will be designated as manual. If necessary, you can later return it to its place by removing the old gap. Let’s talk about how to do this in more detail now.
How to remove page break in excel
Often the user has to remove a page break in Excel if the separation in a particular place was not needed.
- Click on the sheet to which you want to make changes.
- Go to the “View” tab and there we find the “Book View Modes” group, in which we activate the page mode or turn it on from the status bar as described above.
- Further, the sequence of actions depends on what page break was. If you need to remove the vertical one, then you need to select the right column in relation to the page break being removed. If you need to delete a horizontal one, you should select the line that is under the unnecessary break. It is important to remember that deleting an automatic break is technically impossible.
- Then go to the “Page Layout” tab and there we find the “Page Setup” group.
- After clicking on the “Breaks” button, a small additional panel will appear in which we need to click on the “Delete Page Break” item.
There is another way to remove a page break. To do this, you need to take it with the mouse and drag it outside the page view area. But for this feature to be available, you must first activate the drag and drop feature. How to enable it is written in the section on moving an existing gap.
How to remove all manually inserted page breaks
Sometimes the user realizes that they need to remove any page breaks they have inserted. Let’s say he didn’t like the way the document was divided as a whole, and removing each of the breaks individually takes too much time. In such situations, you should use the option to remove all manual breaks. It is enough to press the button once, and everything will return to the standard settings for dividing the table into pages during printing.
To do this, click on the sheet in which we need to make changes. After that, we activate the page mode through the ribbon or the status bar. Then we find the “Page Setup” section, in which we find the breaks menu. Open it and find the item “Reset page breaks” there. 
You can also use the context menu to accomplish this task. To do this, right-click on any cell and click on “Reset page breaks”. After we do this, all previous actions related to dividing the table into pages should be rolled back.
How to return to normal mode
After the user has done everything necessary in the page view mode, he will need to return to the normal one. To do this, you can click the corresponding button on the status bar.
You can also return to the standard view via the ribbon. To do this, go to the “View” tab and select the normal view of the book. In normal mode, page breaks can also be displayed if they are enabled. That is, when switching from page view mode to normal, breaks may remain. This is done for greater user convenience, but not in all situations there is a real need for this.
What should be done to hide the gaps in such a situation? Just close and reopen the book. There is no need to save the file. If you still saved the document, then all page breaks will be saved even after restarting the program.
To fix this situation, you need to disable them in the “File” menu using advanced options. There will be a corresponding button.
How to show and hide page breaks in normal view
Now let’s talk in more detail about how to show or hide page breaks in normal mode, if necessary. As described above, this is done through the “File” menu. You need to move the focus to the file in which you want to make changes, and click on this button. After that, go to the options (or if you have Excel 2007 version, then you need to use the “Excel Options” menu in the “Office” menu).
Then go to the “Advanced” category, and there we find the “Show options for the next sheet” section. There you need to find the option “Show pagination”, which you need to activate or deactivate depending on the situation.
After making these changes and confirming their actions, the user can enjoy the Excel document without page breaks, both manual and automatic, as usual. If you need to see how the table will be divided into several sheets of paper, you can use the appropriate mode.
As you can see, working with page breaks is not that difficult. All settings are in intuitive places. Moreover, there are several ways to perform the same action at once: add breaks, edit and delete them. Before putting the acquired knowledge into practice, it is necessary to practice. In this way, knowledge can be translated into skills. In general, this is a simple function that does not require additional knowledge. Errors in working with it occur extremely rarely. Therefore, you can be sure that you will definitely succeed.