Written by Allen Wyatt (last updated June 22, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Chris noted that Excel differs from Word in how it handles footers and headers. In Excel Chris can change what appears in the header or footer for left, center, and right. He would like, however, to have a footer that extends the full width of the page (like in Word).
The three sections provided by Excel for headers and footers can be a bit deceiving. It is probably best to think of these sections as doing nothing more than handling alignment of the material within each section. Excel allows you to put quite a bit of information in each section (up to approximately 255 characters), and the contents will "spill" into other areas of the footer or header.
So, for instance, if you put something like 100 characters in the left section of the footer and nothing in either the center or right section, then all 100 characters will "spill" across the page and be left justified. If the characters require more space than is available between the page margins, then Excel will automatically wrap that text to the next line of the footer or header. This same behavior (spilling and wrapping) applies to the other sections of the header and footer, as well.
With this understanding of the sections in the header and footer, you can come close to what can be done in Word with its headers and footers. What you cannot do is what is typically considered word-processing-level formatting, like setting tab stops, using justified paragraphs, or doing some of the fancier formatting possible in Word.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11648) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Today's date is easy to add to a header, but what if you want to add a date that is adjusted in some manner? Adding ...
Discover MoreWhen you have a worksheet that includes a long list of names, you may want the first and last names on each page to ...
Discover MoreDo you find that there is a lot of extra space around the data on your worksheet when it is printed? Changing the margins ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-07-02 12:23:46
Note that if you put the header or footer text in the center section, it will spill over on both sides, thus centering on the page.
2024-06-23 11:13:15
J. Woolley
My Excel Toolbox includes the following function to put Text in a worksheet's headers or footers, which makes them dynamic instead of static:
=SetHeaderFooter(H_F, L_C_R, Text, [FirstPage], [EvenPages], [Target])
As an example, suppose you have a dynamically calculated total in cell E9. The following formula will put that result in the center header of the first page (only) for the current worksheet:
=SetHeaderFooter("head", "center", "Current total is "&E9, TRUE)
For this example to be successful it is necessary to manually enable the Different First Page setting in the Header/Footer tab of the Page Layout dialog (Alt+P+SP). For a complete description of this function, see
https://excelribbon.tips.net/T000559_Using_a_Formula_in_a_Footer.html
My Excel Toolbox also includes the following function to return a worksheet's page setup properties, including headers and footers:
=ListPageSetup()
Expect 2 columns and 38, 44, or 50 rows depending on the presence of different first page and/or even page headers and footers.
See https://sites.google.com/view/MyExcelToolbox/
2024-06-22 09:02:58
Tomek
The limit of approximately 255 characters applies to the total number of characters in all three sections of the header or footer, not for each section individually.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments