How to Add Same Header to All Sheets in Excel (5 Simple Ways)

If you’re working with a workbook that contains multiple sheets like monthly reports or departmental data, you might want to add the same header to every sheet for consistency. Repeating this manually can be time-consuming and error-prone.

In this article, you’ll learn multiple ways to apply identical headers across all your worksheets, whether it’s a title row, column labels, or print headers.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim Setup As PageSetup
    Set Setup = Worksheets("Sheet1").PageSetup
    With Sh.PageSetup
        .LeftHeader = Setup.LeftHeader
        .CenterHeader = Setup.CenterHeader
        .RightHeader = Setup.RightHeader
        .LeftFooter = Setup.LeftFooter
        .CenterFooter = Setup.CenterFooter
        .RightFooter = Setup.RightFooter
    End With
End Sub

This method is best for quickly applying the same header or footer to several sheets at once. It’s simple, manual, and doesn’t require any advanced Excel knowledge or tools.

Steps:

➤ Right-click any sheet tab and choose Select All Sheets, or hold  Ctrl  and click each sheet tab individually.

➤ Once the sheets are selected, go to the Insert tab on the ribbon.
➤ Click Header & Footer under the Text group. This will switch the view to Page Layout mode and allow you to edit the header/footer sections.

➤ Click inside the header area at the top of the sheet (or scroll down to the bottom for the footer), and type in your desired content. For example, a company name, document title, or date.

➤ After entering your content, right-click on any sheet tab again and choose Ungroup Sheets to apply the changes and return to normal editing.

Now all sheets with your dataset will have the same header or footer.

Need something more advanced, like inserting page numbers, file names, or custom formatting into your headers or footers? This method gives you full control. Using the Page Setup dialog allows you to customize each section of the header/footer in detail and see how it will appear when printed.

Steps:

➤ Right-click any sheet tab and choose Select All Sheets.

➤ Click on the Page Layout tab in the ribbon.
➤ In the Page Setup group, click the small diagonal arrow in the bottom-right corner to open the Page Setup dialog box.
➤ In the new window, navigate to the Header/Footer tab.
➤ Click Custom Header or Custom Footer depending on what you need to edit.
➤ You’ll see three sections: Left, Center, and Right. Type your custom content in each section or use buttons to insert elements like Page Number, Date, File Name, etc.
➤ Click OK to confirm, then again on the Page Setup dialog.

➤ Go to View >> Page Layout or press  Ctrl  +  P  to preview your header/footer in the print layout.

This method gives you great flexibility and precision when formatting professional headers or footers.

If you often create new sheets and want them to come preloaded with a header or footer, VBA can do it for you automatically. With a simple macro, every new sheet added to your workbook will copy the header/footer from a specific sheet you choose.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ In the Project Explorer on the left side, double-click ThisWorkbook under your current workbook’s name.
➤ In the coding window, paste the following VBA code:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim Setup As PageSetup
    Set Setup = Worksheets("Sheet1").PageSetup
    With Sh.PageSetup
        .LeftHeader = Setup.LeftHeader
        .CenterHeader = Setup.CenterHeader
        .RightHeader = Setup.RightHeader
        .LeftFooter = Setup.LeftFooter
        .CenterFooter = Setup.CenterFooter
        .RightFooter = Setup.RightFooter
    End With
End Sub

➤ Replace “Sheet1” with the sheet that has your desired header/footer.
➤ Close the VBA Editor by clicking the Cross sign (X) or pressing  Alt  +  Q .

➤ Now try adding a new sheet by clicking the plus (+) icon next to your sheet tabs. The new sheet will automatically inherit the same header and footer from your reference sheet.

Use this approach when you want headers across multiple sheets to stay synced. By linking cells with formulas, any change in the master sheet reflects automatically in other sheets.

Steps:

➤ Go to the target sheet where you want to replicate the header.
➤ Click on the cell (e.g., A1) where the header should appear.
➤ Enter formula: =Sheet1!A1 inside the cell.
➤ You can change “Sheet1” to match the name of your master sheet and adjust the cell reference (e.g., A1, B1, etc.) as needed.

➤ Use the fill handle (small square at the corner of the selected cell) to drag the formula across other columns in the row.

➤ Repeat on other sheets as needed to mirror the full header.

Now, if you update the header on Sheet1, it will automatically reflect in all linked sheets.

If you regularly create new workbooks that all need the same header or footer, this method is a huge time-saver. By creating a custom Excel template (.xltx), you can have your headers/footers right into the template.

Steps:

➤ Open a new workbook in Excel and set up your desired header and footer using any method above.
➤ Make any other changes you want in your base template (e.g., column headers, formatting, logo, etc.)

➤ Once everything is ready, click File >> Save As.

➤ Choose a location on your PC where you’d like to store your templates.
➤ Select Excel Template (*.xltx) from the Save as type dropdown.
➤ Hit Save to confirm.

Frequently Asked Questions

Can I link the header cells between sheets to auto-update?

Yes. In each new sheet, use a formula like =Sheet1!A1 to reference the original. However, this works only for static values and not formatting.

Will grouping sheets affect existing data?

Yes, any changes made while sheets are grouped will apply to all selected sheets. Always ungroup afterward to avoid accidental edits.

Can I use VBA to add headers to all sheets automatically?

Yes. By running a simple macro, you can loop through each worksheet and apply a consistent header/footer without doing it manually one by one.

Wrapping Up

In this tutorial, we learned multiple ways to apply the same header or footer across all Excel sheets whether through sheet grouping, the Page Setup dialog, VBA automation, formula linking, or templates. Choose the method that best fits your workflow and reuse it whenever you need consistency in formatting. Feel free to download the practice file and share your feedback.