How to Insert Sheet Name into Header or Footer in Excel?

by

Excel allows you to specify the header and footer in a worksheet, which can be quite useful if you need to print your data in the worksheet.

A lot of times, users need to add the name of the worksheet in the header or the footer. This is especially useful if you have worksheets for different months, different product lines, or different departments.

Having the sheet name in the header or the footer would make it easy for anyone who’s printing the data to know which page refers to which worksheet.

In this tutorial, I’ll show you a really simple and quick way to add the sheet name into the header or the footer in Excel.

Insert Sheet Name into Header or Footer

Excel has an inbuilt header and footer option that allows you to quickly add the sheet name to it.

In case you need to do this for all the worksheets in your file, you can do it using the VBA code (As it will be faster)

Using the Header & Footer Option

For this tutorial, let’s take an example of an Excel file where I have separate worksheets for each month.

Sheet names

Below are the steps to add the sheet name to the header in Excel:

  1. Select the worksheet in which you want to insert the name into the header. In this example, I will select the sheet named ‘Dec’
  2. Click the ‘Insert’ tab
Click the Insert tab
  1. Click on the Text option
Click the Text option
  1. Click on the ‘Header & Footer’ option. This will display the header and footer area in your worksheet and place the cursor within it
Click on Header and Footer
  1. The header area has three sections in it. Place the cursor where you want to insert the sheet name (in this example I’ll go with the middle section)
  2. Enter the text &[Tab]
Enter &[Tab] in the header section
  1. Click anywhere outside of the header area

You’ll notice that the sheet name has now been added to the header.

Sheet name is inserted in the header

&[Tab] is a code that tells Excel to add the sheet name in the header or the footer area. Note that this would only work in the header/footer area, and not in the cells in the worksheet.

You can also combine the &[Tab] shortcode with other similar codes to have more details in the header or the footer area.

For example, if you want to include the date after the sheet name, then you can use the below code:

&[Tab] – &[Date]

And if you want to show the filename followed by the tab name then you can use the below code

&[File] – &[Tab]

Just like I added the sheet name to the header area, you can also add it to the footer area by scrolling down and then clicking on the footer section. This will again show you the three boxes where you can choose where to insert the sheet name.

A couple of important things to know when you insert the sheet name in the header or footer of the worksheet in Excel:

  • Adding the sheet name to the header or the footer would only be applied to that specific worksheet. If you want to do this for all the worksheets in your file, you either need to do this manually or use the VBA method (covered next)
  • The shortcode &[Tab] updates dynamically. If you change the sheet name, this would automatically change to reflect the new sheet name
  • If you want to remove the sheet name from the header or the footer, you need to repeat the same process and delete the shortcode &[Tab]
Also read: How to Get Sheet Names in Excel?

VBA to Insert Sheet Name into the Header in Every Sheet

Below is the VBA code that would cycle through all the worksheets in your Excel workbook and add the sheet name into the footer of each sheet

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Insert_sheetname_header()
Dim ws As Worksheet
For Each ws In Worksheets
ws.PageSetup.CenterHeader = "&A"
Next ws
End Sub

And below is the VBA code that would enter the sheet name into the footer of each sheet in the Excel workbook

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Insert_sheetname_header()
Dim ws As Worksheet
For Each ws In Worksheets
ws.PageSetup.CenterFooter = "&A"
Next ws
End Sub

So this is how you can easily add the sheet name to the header or footer in any worksheet.

In case you want to do it for a single sheet, you can do it manually using the header and footer options, and if you want to do it for all the worksheets in your file, you can use the VBA method.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment