How to Keep Header in Excel When Printing (3 Ways)

Excel generally tends to print the headers once when a spreadsheet is printed on multiple pages. In this way, you have to check for the column name from the first page every time you have to find which column a particular value belongs to. Excel provides ways to repeat the table header on each page to ease up the process. Additionally, you can keep row and column headings when printing too. In this tutorial, I am going to show how how to keep a header in Excel when printing by both traditional methods and using VBA.


Download Practice Workbook

The dataset I have used for this example is included in this workbook. You can download it and try it yourself while going through the tutorial.


3 Ways to Keep Header in Excel When Printing

For this tutorial, I am using the dataset shown below. The table has 50 rows which aren’t possible to print on one page.

Upon printing, on the second page, it will look something like this.

As you can see there is no header on the second page.

Follow along to learn how to keep the table header on each page, along with row numbers and column letters.


1. Keep Header When Printing Using Page Setup

Page setup options help you to modify the pages to adjust those for better readability after printing. You can make the headings reappear on every page by selecting the specific row as the title. To do that, simply follow these steps.

Steps:

  • In the ribbon, go to the Page Layout tab.
  • Under the Page Setup group, click on Print Titles.

Keeping Header in Excel When Printing from page layout

  • Then, in the Page Setup box that popped up, go to the Sheet tab.
  • Select Rows to repeat at top of the Print Titles.
  • Now, select row 4 from the spreadsheet or type $4:$4 in the box.

page setup box to keep header in excel when printing

  • Then click on OK.
  • Now go to File, then click on Print (or press Ctrl+P for a shortcut) to print the spreadsheet and it will have headers in the later pages.

pdf view to keep header in excel when printing

Read More: How to Print Excel Sheet with Header on Every Page in Excel (3 Methods)


2. Keep Header Using VBA in Excel

You can use Visual Basic for Applications(VBA) to achieve the same result too. In order to do this, you need the Developer tabto show on your ribbon. After you have it, you can follow these steps and achieve the result easily.

Steps:

  • From the ribbon, go to the Developer tab.
  • Select Visual Basic from the Code group.

  • In the VBA window, go to Insert and select Module.

  • Then select the module from the Modules folder and write in the following code.
Sub Repeat_Header_Every_Page()

Dim TotalPages As Long

TotalPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"

ActiveSheet.PrintOut From:=1, To:=TotalPages
.PrintTitleRows = ""

End With
End Sub
  • Save it and close the window.
  • Now, go back to the Developer tab and select Macros.

  • In the Macro box, select the macro with the name you have just created and click on Run.

selecting macro to keep header in excel when printing

  • Save the pages as a PDF document where it will contain a header in the later pages. You can print the table with the header from here.

preview of the print file

Read More: How to Add Same Header to All Sheets in Excel (5 Easy Methods)


Similar Readings:


3. Keep Row and Column Header Using Sheet Options When Printing

Besides keeping just the headers from the table, you can also keep the row and column headings (the numbers and letters of the column) on the page where you are printing your spreadsheet. Follow these steps.

Steps:

  • In the ribbon, go to the Page Layout tab.
  • Then go to the Sheet Options group and under Headings, check the box beside Print.

enabling print headings option

  • Now go to File, then click on Print (or press Ctrl+P for a shortcut). You can see the row and column headings in the preview print and your printed page will have them too.

headings on the excel spreadsheet

Read More: How to Print All Sheets in Excel (3 Methods)


Conclusion

These were the different ways to have the header printed on each page from Excel. I hope you have found this article informative and helpful. For more guides and tutorials visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo