Mukesh Dipto
Sometimes while working with a big dataset we need to repeat rows in Excel when scrolling. This feature helps to keep important rows visible during the time of scrolling. In this tutorial, we will demonstrate 6 suitable ways to repeat rows in Excel when scrolling. To illustrate the methods, we will use a unique dataset. It will help you to understand the concepts of this article more clearly.
How to Repeat Rows in Excel When Scrolling: 6 Suitable Ways
Throughout this article, we will discuss 6 suitable ways to repeat rows in Excel when scrolling. Here we have a sample dataset. The dataset contains sales numbers of different salespeople in different months. We will explain all our methods using this dataset directly or by modifying slightly.
1. Use Excel Freeze Panes Feature to Repeat Rows When Scrolling
Basically, to repeat rows in Excel when scrolling we have to Freeze Panes. Excel provides a built-in feature to Freeze Panes.
1.1 Repeat Top Row
First and foremost, we will repeat the top row in our Excel worksheet when scrolling. In this method, we will use the following modified version of our previous dataset.
Let’s see the steps to perform this method.
STEPS:
- To begin with, go to the View tab.
- In addition, select the Freeze Panes drop-down from the Window section.
- Furthermore, select the option Freeze Top Row.
- After that, we can see that the top row is frozen. A gray-colored line separates the top row.
- Lastly, scroll down using the mouse. We can see the repetition of the first row.
1.2 Repeat Multiple Rows
We will see how to repeat multiple rows in Excel when scrolling in this method. We will use the Freeze Panes feature for this method also. Go through the following steps to perform this method.
STEPS:
- First, select row 6.
- Next, go to the View tab > Freeze Panes drop-down > Freeze Panes.
- Then, a gray-colored line will appear. The rows above the line will repeat in time of scrolling.
- Finally, scroll down using the mouse. We will get results like the following image.
Read More: How to Move Up and Down in Excel
2. Repeat Rows in Excel When Scrolling with Split Feature
We can also use the Split feature to repeat rows in Excel. The split feature in Excel divides the worksheet into different panes. To use the split feature follow the below steps.
STEPS:
- Firstly, select cell C6.
- Secondly, go to the View tab.
- Thirdly, select the Split option from the Window group.
- As a result, the worksheet gets divided into two parts.
- Lastly, using the mouse scroll down. We can see that the rows above the split line repeatedly during the time of scrolling.
3. Add Magic Freeze Button to Repeat Rows in Excel
In this method, we will add a magic freeze button in the Customized Quick Access Toolbar. Using this button we will repeat rows in Excel when scrolling. Since we can access this button easily and quickly, it saves a lot of time. Let’s see the steps to add a magic freeze button.
STEPS:
- In the beginning, select the Customized Quick Access Toolbar drop-down.
- Next, select the More Commands option.
- The above command will open a new dialogue box named ‘Excel Options’.
- Then, go to the Quick Access Toolbar option.
- Now, scroll down in the ‘Choose commands from’ section. Select the option Freeze Panes.
- Subsequently, click ok the Add button.
- Click on OK.
- The above command will add the Freeze Panes drop-down in the Quick Access Toolbar.
- However, select row 6.
- Afterward, click on the Freeze Panes drop-down that we added to the Quick Access Toolbar.
- Moreover, select Freeze Panes.
- In the end, we can see a gray-colored line. If we scroll down using the mouse we will get results like the following image.
Similar Readings
- Excel Not Scrolling with Arrow Keys
- [Fix]: Arrow Keys Not Moving Cells in Excel
- How to Stop Excel from Scrolling to Infinity
4. Insert Excel Tables to Repeat Rows When Scrolling
Another method to repeat rows in Excel while scrolling is to use the Excel Table. We will convert our data range into a table to illustrate this method. Follow the below steps to perform this method.
STEPS:
- In the first place, select the cell range (B4:F11).
- Next, go to the Insert tab.
- Then, select the Table option.
- A new dialogue box will pop up named ‘Create Table’. Also, check the option ‘My table has headers’.
- Now click on OK.
- The above action converts the data range into a table.
- After that, select cell F5.
- Finally, scroll down using the mouse. We can see the header of the table repeats when scrolling.
Read More: How to Turn On/Off Scroll Lock in Excel
5. Repeat Rows in Excel with Keyboard Shortcut
We will use a keyboard shortcut to repeat rows in Excel when scrolling in this method. Again, we will repeat the rows, upper to row number 6. To do this with a keyboard shortcut, follow the below steps.
STEPS:
- Firstly, select row number 6.
- Secondly, one by one press Alt + W. This will take us to the View tab like the below image.
- Thirdly, press the F This will open the Freeze Panes dropdown.
- Press the F key again.
- Lastly, the above actions freeze rows 1 to 5. If you use the mouse to scroll down, you will get results like the following image.
Read More: How to Scroll One Row at a Time in Excel
6. Apply VBA Code to Repeat Rows When Scrolling
Excel VBA always helps us to perform any task more rapidly. In this method, we will use VBAÂ code to repeat rows in Excel. To apply the VBA code in this method follow the below steps.
STEPS:
- To begin with, select the sheet where we want to apply the code.
- In addition, right-click on the mouse.
- Then, select the option View Code.
- The above command will open a new VBA code window.
- Furthermore, type the following code in that code window:
Option Explicit
Sub RepeatRows()
ActiveSheet.Range("A6").Select
ActiveWindow.FreezePanes = True
End Sub- Now, click on the Run button or press the F5Â key.
- A new window like the following image will appear.
- After that, in the Macro Name field, select the macro ‘RepeatRows’.
- Now, click on the Run button.
- The gray line appears to separate rows.
- Finally, scroll down using the mouse to show the repetition of rows.
How to Remove Repetition of Rows When Scrolling in Excel
So far, we have seen how to repeat rows in Excel when scrolling. What if we have to do the exact opposite. In this method, we will see how to remove the repetition of rows in Excel when scrolling. The following image of a dataset shows the repetition of rows. From that dataset, we will remove the repetition of rows.
We will follow the below steps to perform this action.
STEPS:
- First, go to the View tab.
- Next, click on the Freeze Panes drop-down from the ribbon.
- After that, select the option Unfreeze Panes.
- As a result, there will be no repetition of rows during the time of scrolling.
Read More: [Fixed!] Excel Scrolling Too Many Rows
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, this tutorial demonstrates 6 suitable ways to repeat rows in Excel when scrolling. To put your skills to the test, download the practice worksheet included in this article. Leave a comment in the box below if you have any questions. Our team will try to reply to your message as quickly as possible. In the future, keep your eyes peeled for more innovative Microsoft Excel solutions.
