Learn how to work with Page Breaks and Print Areas in Excel.
Working with Page Breaks and Print Areas
When you go to print one of your worksheets – or even an entire workbook – it’s the Print preview through the File tab’s Print command that tells you how your data will lay out on the pages. As you can see here, my worksheet is too wide to fit entirely on a sheet of 8.5 x 11 paper, so it’ll take 2 sheets, and about half of the columns are going to end up on page 2.
As I demonstrate in our video on Printing, you can use the various settings here in the Print view to change your paper size, reduce the margins, switch the orientation of the pages, even apply scaling to shrink your content to fit on the desired number of pages.
Another way to (A) find out how your worksheet will print out and (B) make changes to how the content spreads out over one or more sheets of paper is to work in Page Break Preview, one of your view options on the View tab.
Here, as I switch to that view, you can see the same workbook, and the dashed line showing where that split, which puts the last 5 columns of the worksheet on page 2, occurs. The watermarks Page 1 and Page 2 in gray also show you how many pages you’ll print out on.
To quickly make all of this worksheet fit on one page, all I have to do is drag the dashed line to the far left (note my 2-headed mouse pointer as I point to that dashed line), telling Excel to make all of the columns fit on one page.
A quick switch to the Print Preview on the File tab shows me more clearly how my changes will affect the printout of my worksheet, and I can make one change here – switching to Landscape orientation to let the columns spread out over a larger area horizontally. I can also adjust the Scaling to fully utilize the width of the page.
Now, back on the Home tab, let’s switch to a more demanding worksheet – this sheet has a large Pivot Table on it. Pivot Tables are something you’ll learn to make and edit in our other videos specific to that topic, but suffice to say they’re a great tool for displaying data from various perspectives, for easier analysis.
This worksheet has 4 pages, but the page breaks are at awkward places, cutting off data from the headings and titles that would identify it.
If I drag the dashed and solid borders, around a bit, I can fix that.
As you can see, as I drag the lines to the right, I can bring all the data columns onto one page and then leave the Job Title, Insurance, and Salary boxes on their own page.
Of course, you can return to Normal view for editing your worksheet – after you’ve made all the changes to the print layout that you need – by clicking Normal on the View tab.
To test the outcome of my Page Break Preview adjustments in a more visually accurate way, I can switch to Print view from the File tab, and then make any other changes – to margins, page orientation, etc… - that fine-tune the printout.
Another way to control what gets printed from within your worksheets is to establish a Print Area.
It’s very simple – just select what you want to print – typically a selection you’ll print over and over, as the print area for any given sheet is saved with the worksheet and will appear by default whenever you go to print that worksheet in the future.
After making the selection (here, I’ll select the range from cell B3 through G24), go to the Page Layout tab and in the Page Setup group, choose Print Area, Set Print Area.
When I go to the File tab and click Print, you see that the selected area is set to print, despite there being content all around it in the worksheet.
If the layout of the worksheet changes and you want to change the print area, you can go back to the Page Layout Tab and from the Print Area button, choose Clear Print Area. Then you can make a new selection and select Set Print Area to reestablish the area you want to print by default.