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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 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 MoreDon't like the default date format used by Excel when you place the date in a header or footer? You can use a macro to ...
Discover MoreNeed your page numbers to not appear as regular Arabic numerals? Here's a way to get them to appear in a different ...
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