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.

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. Using Page Setup to Keep Header in Excel When Printing

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.

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.

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.

Read More: How to Add Header in Excel

2. Applying VBA Macro to Keep Header 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 tab to 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.

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

3. Using Sheet Options to Keep Row and Column Header 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.

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.