Thank you Col Delane.
Actually I went with a 2 line footer, putting the Tab name and Date on the topmost line and the File and Page n of n on the second line. The File name is just way too long, in my case.
I did find some other useful header/footer formatting codes here, which I pass along for those stumbling across this thread:
https://msdn.microsoft.com/en-us/library/office/ff822794.aspx?f=255&MSPPError=-2147217396
Thanks again to everyone. I am sure I will be visiting Allen Wyatt's site more often in the future.
Rick
Loading
ExcelTips (Menu Interface)
This Site
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Subscribe
Helpful Links
ExcelTips FAQ
ExcelTips Resources
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
Positioning Headers and Footers
Besides allowing you to control what appears in a header or footer, Excel allows you to also control the distance at which they appear relative to the top or bottom of the paper on which you print your spreadsheet. To position the header or footer, follow these steps:
- Select Page Setup from the File menu. This displays the Page Setup dialog box.
- Make sure the Margins tab is selected. (See Figure 1.)
- Use the Header and Footer controls to specify the distance from the edge of the paper at which the header or footer should appear.
- Click on OK.
Figure 1. The Margins tab of the Page Setup dialog box.
Make sure that when you specify a position for the header or footer that the value is smaller than what you have set for top or bottom margins. If the Header or Footer values are larger than the margins, your header or footer could overlap with data printed on the page.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2636) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Related Tips:
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!
Leave your own comment:
Comments for this tip:
Rick Powell 09 May 2016, 08:02
Col Delane 09 May 2016, 00:06
@Rick
The best way to overcome your problem is to append the tab name to the filename within the left footer, viz;
&[Path]&[File] - &[Tab]
(Remove &[Path] if you only want filename & tab name)
I think the reason that &[Tab] followed by a Carriage Return causes the sheet name to appear 1 line above the rest of the footer info is that the footer margin determines where a last/bottom line of footer will print, so any additional lines in the footer must (by definition) push further into the print area. It's a little counter-intuitive: so if a long left footer (e.g. path-filename-tab) overlaps the centre footer (e.g. Page n of n), you need a carriage return AFTER the field codes of the chosen footer to push that footer above the other.
The best way to overcome your problem is to append the tab name to the filename within the left footer, viz;
&[Path]&[File] - &[Tab]
(Remove &[Path] if you only want filename & tab name)
I think the reason that &[Tab] followed by a Carriage Return causes the sheet name to appear 1 line above the rest of the footer info is that the footer margin determines where a last/bottom line of footer will print, so any additional lines in the footer must (by definition) push further into the print area. It's a little counter-intuitive: so if a long left footer (e.g. path-filename-tab) overlaps the centre footer (e.g. Page n of n), you need a carriage return AFTER the field codes of the chosen footer to push that footer above the other.
Rick Powell 20 Apr 2016, 11:01
I am using Excel 2010.
Is there a way to control horizontal alignment of each of the 3 sections (left, center, right) when you use the Header/Footer tab of the Page Setup dialog box - after clicking the Custom Footer... button?
For instance, I want to heve the Left section contain &[File] (which is by default left-aligned), and the Right section to contain &[Page] (which is by default right-aligned), and the Center section always appears to be center-aligned. I would like to have &[Tab] in the Center section, but right-align it.
The real problem I am trying to solve is with a LONG Filename that extends very far into the Center section of the footer. The best solution I have come up with so far is to define the Center section to contain: &[Tab] followed by a Carriage Return, which causes the actual sheet name to appear 1 line above the rest of the footer info.
Many thanks for all the awesome stuff you have on your website!
Rick
Is there a way to control horizontal alignment of each of the 3 sections (left, center, right) when you use the Header/Footer tab of the Page Setup dialog box - after clicking the Custom Footer... button?
For instance, I want to heve the Left section contain &[File] (which is by default left-aligned), and the Right section to contain &[Page] (which is by default right-aligned), and the Center section always appears to be center-aligned. I would like to have &[Tab] in the Center section, but right-align it.
The real problem I am trying to solve is with a LONG Filename that extends very far into the Center section of the footer. The best solution I have come up with so far is to define the Center section to contain: &[Tab] followed by a Carriage Return, which causes the actual sheet name to appear 1 line above the rest of the footer info.
Many thanks for all the awesome stuff you have on your website!
Rick
JurgenG 28 Apr 2015, 04:27
Hi,
so I understand it correctly that it's impossible to have a full-page watermark on the page?
Currently, the best I can achieve is to have it span top to bottom (header and footer margins to 0), but can't get it to align to the left page border.
so I understand it correctly that it's impossible to have a full-page watermark on the page?
Currently, the best I can achieve is to have it span top to bottom (header and footer margins to 0), but can't get it to align to the left page border.