When you work with data in Excel, there is a handy feature that allows you to freeze the top row and header columns (or even lock multiple top rows or left columns).
So when you scroll down, the headers are always visible.
But that’s not the case when you print your reports.
By default, a printed report would only have the header row at the top of the first printed page, and then rest all the other pages won’t have it.
This can make going through the reports a nightmare. Imagine being on the third page and not knowing what a data point represents (and the back and forth to check the headers can be maddening).
The solution – have the headers repeat on every printed page of the report.
In this tutorial, I will show you how to print the top row on every page in Excel. You can also configure it so that multiple top rows or left columns repeat on every page.
Let’s see how to do this!
How to Print the Top Row on Every Page in Excel
Suppose you have a dataset as shown below.
If you print this data, it would take up multiple pages, but the heading will only appear on the first page.
Below are the steps to make sure that the header row repeats on every page that is printed:
- Click the ‘Page Layout’ tab
- In the ‘Page Setup’ group, click on the dialog box launcher (the small tilted arrow as shown below)
- In the ‘Page Setup’ dialog box, click on the ‘Sheet’ tab
- Click on the field next to the ‘Rows to repeat at top’ option
- Select the top row (you will notice that $1:$1 is automatically inserted in the “Rows to repeat at the top” field.
- Click OK.
Now, when you print this data, you will notice that the top row header repeats on every page that is printed.
You can check this by Clicking on the ‘File’ tab and then clicking on the ‘Print’ option. This will open the Print Preview pane.
Click on the arrow icons at the bottom of the Print preview pane, and you should see the headers repeat on each of the pages.
Just like we have configured the settings to print the top row on every page, you can also set it to print multiple header rows on every page.
To do this, in Step 5 of the above steps, instead of selecting the top row, select multiple top rows that you want to repeat on every printed page.
Note that you need to select contiguous rows (i.e., you can set row number 1, 2, and 3 to be printed on every page, but you cannot set row number 1 and 3 to be repeated on every printed page)
You can also set the left-most column (or multiple left-most columns) to repeat on every page when printed. The process is exactly the same, where, in Step 5, instead of choosing the row, you can select the column that you want to repeat.
And of course, you can also set the top row and the top column to repeat on every printed page.
Also read: Freeze Columns In Excel
Print the Rows on Every Page Using the NameBox Trick
Now, let me also share an amazing Excel trick that not many people know about.
The benefit of using the above method (where we use the Page Setup dialog box) is that it gives you a lot more options when you’re printing your reports.
But if all you want to do is make sure that the top through or the leftmost column repeats on every printed page, this NameBox trick is a lot faster.
Suppose you have a dataset as shown below.
Below are the steps to make sure the headers in printed on every page
- Select the header row
- Click on the ‘NameBox’ field
- Manually enter the text Print_Titles
- Hit the enter key
That’s it! Now when you print the dataset, the first row would repeat on every page.
In case you want to repeat multiple header rows or columns, select those first and then name these as Print_Titles
Why this Works?
When you use the Page Setup dialog box to set the rows and columns that should be repeated, Excel automatically creates a Named Range with the name Print_Titles.
So, instead of going the Page Setup dialog box route, if you create the same Named Range yourself, that would work too.
How to Repeat Header Rows on Every Page Except the Last Few Pages?
Unfortunately, there is no way in-built way to make sure that the headers print on every page except the last page (or the last few pages).
One workaround could be to have the pages, where you do not want the headers to repeat, so be in a separate worksheet.
But this may not be ideal for everyone.
Below is the VBA macro code that will do this.
Sub RepeatHeadersPrintExceptLastPage()
Dim TotalPages As Long
TotalPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
ActiveSheet.PrintOut From:=1, To:=TotalPages - 1
.PrintTitleRows = ""
ActiveSheet.PrintOut From:=TotalPages, To:=TotalPages
End With
End Sub The above code uses Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”) to get the total number of pages in the dataset that will be printed.
It then prints all the pages (except the last one), and during this time, the PrintTitleRows is set to $1:$1. So the first row will be printed as a header on all pages except the last one.
Then the last page is printed where the PrintTitleRows property is set to null, so no header rows are printed on the last page.
This is a clumsy workaround, but if all you want to do is print all the data in the worksheet so that the header repeats on every page except the last page, this would work.
You might have to modify the code a little bit in case you want more headers to repeat or also want the column headers to be printed on each page.
I hope you found this tutorial useful!
Other Excel tutorials you may like:
