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

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

The sample 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, you might get lost while scrolling. We’ll repeat the top rows that function as column headers.

Method 1 – Repeat Rows at the Top in Excel Using the Page Setup Tool

STEPS:

• Go to the Page Layout tab from the ribbon.
• Under the Page Setup category, click on the anchor icon to open the Page Setup dialog. Alternatively, you can use the keyboard shortcut Alt + P, then press the S + P keys together to display the Page Setup window.

• This will display the Page Setup dialog box.
• Go to the Sheet menu and click on the arrow next to Rows to repeat at top.

• In the dialog box Page Setup – Rows to repeat at top, select the range of rows you want to repeat at top. We selected $1:$4.
• Press Enter.

• This will take you back to the Page Setup dialog. Click on Print.

• A print window will appear with the page preview on the right.

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

Read More: How to Repeat Rows in Excel When Printing

Method 2 – Freeze Panes to Repeat Rows at the Top While Scrolling

STEPS:

• Select the first cell after the row you want to freeze. If you want to freeze the top four rows, select A5.
• Go to the View tab from the ribbon.
• Click on the Freeze Panes drop-down menu under the Window group.
• Select the Freeze Panes option from the drop-down.

• If you scroll down, the rows will be locked at the top.

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

Method 3 – Apply Excel VBA to Repeat Rows at the Top

STEPS:

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

• You can also right-click on your worksheet and select View Code. This will also take you to Visual Basic Editor.

• Copy and paste the VBA code shown below into the module.

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

• Run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

• Go to the File tab from the ribbon.

• Click on Print.
• You’ll get a page preview on the right.

• Go to the next page to see if the top rows remained.

Things to Keep in Mind

• If we select over one sheet, the Rows to Repeat at Top box in the Page Setup dialog box is 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, save the spreadsheet as .xlsm.

Download the Practice Workbook

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