How to Add Same Header to All Sheets in Excel (5 Easy Methods)

If you want to add same header to all sheets in Excel, you have come to the right place. Here, we will demonstrate to you 5 easy methods to do the task effortlessly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


5 Methods to Add Same Header to All Sheets in Excel

The following Excel file has several worksheets. Next, we want to print the worksheets and we want to add same header to all sheets. To do so, we will go through 5 easy methods. Here, we used Excel 365. You can use any available Excel version.

How to Add Same Header to All Sheets in Excel


1. Using Page Setup Option to Add Same Header to All Sheets in Excel

In this method, we will use the Page Setup option from the Page Layout tab to add same header to all sheets in Excel.

Let’s go through the following steps to do the task.

  • First of all, select Page Setup sheet  >> then right-click on it.
  • Then, from the Context Menu >> select Select All Sheets.

Therefore, you can see all the sheets of the workbook have been selected.

How to Add Same Header to All Sheets in Excel

  • After that, go to the Page Layout tab.
  • Afterward, from Page Setup group >> click on the Page Setup drop-down arrow which is marked with a red color box.

At this point, a Page Setup dialog box will appear.

  • Furthermore, go to the Header/Footer group.

Then, you can see Custom Header and Custom Footer options.

  • Here, we selected Custom Header as we want the header on top of the page.

How to Add Same Header to All Sheets in Excel

Next, a Header dialog box will appear.

Here, you can see the Left section, Center section, and Right section.

  • Afterward, we click on the Center section as we want the header in the center of the page.
  • Next, in the Center Section, we will type Employee List as our header.
  • Moreover, click OK.

After that, in the Page Setup dialog box, click OK.

  • Afterward, make sure the worksheets are selected and go to the File tab.

How to Add Same Header to All Sheets in Excel

  • Then, select the Print option.

Therefore, in the Print Preview, you can see the header on page 1.

  • Next, we will click on the rightward arrow to see the header of other worksheets.

As a result, you can see that page 5 also contains Employee List as a header.

Hence, all the sheets have the same header.

How to Add Same Header to All Sheets in Excel

Read More: How to Insert a Footer in Excel (2 Suitable Ways)


2. Use of Page Layout Option to Add Same Header to All Sheets in Excel

In this method, we will use the Page Layout option from the View tab to add same header to all sheets in Excel.

Let’s go through the following steps to do the task.

  • First, select Page Setup sheet  >> then right-click on it.
  • Then, from the Context Menu >> select Select All Sheets.

Therefore, you can see all the sheets of the workbook have been selected.

How to Add Same Header to All Sheets in Excel

  • After that, we will go to the View tab.
  • Then, from Workbook Views  group>> select Page Layout.

  • Afterward, hover your mouse on top of your sheet. You will see three sections under the Header title.
  • Furthermore, we click on the center section as we want our header on the top and center positions of the sheet.
  • Then, we type Employee list as our header in the Center Section.

How to Add Same Header to All Sheets in Excel

  • After that, go to the File tab.

  • Furthermore, select the Print option.

Afterward, in the Print Preview pane, you can see the header Employee list on the top of First sheet.

  • Next, click on the rightward arrow to see the header of other worksheets.

As a result, you can see that page 5 also contains the Employee list as a header.

Hence, all the sheets have the same header.

How to Add Same Header to All Sheets in Excel

Read More: How to Add Header in Excel (5 Quick Methods)


Similar Readings


3. Using Header & Footer Feature

In this method, we will use the Header & Footer feature from the Insert tab to add same header to all sheets in Excel.

Let’s go through the following steps to do the task.

  • In the beginning, select Page Setup sheet  >> then right-click on it.
  • Moreover, from the Context Menu >> select Select All Sheets.

As a result, you can see all the sheets of the workbook have been selected.

  • After that, go to the Insert tab.
  • Afterward, from Text group >> select Header & Footer.

How to Add Same Header to All Sheets in Excel

After that, hover your mouse on top of your sheet. You will see three sections under the Header title.

  • Furthermore, we click on the center section as we want our page number on the top and center positions of the sheet.
  • Next, we type Employee List as our header on the Center Section.

  • After that, go to the File tab.

  • At this point, select the Print option.

How to Add Same Header to All Sheets in Excel

Afterward, in the Print Preview pane, you can see the header Employee List on the top of First sheet.

  • Next, click on the rightward arrow to see the header of other worksheets.

Therefore, you can see that page 5 also contains Employee list as a header.

Hence, all the sheets have the same header.

Read More: How to Repeat Rows in Excel at Bottom (5 Easy Ways)


4. Use of Page Layout Button from Status Bar

In this method, we will use the Page Layout button from the Status bar to add same header to all sheets in Excel.

Let’s go through the following steps to do the task.

  • First of all, select Page Setup sheet  >> then right-click on it.
  • Next, from the Context Menu >> select Select All Sheets.

How to Add Same Header to All Sheets in Excel

Therefore, you can see all the sheets of the workbook have been selected.

  • Afterward, from the Status bar, we will click on the Page Layout button, which is marked with a red color box in the following picture.

  • Then, hover your mouse on top of your sheet. You will see three sections under the Header title.
  • Furthermore, we click on the center section as we want our page number on the top and center positions of the sheet.
  • In addition, we type Employee List as our header on the Center Section.

How to Add Same Header to All Sheets in Excel

  • After that, we will right-click on the sheet Page Layout Button.
  • Next, from the Context Menu >> select Ungroup Sheets.

As a result, you can see all the sheets have been ungrouped.

  • After that, we will click on the sheet Page Layout Button.
  • Then, from the Status bar >> click on Normal, which is marked with a red color box.

Therefore, you can see no header on the sheet.

How to Add Same Header to All Sheets in Excel

  • Next, to see the header on the sheet, click on the Page Layout button from the Status bar.

As a result, you can see the header Employee List in Page Layout Button sheet.

Along with that, to see the header of other sheets, we have to click on the Page Layout button from the Status bar of that sheet.

Here, you can see in the Normal layout the sheet VBA has no header.

How to Add Same Header to All Sheets in Excel

  • However, when we click on the Page Layout button from the Status bar, you can see Employee List as a header for sheet VBA.

Here, one thing must be noted that you can see that all sheets have the same header from the print preview pane.

To do so, after typing the header in the sheet Page Layout Button, do not ungroup the selected sheets rather keep the sheets selected >> go to the File tab >> go to the Print option. And from the Preview pane, you can also see that all the sheets contain the same header.

Read More: How to Print Excel Sheet with Header on Every Page in Excel (3 Methods)


5. Applying VBA to Add Same Header to All Sheets in Excel

Here, we will use VBA code to add same header to all sheets in Excel. This is a handy and quick method to do the task.

Let’s go through the following steps to do the task.

  • In the beginning, go to the Developer tab >> select Visual Basic from the Code group.

At this point, a VBA editor window will appear.

  • Moreover, from the Insert tab >> select Module.

  • After that, we will type the following code in the Module.
Sub Add_header_to_all_sheets()
Dim work_sheet As Worksheet
For Each work_sheet In ThisWorkbook.Sheets
    With work_sheet.PageSetup
         .LeftHeader = ""
         .CenterHeader = "Employee List"
         .RightHeader = ""
    End With
Next work_sheet
Set work_sheet = Nothing
End Sub

How to Add Same Header to All Sheets in Excel

Code Breakdown

  • Here, we created Add_header_to_all_sheets as our Sub procedure.
  • We declared the work_sheet variable as
  • We used the For loop to run the code across the worksheets until it finds the last sheet.
  • Next, within the Loop used the VBA PageSetup property under With Statement where I defined the header position along with text that I want to see in all sheets.
  • Afterward, click on the Run button which is marked with a red color box to run the code.
  • Then, we will close the VBA editor window and return to our worksheet.
  • After that, select VBA sheet >> then right-click on it.
  • Moreover, from the Context Menu >> select Select All Sheets.

Therefore, you can see all the sheets of the workbook have been selected.

  • Next, go to the File tab.

How to Add Same Header to All Sheets in Excel

  • Afterward, select the Print option.

Afterward, in the Print Preview pane, you can see the header Employee list on the top of First sheet.

  • Next, click on the rightward arrow to see the header of other worksheets.

How to Add Same Header to All Sheets in Excel

As a result, you can see that page 5 also contains Employee List as a header.

Hence, all the sheets have the same header.

How to Add Same Header to All Sheets in Excel

Read More: How to Keep Header in Excel When Printing (3 Ways)


Conclusion

Here, we tried to show you 5 easy methods to add same header to all sheets in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo