Abrar-ur-Rahman Niloy
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 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
Similar Readings:
- How to Print Excel Sheet with Header on Every Page in Excel
- How to Edit Header in Excel
- How to Move Header in Excel
- How to Maintain Excel Header Alignment
- How to Insert Logo in Excel Header
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.
Read More: How to Remove a Header in Excel
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.
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.
