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.

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.

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.

Note: You don’t need to change the code. All you can do is just change the range as per your requirements.

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

Repeat Rows at Top.xlsm