10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

You've done the hard work of building your spreadsheet. Now you just need to go through your page setup to ensure it looks great when printed. 

All the little things like what you want to see in your headers and footer, the size or your margins and which orientation works best (portrait or landscape).

Plus, how do you ensure that the pages don’t break in weird places?  

And how do you make headings and labels appear automatically on each page without lots of copy and paste?

Read on, my friend. All these questions are answered below.

1. Setting the margins

1.  Click the Page Layout tab.

2.  Click the Margins button.

2.  Choose from one of the pre-set margin configurations – NormalWide or Narrow.

4.  Alternatively, click the Custom Margins link. This opens up the dialog box that appeared in previous version of Excel and allows you to set your own margins.

Choose the best margin size for your page setup

5.  Set the margins by clicking the spinner boxes or typing values directly into the text boxes.

Choosing a custom margin size for your printed Excel spreadsheet

2. Positioning your page content

To align the worksheet contents to the centre of the page vertically and/or horizontally:

1.  Click the Page Layout tab.

2.  Click the Margins button.

3.  Choose Custom Margins.

4.  Tick the boxes labelled Horizontally or Vertically, as appropriate.

How to center the page contents horizontally and/or vertically

3. Changing the page orientation

PORTRAIT orientation is where the page sits upright with the short side on the bottom, like a model posing for a portrait.

LANDSCAPE orientation is where the page sits flat with the long side on the bottom, like a landscape painting

To set the orientation:

1.  Click the Page Layout tab.

2.  Click the Orientation button.

3.  Choose Portrait or Landscape.

Choose the page orientation

4. Creating a simple header and footer

A header and footer are set up once. It then appears on every page that is printed. Typically, headers and footer show information like the author or company name, the filename, dates etc.

Page Setup: Creating a simple header and footer

1.  Click the Insert tab.

2.  Click the Header & Footer icon

(Note: If you are using a small screen, like on a laptop and you cannot see the icon, look for the Text group-icon, then click the drop-down arrow and you will see the group expanded and the Header & Footer is one of the options.

3.  Type your header text into the header box on the worksheet. You can utilise the icons on the Header & Footer Tools Design ribbon.

Enter your header or footer details into the 3 placeholders provided

5. Using the header and footer tools

1.  Click inside the header or footer text box on the worksheet. This activates the Header & Footer Tools ribbon.

Header & footer Design ribbon

2.  Click one of the icons on the ribbon to insert a placeholder.

For example, if you click the File Name icon, Excel inserts a &[File] placeholder. When you print your worksheet, Excel interprets this into the file name of the workbook.

6. Using preset header and footer entries

If you do not feel confident inserting your own header and footer elements, you can use one of the pre-set entries that combines elements such as Page x or y and Author, Page Number and Date.

1.  Click the Insert tab.

2.  Click the Header & Footer icon.

3.  On the Design tab (under Header & Footer Tools), click the Header icon or the Footer icon.

4.  Select from the pre-set entries listed.

Using pre-set header and footer entries

Where there are several items separated by commas, Excel places the first item on the left of the page, the second in the centre and the third on the right.

7. How to make column headers or row labels appear automatically on every page

If you have a table of data big enough to spill onto two or more pages, you'll already know that your column headings and row labels do not automatically print on every page.

So, many parts of your printed spreadsheet have no reference point because the labels are missing.

Some people resort to lots of inserting, copying and pasting, but when extra data gets added or existing data is removed, the headers and labels have to be re-positioned again.

Other people just print as-is then cut it up with scissors and carefully tape it together on the wall in a Frankensheet approach.

Here is a better way:

1.  Select the Layout tab (formerly the Page Layout tab).

2.  Click the Print Titles icon. The Sheet tab within the Page Setup box is displayed.

The Sheet tab of the Page Setup dialog

3.  Place the cursor in the Rows to repeat at top box

4.  Move the mouse pointer into the worksheet (it will become a
black horizontal arrow).

5.  Click any row (or rows) that you want to display on every page. Excel will add some funny notation into your dialog box, something like $3:$3. This just means the headings on row 3 will appear where appropriate on every printed page. Neat huh?

The dollar signs apply something call absolute referencing (which just means 'fixed').  

6.  Place the cursor in the Columns to repeat at left box.

7.  Move the mouse pointer into the worksheet (it will become a
black vertical arrow).

8.  Click any column (or columns) that you wish to display on every page. Again, Excel will add some funny notation into your dialog box, something like $A:$A, which means the labels in column A will appear where appropriate on every printed page. 

Rows to repeat at top and Columns to repeat at left

Next time you check the Print Preview, you’ll see that every page has column headings and row labels.

8. Controlling exactly where pages break

1.  Select the View tab.

2.  Click the Page Break Preview button.

Page Break Preview icon: Previewing & changing the page break positions

Page breaks are shown by a thick blue dashed line.

The outer boundary of the printable area is shown by a thick blue solid line.

Final stage of the Page Setup: Page breaks are shown by a thick blue dashed line. The outer boundary of the printable area is shown by a thick blue solid line.

To move the page break:

1.  Position the mouse pointer over the blue dashed line.

2.  Click and drag the break to a new position.

Excel will expand or reduce the content to fit the page accordingly. For example, if you move a page break to include more columns, Excel reduces the size of the content, so it can still fit onto the page when printed.

9. Print preview

You can check what your spreadsheet will look like before you print it.

1.  Click the File tab.

2.  Select the Print option on the left sidebar.

A preview of your page is shown in the main window,

3.  To browse each page, use the navigation buttons underneath the preview image. This  has been circled on the following screen capture.

Always preview the layout before printing to ensure that your page setup is complete

10. Printing selected cells, worksheets or everything

To control what is printed:

1.  Click the File tab.

2.  Select Print on the left sidebar.

3.  In the area under Settings, choose Print Active Sheets, Print Active Sheets, Print Entire Workbook or Print Selection.

4.  To print a selection of pages, choose Print Selection, then specify the page or pages you would like to print.

5.  In the Copies section, specify how many copies you wish to print.

How to print particular cells, worksheets or the whole caboodle

11. Excel page setup: Key takeaways

  • Use your common sense to set appropriate margins and page orientation.
  • To add a header or footer, click the Insert tab, then click the Header & Footer icon. If this icon is not visible, click the Text icon to expand the icon group, then click the Header & Footer icon.
  • Excel provides a dedicated set of header and footer tools. Click the Design tab under Header & Footer Tools.
  • The Page Setup group on the Page Layout ribbon contains options to set margins, orientation, page size, print area and print titles. 
  • Click the launcher in the bottom-right corner of the Page Setup group to display a dedicated Page Setup dialog with the full suite of options.
  • Click the Print Titles icon on the Page Layout ribbon then set the Rows to repeat at top and Columns to repeat at left to ensure every page has headings and labels. 
  • View the current page breaks by clicking the Page Break Preview button on the View ribbon.
  • Move the existing page breaks by hovering over the thick blue solid or dashed lines, then click and drag to move them.
  • There is a Print Preview option. Click the File tab, then Print. The preview appears in the main window. Alternatively, customise the Quick Access Toolbar to display the Preview icon. Or just press Ctrl P.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>