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

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.

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.

Sample Dataset to Repeat Rows 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 reach your desired target. But, if you face any problems regarding versions, please leave us a comment below.

1. Using Print Titles Command to Repeat Repeat Rows while Printing

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.

Access to the Page Setup Dialogue Box

  • 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.

Customize Page Setup Options to Repeat Rows in Excel when Printing

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.

Repeated Rows in Excel When Printing

Read More: How to Repeat Rows at Top in Excel

2. Rename the Rows with Print_Titles (Which Are to Be Repeated While Printing) in the 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.

Click on Row Header

  • 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 the desired output.

Rename Name Box to Repeat Rows in Excel When Printing

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

Read More: How to Repeat Rows at Top of Specific Pages in Excel

3. Applying VBA Code to Repeat Rows When Printing in Excel

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.

Access to the Visual Basic Tool

  • As a result, the Microsoft Visual Basic for Applications window will appear.
  • Now, from the VBAProject group choose Sheet 4.
  • 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

Write Code in the VBA Window t Repeat Rows in Excel When Printing

  • Afterward, press Ctrl + S.

Save VBA Code

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

Access the File Tab

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

Choose Save As Option

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

Excel Save As Window

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

Save the Excel as .xlsm Format

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

Access the Visual Basic Tool

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

Run the VBA Code

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

Run the Macro

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

Read More: How to Make a Pattern Repeat in Excel

Download Practice Workbook

You can download our practice workbook from here for free!


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. Thank you!

Related Articles

<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF