This may be a funny post to have, dedicated just to Headers & Footers, but in fact because Excel allows you to access these from two different directions, both with very different looks and feels, and inserts code when you might not expect it, it’s worth having this all combined into a single place.
Summary of how to get there – Method 1:
- Go to Print Preview and click on Page Setup at the bottom. This will bring up the Page Setup dialog box: click on the Header/Footer tab.
OR - Go to the Page Layout tab and click on the small fly-out to access the Page Setup dialog box. Click on the Header/Footer tab.
Summary of how to get there – Method 2:
- Click on Insert, Header & Footer (way over on the right-hand side) – this will convert your view to Page Layout View, place your cursor in the centre header panel and bring up the Header & Footer tools ribbon.
OR - Click on View, Page Layout where you can see your header area. Click into this area and you will access the Header & Footer tools ribbon.
Which is better?
Which way to go? Page Setup, or Page Layout View? I am loathe to tell you that you must use one way or the other – I don’t think that’s how training should work. Many people prefer Page Setup, because all settings are in one place, because Page Layout view removes any Freeze Panes, and because mentally they separate out the spreadsheet itself from its printed version. Many people prefer Page Layout, because they can actually see the headers in place then and there and the options for inserting date, file name and so on are a little more obvious than via the dialog box. The only thing I note is that people who use the latter tend not to realise that the Header and Footer areas have three separate sections, and because you have to scroll down to see the Footer, they tend not to use the Footer area so much. But now I’ve told you about this, you can have a head start!
I will leave it entirely up to you which you choose. I am not going to tell you one way is better than another way – you decide. Whichever you prefer, get familiar and fluent with it. 🙂
Inserting items into the Header or Footer – Method 1 (dialog box)
Bring up your Page Setup dialog box from either of the two directions shown above.
Make sure you’re on the Header/Footer tab, and click Custom Header…
You will get a new dialog box. You can see the three sections for the top left, top centre and top right of your page.
As you can see, there are some instructions on how to use the dialog box, but it’s fairly obvious anyway.
Note that when you click on most of the buttons, you’ll get code (such as &[Page] ) which you must be careful not to amend in any way. Don’t worry, on the printed version it’ll have the actual thing you want, not the code!
When you’ve added what you need, in either the left, centre or right section, click OK and you’ll see a mini-version of what your header looks like.
Click in Custom Footer… and add a footer too (or instead of), should you require.
Click here to download a PowerPoint file with this animation in, if it doesn’t show up so well here.
Inserting items into the Header or Footer – Method 2:
Go to the Page Layout View from either of the two directions shown above.
Click in any of the three sections at the top – don’t just click where it says Add header, they each work the same!
Click on any of the buttons at the top to add the detail you require. Note that when you click on most of the buttons, you’ll get code (such as &[Page] ) which you must be careful not to amend in any way. Don’t worry, on the printed version it’ll have the actual thing you want, not the code!
If you add text and want to format it, you’ll have to highlight the text then click on the Home tab. Don’t forget to return to the Header & Footer tools tab once you’re done, to continue what you’re doing!
When you click out of the header area onto the spreadsheet itself, you’ll see what your header looks like.
To move to the Footer area, click Go to Footer. Again, you have three areas, left, centre and right, in which you can add a footer.
Click here to download a PowerPoint file with this animation in, if it doesn’t show up so well here.
The different codes and how to use them
To make the most use of the page numbering and the date/time buttons, you can pad them out with explanatory text. For instance, Printed on &[Date] at &[Time] where the codes (in green) are button clicks and the writing (in blue) was typed by me. This will tell you exactly when your spreadsheet was printed out – it will always update! Very handy, especially when you’re working on multiple revisions.
File Path, File Name and Sheet Name
&[Path]&[File] gives you not just the name of your spreadsheet (file) but also where it’s located (path).
&[File] gives you the name of your workbook, or spreadsheet.
&[Tab] gives you the name of your worksheet, or tab (at the bottom, such as Sheet1).
I find &[Path]&[File] a massive help when I’ve got lots of work on the go and I need to refer to a spreadsheet I created some time ago – where did I save it? However, if you use a network drive or shared server, do check your preview before printing. Some of the paths and file names have been so long that they’ve gone along the whole of the width of the page and then onto a new line! And if you have anything set up in either of the other two sections of your header or footer, it will print straight on top of it – literally, the two things lie on top of each other! – which can be really strange. Here, you can just about see the word Page underneath all the other text.
Picture
There’s an option to add a picture, which gives the code &[Picture]. You have to be really careful using this one, as the following two images show.
In this one, I have a nice small image, which fits in the space pretty well. All I need to do is adjust the margin a little bit at the top (via Print Preview) and I’ll be fine.
In this one, I have the original image, before I resized it – yes, it goes behind the actual spreadsheet by some way, and would look awful if it had data on it!
So, the moral of this story is, be prepared to resize your company logo to a size that fits OK – the top one is only about 60 pixels high.
To delete the image, delete the &[Picture] code.
I hope that gives you a good enough overview of the Header & Footer feature in Excel. As always, check your Print Preview before finally hitting the Print button. Think of the environment, or think of your costs… try and save paper!
If you’re following the suggested Learning Plan, please click here to go to the next post.