How to Repeat Rows at Top in Excel (3 Suitable Ways)

While working with Excel, we may utilize examining large quantities of data. There are many various methods to modify the worksheets in Excel. Anyone who prints or gets a large document and finds it challenging to identify a unit of information with the right category will find it challenging to understand how to set their Excel rows to repeat at the top. In this article, we will demonstrate different ways to repeat rows at the top in Excel.

How to Repeat Rows at Top in Excel: 3 Suitable Ways 

The information in each field is often identified in a row at the top of the worksheet. But by default, that row only prints on the first page. It’s possible that you individually attempted to add that row after page breaks. However doing so may be time-consuming and annoying, especially if we have to remove any rows from the worksheet.

For this, we are going to use the following dataset. The dataset contains some items, the quantity of each item, and the total number of sales of each item. As this is a quite large dataset while scrolling the data or printing them we might face problems if the heading rows don’t repeat at the top. So, let’s follow the methods to repeat rows at the top in Excel.

1. Repeat Rows at Top in Excel Using Page Setup Tool

Page Setup is the presentation and layout of a printing page that controls a set of particular parameters. This kind of resource is present in many contemporary word processors and other document preparation programs, such as those found in Microsoft Office products. We can quickly repeat rows at the top using the page setup tool in Excel.

STEPS:

• Firstly, go to the Page Layout tab from the ribbon.
• Secondly, under the Page Setup category, click on the tint arrow icon to open the Page Setup dialog.
• Alternatively, you can use the keyboard shortcut Alt + P after that, press the S + P key together to display the Page Setup window.

• This will display the Page Setup dialog box.
• Now, go to the Sheet menu and click on the arrow beside Rows to repeat at top option.

• And, you will be able to see the dialog of Page Setup – Rows to repeat at top, where you can select the range of rows you want to repeat at top. In our case, we select $1:$4.
• Then, press the Enter key on your keyboard.

• This will again take you back to the Page Setup dialog. Now to make sure that this works properly, click on Print.

• A print window will appear and the top rows of page 1 are visible if you pay great attention to the region that is highlighted.

• Go to the following page to see the rows at the top as well.

Read More: How to Repeat Rows in Excel When Printing

2. Freeze Panes to Repeat Rows at Top While Scrolling

We may freeze our rows using the Excel Freeze Panes option so that they won’t move when we scroll down or across to see the remainder of our page. We can repeat rows at top while scrolling down by using the freeze panes in Excel.

STEPS:

• To begin with, select the bottom of the cell where you want to use the freeze panes.
• Then, go to the View tab from the ribbon.
• After that, click on the Freeze Panes drop-down menu, under the Window group.
• Next, select the Freeze Panes option from the drop-down.

• And, that’s it! Now, if you scroll down, the rows will be shown at the top and this will work as this repeats at the top.

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

3. Apply Excel VBA to Repeat Rows at Top

With Excel VBA, users can easily use the code which acts as Excel menus from the ribbon. To use the VBA code to generate a table from the range, let’s follow the procedure.

STEPS:

• Firstly, go to the Developer tab from the ribbon.
• Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

• Instead of doing this, you can just right-click on your worksheet and select View Code. This will also take you to Visual Basic Editor.

• Further, copy and paste the VBA code shown below.

VBA Code:

Sub Rows_to_Repeat()
Dim rw As Long
For rw = 1 To Worksheets.Count
    Sheets(rw).PageSetup.PrintTitleRows = "$1:$4"
Next rw
End Sub

• After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

• To make sure that the rows are repeated at the top, go to the File tab from the ribbon.

• This will take you backstage of the Excel option. Click on Print.
• If you take a close look at the highlighted area, on page 1 top rows are shown at the top.

• Now, going to the next page will also show the rows at the top.

Things to Keep in Mind

• If we select over one sheet, the Rows to Repeat at Top box in the Page Setup dialog box are unavailable.
• Click any sheet that is not already chosen to remove the selection of all sheets.
• Ungroup Worksheets may be found on the menu that appears when you right-click the tab of a specified sheet if there are no unselected sheets present.
• While using Excel VBA, make sure that you save the spreadsheet with macro enable extension .xlsm.

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above methods will assist you in repeat rows at top in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

Related Articles

• How to Repeat Rows for a Specified Number of Times in Excel
• How to Repeat Rows in Excel at Bottom
• How to Make a Pattern Repeat in Excel
• Repeat Text in Excel Automatically
• [Fixed] Repeat Last Action Not Working in Excel

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