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.
Below are the steps to add the sheet name to the header in Excel:
- Select the worksheet in which you want to insert the name into the header. In this example, I will select the sheet named ‘Dec’
- Click the ‘Insert’ tab
- Click on the Text option
- Click on the ‘Header & Footer’ option. This will display the header and footer area in your worksheet and place the cursor within it
- 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)
- Enter the text &[Tab]
- Click anywhere outside of the header area
You’ll notice that the sheet name has now been added to 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]
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:
- How to Center the Worksheet Horizontally on the Page in Excel (3 Easy Ways)
- How To Go to the Top of the Page in Excel (Shortcuts)
- How to Insert Chart Title in Excel? 2 Easy Ways
- How to Insert an Excel file into MS Word (3 Easy Ways)
- How to Insert Square Root Symbol in Excel (5 Easy Ways)
- How to Show Ruler in Excel? (Ruler Grayed Out)