Durjoy Paul
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.
Keyboard Shortcut
To open the Page Setup dialog box, we can use a keyboard shortcut Alt+P+S+P. It will open up the Page Setup dialog box automatically.
- 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 a Double Row Header in Excel
Similar Readings
- How to Promote a Row to a Column Header in Excel
- How to Make Multiple Sortable Headings in Excel
- Keep Row Headings in Excel When Scrolling Without Freeze
- How to Create Column Headers in Excel
2. Applying Excel VBA Code to Repeat Column Headings on Each Page
You can repeat column headings on each page in Excel using VBA codes. 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 Header Name in Excel VBA
Similar Readings:
- How to Change Column Headings in Excel
- How to Rename Column in Excel
- How to Remove Column Headers in Excel
- How to Keep Row Headings in Excel When Scrolling
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 Make a Row Header 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.
