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

While working with Microsoft Excel, we may utilize examining large quantities of data. There are many various methods to modify the worksheets in Microsoft 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.


Download Practice Workbook

You can download the workbook and practice with them.


3 Suitable Ways to Repeat Rows at Top in Excel

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. But 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 so 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.

excel rows to repeat at top


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.

excel rows to repeat at top

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

excel rows to repeat at top

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

excel rows to repeat at top

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

excel rows to repeat at top

  • 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 at Top of Specific Pages in Excel


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 Header Row When Scrolling in Excel (6 Ways)


Similar Readings


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

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.

excel rows to repeat at top

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

Note: You don’t need to change the code. All you can do is just change the range as per your requirements.
  • 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 1s top rows are shown at the top.

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

Read More: [Fixed!] Excel Rows to Repeat at Top Not Working (4 Solutions)


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 spreadsheet with macro enable extension .xlsm.

Conclusion

The above methods will assist you to 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. Or you can glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo