How to Repeat Rows in Excel When Printing (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we have to print large dataset which requires multiple pages to print. Now, when printing multiple pages, it is better if we can print the top header rows on every page. It would help people understand the prints better. Now, if you are looking for ways to do this task, you have landed in the perfect place. In this article, I will show you x effective methods to repeat rows in Excel when printing.

Table of Contents hide

Download Practice Workbook

3 Ways to Repeat Rows in Excel When Printing

1. Use Print Titles Command

2. Rename the Rows with Print_Titles (Which Are to Be Repeated While Printing) in Name Box

3. Apply a VBA Code to Repeat Rows When Printing

Conclusion

Download Practice Workbook

You can download our practice workbook from here for free!

Repeat Rows When Printing.xlsm

3 Ways to Repeat Rows in Excel When Printing

Say, you have a dataset of 100 students’ marks in 3 subjects. Now, you want to print the mark sheet. Excel automatically prints the whole mark sheet in 3 pages. But, for the 2nd and 3rd pages, there is no header row. For which, it becomes difficult to understand after the printout. Now, you can follow any of the 3 ways below to repeat the header row in Excel when printing.

We have used the Office 365 version of Microsoft Excel here. You can use any other version that is available to you to avail your desired target. But, if you face any problems regarding versions, please leave us a comment below.

1. Use Print Titles Command

You can use the Page Setup dialogue box (which appears after pressing the Print Titles button) to repeat rows in Excel when printing. Go through the steps below to do this.

📌 Steps:

First and foremost, go to the Page Layout tab >> Print Titles tool.

As a result, the Page Setup dialogue box will appear.
Subsequently, go to the Sheet tab from the dialogue box >> write $4:$4 in the Rows to repeat at top: text box >> click on the OK button.

Consequently, you will see that now you will have your header row which is the 4th row on your dataset, being repeated on every page when printing. And, the prints would have a preview like this.

2. Rename the Rows with Print_Titles (Which Are to Be Repeated While Printing) in Name Box

Another quick trick to repeat rows in Excel when printing is to rename them with Print_Titles in the Name Box. Follow the steps below to accomplish your desired outcome in this way.

📌 Steps:

At the very beginning, click on the row header number of your header row (4 here) that you want to repeat on every page. If you have multiple rows to show on every page-top, then select all of them.

Afterward, click on the Name Box on the left corner of the formula bar.
Now, write Print_Titles in the Name Box area.

Print_Titles must be used to rename. Otherwise, you will not get desired output.

Thus, your task is accomplished. Now, if you print the pages, you will see that every page has the 4th row repeating on top.

Similar Readings

3. Apply a VBA Code to Repeat Rows When Printing

Moreover, you can apply a VBA code to repeat rows in Excel when printing. Follow the steps below to achieve your result in this way.

📌 Steps:

At the very beginning, go to the Developer tab >> Visual Basic tool.

Asa result, the Microsoft Visual Basic for Applications window will appear.
Now, from the VBAProject group choose the Sheet4.
Subsequently, write the following code in the code window of Sheet4.

Sub RepeatRowsPrint()
Dim NumberOfPages As Long
NumberOfPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
ActiveSheet.PrintOut From:=1, To:=NumberOfPages
End With
End Sub

Afterward, press Ctrl + S.

Now, close the VBA window and go to the File tab from the main Excel ribbon.

Subsequently, choose the Save As option from the expanded File tab.

As a result, the Excel Save As window will appear. Click on the Browse option.

Consequently, the Save As dialogue box will appear.
Following, choose the Save as type: option as .xlsm format.
Finally, click on the Save button.

Now, go to the Developer tab >> Visual Basic tool again.

Now, click on the Run icon button for the Sheet4 code.

As a result, the Macros window will appear.
Subsequently, choose the macro you created and click on the Run button.

And thus, you will be able to print the pages with the repeated header row.

Conclusion

So, I have shown you the most effective ways to repeat rows in Excel when printing. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!