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.
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.
- 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 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.
- 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 in Excel When Scrolling (6 Suitable Ways)
- How to Repeat Formula in Excel for Whole Column (5 Easy Ways)
- How to Repeat Column Headings on Each Page in Excel (3 Ways)
- Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)
- Repeat Text in Excel Automatically (5 Easiest Ways)
- How to Count Repeated Words in Excel (11 Methods)
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.
- 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.
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 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.
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!
- How to Repeat Rows in Excel When Printing (3 Effective Ways)
- Repeat Cell Values in Excel (6 Quick Methods)
- How to Repeat Formula for Each Row in Excel (7 Easy Ways)
- Repeat Cell Value X Times in Excel (6 Easy Methods)
- How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)
- How to Repeat Rows in Excel Based on Cell Value (4 Easy Ways)