How to Repeat Column Headings on Each Page in Excel (3 Ways)

When we work with an extensive dataset, Excel tends to show the column headings on the first page. On the other pages, there will be no headings. So, every time you need to go to the first page to get the headings. It is pretty frustrating to do. In Microsoft Excel, you can easily repeat column headings on each page. In this article, we will show how to repeat column headings on each page in Excel. I hope you find this article very interesting and gain more knowledge about this particular topic.

To repeat column headings on each page, we have found three different methods through which you can easily do the work. These methods are basically based on the page setup, name box, and VBA codes. All of these methods are fairly easy to understand. For this article, we take a dataset that includes months and the corresponding sales amount of different countries. As the dataset is large, we can’t put them on one single page.

Then, if we see the 2nd page of the dataset in the print preview, we will see there are no column headings on that page.

We will try to repeat the column headings on each page by using the following three methods.

1. Modifying Page Setup to Repeat Column Headings on Each Page in Excel

Our first method is based on the page setup option. The page setup option basically helps you to modify pages to have better readability after printing. But you can utilize the page setup option while repeating column headings on each page. To understand the method clearly, you need to follow the steps properly.

Steps

• First, go to the Page Layout tab in the ribbon.
• Then, from the Page Setup group, click on Print Titles.

• It will open up the Page Setup dialog box.
• Then, go to the Sheet tab.
• After that, in the Print Titles section, select the Rows to repeat at top.
• Then, select row 4 from the dataset or type $4:$4.
• Finally, click on OK.

• After that, go to the File tab in the ribbon.
• Then, select Print or you can click Ctrl+P for a keyboard shortcut.
• The column heading will appear on the other pages. See the screenshot.

Read More: How to Create Column Headers in Excel

2. Applying Excel VBA Code to Repeat Column Headings on Each Page

You can repeat column header names using the VBA Macro on each page in Excel. To use this method, you need to enable the Developer tab on the ribbon. After that, you can use the VBA code which helps to repeat column headings on each page. Follow the steps properly.

Steps

• First, go to the Developer tab in the ribbon.
• Then, select Visual Basic from the Code group.

• After that, the Visual Basic window will appear.
• Then, go to the Insert tab and select Module.

• It will open up a Module code window where you can write your VBA code.
• Write down the following code.

Sub Repeat_Column_Headings_Every_Page()
Dim TotalPages As Long
TotalPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
ActiveSheet.PrintOut From:=1, To:=TotalPages
.PrintTitleRows = ""
End With
End Sub

• Save the code and close the Visual Basic
• Then, go to the Developer tab in the ribbon.
• From the Code group, select Macros.

• As a result, it will open up the Macro dialog box.
• Then, select the Repeat_Column_Headings_Every_Page option from the Macro name
• Finally, click on Run.

• As a result, this will save as a PDF file where you will get headers on the later page of the dataset. On the first page, we have the following column headings.

• Because using the VBA code we have the same column headings on the second page also. See the screenshot.

Read More: How to Change Column Headings in Excel

3. Changing Name Box to Repeat Column Headings

Our Final method is based on altering the name box. In this method, we select any row, and then in the name box, we set the Print_Titles name. It will eventually repeat column headings on each page. To understand the method clearly, follow the steps properly.

Steps

• First, select row 4 where you have column headings.

• Then, go to the Name Box section where you can modify the name.

• After that, delete everything in the Name Box.
• Then, write Print_Titles.
• Finally, press Enter to apply.

• To verify the result whether repeats column headings on each page, we need to go to the File tab in the ribbon.
• Then, select Print or you can click Ctrl+P for a keyboard shortcut.
• The column heading will appear on the other pages. See the screenshot.

Read More: How to Title a Column in Excel

Things to Remember

• We need to go to the print preview to show the repeated column headings on each page. Otherwise, you can create a pdf file. Then you will get the headings.
• In the VBA method, we will have a PDF file where you will get your desired result.

Download Practice Workbook

Download the practice workbook below.

Conclusion

We have shown three different methods to repeat column headings on each page in Excel. These methods include some Excel commands and a VBA code. All of these methods are fairly easy to use. I hope we cover all possible areas by repeating column headings on each page. If you have any further questions, feel free to ask in the comment box.

Related Articles

• How to Rename Column in Excel
• How to Remove Column Headers in Excel
• How to Change Excel Column Name from Number to Alphabet
• How to Create Excel Table with Row and Column headers
• How to Remove Column1 and Column2 in Excel
• [Fixed] Excel Column Numbers Instead of Letters

<< Go Back to Rows and Columns Headings | Rows and Columns in Excel | Learn Excel