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.
- 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 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.
- 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.
Read More: How to Add Same Header to All Sheets in Excel (5 Easy Methods)
Similar Readings:
- Move Header in Excel (with Easy Steps)
- How to Print Titles in Excel (5 Easy Ways)
- Hide Header and Footer in Excel (2 Easy Methods)
- How to Print Selected Cells in Excel (2 Easy Ways)
- Insert Logo in Excel Header (4 Easy Ways)
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.
- 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.
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
- How to Repeat Rows in Excel at Bottom (5 Easy Ways)
- Print Address Labels in Excel (2 Quick Ways)
- How to Add Header in Excel (5 Quick Methods)
- Display Print Preview with Excel VBA (3 Macros)
- Insert a Footer in Excel (2 Suitable Ways)
- How to Center the Print Area in Excel (4 Ways)
- Remove Header and Footer in Excel (6 Methods)