How to Repeat Rows in Excel When Scrolling (6 Suitable Ways)

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.


Download Practice Workbook

You can download the practice workbook from here.


6 Suitable Ways to Repeat Rows in Excel When Scrolling

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.

Repeat Top Row

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.

Repeat 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

Repeat Multiple Rows

  • 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: Smooth Scrolling with Mouse Wheel in Excel (A Detailed Analysis)


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.

Repeat Rows in Excel When Scrolling with Split Feature

  • Secondly, go to the View tab.
  • Thirdly, select the Split option from the Window group.

Repeat Rows in Excel When Scrolling with Split Feature

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

Add Magic Freeze Button to Repeat Rows in Excel

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

Add Magic Freeze Button to Repeat Rows in Excel

  • 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


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.

Insert Excel Tables to Repeat Rows When Scrolling

  • A new dialogue box will pop up named ‘Create Table’. Also, check the option ‘My table has headers’.
  • Now click on OK.

Insert Excel Tables to Repeat Rows When Scrolling

  • 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 Lock Rows in Excel When Scrolling (4 Easy Methods)


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.

Repeat Rows in Excel with Keyboard Shortcut

  • Secondly, one by one press Alt + W. This will take us to the View tab like the below image.

Repeat Rows in Excel with Keyboard Shortcut

  • 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 (4 Quick Ways)


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.

Apply VBA Code to Repeat Rows When Scrolling

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

Apply VBA Code to Repeat Rows When Scrolling

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

Apply VBA Code to Repeat Rows When Scrolling

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

How to Remove Repetition of Rows When Scrolling in Excel

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.

How to Remove Repetition of Rows When Scrolling in Excel

  • As a result, there will be no repetition of rows during the time of scrolling.

Read More: How to Unlock Cells in Excel When Scrolling (4 Easy Ways)


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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo