If you work with Word, there is a big change that you worked with headers and footers. But they are available not only in Word. You can also use them in Excel.
The header is located above the content of the page and footer below the content.
Generally, they contain basic information about the company name, company logo, page number or date.
Microsoft included a bunch of built-in headers and footers you can choose, but you can also create your own.
In order to add a header to the Excel worksheet on every page, follow these steps:
- Navigate to Insert >> Text >> Header & Footer. This will switch to the Page Layout
- Inside the Layout window, there is a new area called Header & Footer Tools with the single tab – Design. Click it.
- Inside this tab, there are a few things you can add, such as text, page number, current date, current time, file path, file name, sheet name, picture.
- If you want to leave the header area, click anywhere inside the worksheet. If you don’t want to keep changes press Esc.
Adding a footer to Excel document is very similar to adding a header.
- Click Insert >> Text >> Header & Footer to switch the view to Page Layout.
- In Design >> Navigation, click Go to Footer.
- The button will become grayed out.
- And the cursor will move to the bottom of the page.
- You can type text or add one of the elements from Header & Footer Elements.
Adding a preset header or footer
Excel offers a few built-in headers and footer which you can add to your document with a single mouse click.
- Navigate to Insert >> Text >> Header & Footer. It will switch the document to the Page Layout
- Under Design >> Header & Footer, you have two options: Header and Footer.
- Click one of the buttons to expand the preset list and choose the one you need.
And this is how this preset looks like on the worksheet.
You have to remember that you need to have some information on any of the sheets, otherwise you will have the hash (#) sign, instead of the page number.
What you need to know about presets is that they are dynamic. If you make some modifications inside your worksheet, it will automatically change the header or footer.
Creating a custom header or footer
Instead of using predefined headers or footers, you can create your own. In this custom header we are going to add:
- sheet name
- page number
In order to create custom header follow these steps:
Adding and scaling a picture
- Navigate to Insert >> Text >> Header & Footer.
- Click the header on the left and choose Picture in Header & Footer Elements.
- Navigate to a picture. The best way is to use png with transparency, but if you print on a white sheet of paper, the white background is also
- If you don’t have the exact size of your logo, you can scale it inside Excel. Click inside the header on the left and choose Format Picture.
- Inside the Format Picture Window, set the scale. In my case, it will be 40%. Just set height and if you have lock aspect ratio checked (you should) it will automatically change the width. If you apply different values to height and width the logo will be distorted.
- Click OK to scale the logo.
If you set a scale too, for example, 40% and click OK, but then you change your mind and try to set 35%, first you have to click the Reset button and then set 35%. Otherwise, it will scale the image to 35% not from 100%, but from 40%.
Adding sheet name
We are going to add a sheet name in the middle of the header.
- Navigate to Header & Footer.
- Make sure that you are in the center field of the header.
- Click the Sheet Name.
- Click inside sheet area to see the sheet name in the header.
Adding page number
The last thing left is the page number. Let’s add to the right side of the header.
- Click the right field of the header.
- Navigate to Design >> Heather & Footer Element and choose Page Number.
- Click inside the worksheet area to see the changes.
Insert date to a footer
So far, we added data to the header. Now, let’s add something to a footer. A footer is a great place to add the current date.
- Click inside header and navigate to Design >> Navigation >> Go to Footer.
- After you click this button, you will be automatically switched to the footer.
- Choose the Current Date
- Click inside a worksheet are and check the result.
Formatting text for header or footer
In order to change the text font or color, you just have to click the header area and navigate to Home >> Font. Notice that some features as fill or cell borders are not available here.
Set font color and formatting.
Click inside worksheet to see the changes.
Adding headers or footers to multiple sheets
So far, we had to create headers and footer for each sheet separately. This time we are going to add them to multiple selected sheets at once.
- Add two more sheets: Employees and Warehouse.
- Click the Sales report
- While holding the Ctrl key, click two sheet tabs.
- Navigate to Page Layout, and click the little arrow icon in the bottom right corner.
- A new window will appear. If you click OK, the sheet name, page number and the current date will be added to the Employees and Warehouse
You can also click the Header/Footer tab to see the header and footer preview.
You can click the Custom Header and Custom Footer buttons to edit three elements of these fields: left, center, and right. There is also the list of buttons from the Header & Footer Elements.
Accept changes and click the Employee tab. Change View to Page Layout in the bottom right corner.
If you look at the header and footer, you will notice that they are set in the same way as the first sheet – Sales report. The only difference is that these additional sheets don’t have the logo set. You will have to do it manually.
Header value from a cell
You can’t add cell reference in header or footer directly from Excel, but you can simulate this using the VBA code.
In order to create header value from a cell, follow these steps.
- Enter values in cell A1, B1, and C1.
- Open the VBA editor by using the Alt + F11 keyboard shortcut.
- Inside the Project window double-click Sheet1.
- Insert the following code.
Sub RenameHeader() ActiveSheet.PageSetup.LeftHeader = Range("A1").Value ActiveSheet.PageSetup.CenterHeader = Range("B1").Value ActiveSheet.PageSetup.RightHeader = Range("C1").Value End Sub
- Run the code by clicking the green triangle.
The left, center and right header changed to the value from cells.
If you modify the text, the header won’t change, unless you run the code again.
Closing header or footer
You can close headers in one of two ways.
- Navigate to View >> Workbook Views >> Normal.
- Click the button on the Status Bar.
If you have page breaks on a worksheet, after switching views, you can remove them in File >> Options >> Advanced. There is an area called Display options for this worksheet. One of the options there is called Show page breaks. Uncheck it.
Removing header or footer
You can remove header or footer from a single worksheet or from multiple worksheets.
Removing header or footer from a single sheet
Click each header area: left, center, and right and use the Backspace key to remove the contents.
Removing header or footer from multiple sheets
Do you remember how you added headers and footers to multiple sheets at once? You can also remove them in a similar way.
- While holding the Ctrl button, select additional sheets in which you want to remove header or footer.
- Click the arrow in the bottom-right corner.
- Inside the Page Setup window, choose the Header/Footer.
- After you click OK, all headers and footers will be removed.
Different header or footer on the first page
You may want to have a different header or footer on the first page and the same for the rest. You can do it in Excel, here is how.
- Click inside the header.
- Navigate to Design >> Options and check Different First Page.
Now, if you setup header or footer on the first page, it will be available only for this page. If you create it for the second page, it will be available for the second and the rest of the pages.
Printing headers or footers
There is one important feature you want to check if you printing your document. In order to keep your header and footer in the same scale every time you print your document, you have to uncheck the Scale with document option.
Click inside header and navigate to Design >> Options, where you can find the following option.
Now, if you print your document, your header and footer will always be the same size, it doesn’t matter whether you want to fit data from only one worksheet or multiple worksheets. You have to be careful because this option is checked by default.