How to Repeat Header Row When Scrolling in Excel (6 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. If you are curious to know how you can Repeat the header row when scrolling in Excel, then this article may come in handy for you. In this article, we will discuss how you can Repeat the header row in Excel when scrolling with an elaborate explanation.


Download Practice Workbook

Download this practice workbook below.


6 Easy Ways to Repeat Header Row When Scrolling in Excel

For the purpose of demonstration, we are going to use the below dataset. In this dataset, we got the sales amount of each salesperson from the month of January to the month of April. We will show in six different ways how you can Repeat one or more than one-row header when scrolling.

Repeat Header Row When Scrolling in Excel


1. Using Freeze Pane Command

Basically, to Repeat rows in Excel when scrolling, we have to Freeze Panes. Excel provides a built-in feature called the Freeze Panes command in the View tab.


1.1 Freeze Only Top Row

Initially, we’ll Repeat the row at the top of our Excel spreadsheet whenever we scroll. For this method, we’ll make use of the following modified version of our prior dataset.

Steps

  • To begin with, we can go to the View tab and then click on the Freeze Panes drop-down.
  • Then from the drop-down menu, click on the Freeze Top Row command.

Using Freeze Pane Command to Repeat Header Row When Scrolling in Excel

  • Following that, we can see that the top row is frozen if we scroll. The top row is separated by a grey line.

Using Freeze Pane Command to Repeat Header Row When Scrolling in Excel


1.2 Freeze Multiple Rows

We will also use the Freeze Panes feature for this method. We will also use the Freeze Panes feature for this method. To use this method, go through the steps below.

Steps

  • First, select cell C5 and then click on the View tab.
  • Then click on the Freeze Panes drop-down.
  • From the drop-down menu, click on Freeze Panes.

Using Freeze Pane Command to Repeat Header Row When Scrolling in Excel

  • Then a grey line will appear. When you scroll, the rows above the line will Repeat.
  • We will get results similar to the one shown below. We will get results like the following image.

Using Freeze Pane Command to Repeat Header Row When Scrolling in Excel

Read More: How to Repeat Multiple Rows in Excel (4 Effective Ways)


2. Utilizing Magic Freeze Button

We’ll add a magic freeze button to the Customized Quick Access Toolbar using this method. When we scroll through Excel, we will use this button to Repeat rows. We save a lot of time because we can get to this button quickly and easily.

Steps

  • To begin, we need to add the freeze pane button to the Quick Access Toolbar.
  • To do this, click on the File in the corner of the worksheet.

Utilizing Magic Freeze Button to Repeat Header Row When Scrolling in Excel

  • Then click on the Options on the startup page in Excel.

  • In the Excel Options dialog box, click on the Quick Access Toolbar.
  • Then from the side panel menu, click on the Freeze Panes.
  • Then click on the Add>>.
  • Clicking “Add” will add the Freeze Panes on the right block of the Options menu.
  • Click OK after this.

Utilizing Magic Freeze Button to Repeat Header Row When Scrolling in Excel

  • Then we get back to the original worksheet.
  • Select cell C6.
  • We can notice that the Freeze Pane options menu is now in the Quick Access Menu, with an arrow sign.
  • Click on the arrow sign and then click on the Freeze Pane command.

Utilizing Magic Freeze Button to Repeat Header Row When Scrolling in Excel

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


3. Using 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. The split feature in Excel divides the worksheet into different panes.

Steps

  • To begin with, select the cell from where you want to freeze the sheet. In this case, we select C6.
  • After that, go to the View tab, and click on the Split command in the Window group.

Utilizing Magic Freeze Button to Repeat Header Row When Scrolling in Excel

  • Then we will see that the sheet is now split at cell C6.
  • And now every row over row 6 is now Repeated, as we scroll down in the sheet.

Utilizing Magic Freeze Button to Repeat Header Row When Scrolling in Excel

💬 Note

  • While you use the split feature to Repeat the Header row, you can’t shift to other sheets. You will be stuck on that particular sheet until you disable Split view.

Similar Readings


4. Utilizing Keyboard Shortcut

When scrolling in Excel, we will use a keyboard shortcut to Repeat rows. We will Repeat the rows from upper to lower row number 6. Follow the steps below to accomplish this with a keyboard shortcut.

Steps

  • In the beginning, select the cell from where you want to Repeat the row header. In this case, we select cell C6.
  • After that, press Alt+W.
  • Pressing Alt+W will highlight every shortcut in the worksheet.
  • Displaying which command in the worksheet is executed by pressing which button in the worksheet.

Utilizing Keyboard Shortcut to Repeat Header Row When Scrolling in Excel

  • At this moment, press “F” twice.
  • One press is for selecting the Freeze Pane, another press is for choosing the Freeze Pane option.

Utilizing Keyboard Shortcut to Repeat Header Row When Scrolling in Excel

  • After pressing the command, you will notice that the row header over row 6 is now Repeated.


5. Utilizing Excel Table

We will turn our data range into a table to demonstrate this method. To use this method, follow the steps below.

Steps

  • To begin, select the whole range of cells that contain your data.
  • And from there, go to the Insert tab.
  • Then from the Insert tab, click on the Table from the Tables group.
  • Doing this will convert the range of cells into a table.

  • After clicking on the Table, we can see a small dialog box.
  • In that dialog box, tick mark the My table has headers checkbox.
  • Click OK after this.

  • After this, you will notice that there is a filter icon on every header.
  • That means our data is now converted to a Table.

Utilizing Excel Table to Repeat Header Row When Scrolling in Excel

  • Then as you scroll down, you can see that the row header of the table is now set in the top row of the sheet.

Utilizing Excel Table to Repeat Header Row When Scrolling in Excel


 6. Embedding VBA Code

Using a small VBA macro can save a tremendous amount of time to Repeat the header row in Excel when scrolling.

Steps

  • To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

Embedding VBA Code to Repeat Header Row When Scrolling in Excel

  • Then there will be a new dialog box. In that dialog box, click on the Insert > Module.
  • Next, in the Module editor window, type the following code:
Option Explicit
Sub RepeatRows()
ActiveSheet.Range("A6").Select
ActiveWindow.FreezePanes = True
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

Embedding VBA Code to Repeat Header Row When Scrolling in Excel

  • After clicking View Macros, select the macros that you created just now. The name here is RepeatRows. Then click Run.

  • After clicking Run, you will notice that the rows over row 6 are now Repeated.

Embedding VBA Code to Repeat Header Row When Scrolling in Excel


Conclusion

To sum it up, the issue of how you can Repeat a header row when scrolling in Excel is answered here with 6 different methods with elaborate explanations. We also used a VBA macro with them. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.

For this problem, a macro-enabled workbook is attached where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo