Headers and Footers

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.

Setting up a spreadsheet for printing

A random fact: if you printed out an entire Excel worksheet (i.e. just one tab) without adjusting the width of the columns or the height of the rows, you’d need 76,355 reams of A4 paper.  Stacked, that’s nearly 4 kilometres tall!

What does this tell us? That we need to be very careful when printing from Excel or we may get more than we bargained for! Luckily Excel has some very good controls when it comes to printing, but it does mean you have to be on the ball before lackadaisically hitting Print.

Using Print Preview

Go to the File menu (or, for Office 2007 users, the Office button  ) and choose Print, or use the keyboard shortcut Ctrl+P.

This will bring up your Print Preview, where you can scroll through the different pages and see how Excel is laying out your spreadsheet.

Things to consider:

• Size – could you make your spreadsheet smaller to fit on one page (without compromising readability) or even slightly larger for a better visual effect?
• Margins – could they be smaller or bigger?
• Headers and footers – would it help to have these on your print-out?
• Gridlines – would your print-out benefit from having the gridlines on it?
• Orientation – portrait or landscape?
• How much – do you really need to print out everything, or is it better to print out a portion?
• Having rows repeat at the top of every print-out – would this make your print-out easier to read?

Once you’ve had a ponder alongside your preview, it’s time to go back to the spreadsheet and sort things out. This is done by clicking the arrow in the top left-hand corner of your screen, or by pressing Esc on the keyboard.

Whenever you do anything to do with printing, Excel will place dotted lines across your worksheet; these show how much will fit on each piece of paper and are called ‘page breaks’. You can’t get rid of them unless you close and reopen your spreadsheet – but to be honest, they’re not that intrusive so you just get used to them.

Using the print and view options

In older versions of Excel, print settings were scattered hither and thither throughout the program – now, they’re at least a little bit better gathered together, but there are still a few bits in different places, so we’ll start off by seeing what they are.

Moving between the views

Click on the View tab. This has three ‘views’ (ways the spreadsheet is displayed) which are important to printing out:

1. Normal: the basic way your spreadsheets will look.
2. Page Break Preview: a zoomed-out view which indicates where new pages will start.
3. Page Layout: a view which allows you to see your spreadsheet with ‘paper’ behind, complete with the edges of the paper and the header/footer areas.

This is what they all look like. You can click on them and get larger versions to see.

1. Normal, showing the dotted lines to show the page breaks (you can only see the vertical one here)

2. Page Break Preview, zoomed out and showing blue lines instead of dotted lines for page breaks

3. Page Layout, showing the spreadsheet more like you’d see in Word, so you see the whole of the paper complete with Header and Footer areas.

They all look quite different, don’t they? They all behave exactly like normal spreadsheets, though – you can sort, make formulae, change the formatting and so on. Some people prefer one over the other; me, I will flip to Page Break Preview for certain tasks, but once I’m done, I’ll always return to Normal view. I really don’t get along with the Page Layout view, although I stress that’s just my personal preference.

The key thing here is knowing that if you open a spreadsheet created by someone else and they’ve left it in a view you don’t get on with – change it to what you do prefer!

The Page Layout tab

Not only do you have the View tab with its three views to change between, but you also have the Page Layout tab which gives you a wide range of choices for controlling printed output. Click on it and have a look at what it offers.

• Margins, Orientation and Size are fairly obvious and easy to understand.
• Print Area allows you to set a specific area to print out. (Practically, this is one of those things that really annoys users who don’t realise a Print Area has been set – and if you want to print out some titles, miss a bit, then print out something below, you’ll end up with your titles by themselves on one page and everything else on a separate piece of paper, unless you remember to Hide Rows.)
• Breaks allows you to put in a manual page break, i.e. point at which it will move to printing out on the next page. (Practically, it only allows you to set horizontal breaks – not much use if you want to do vertical breaks! I always use Page Break Preview for this, to be honest, rather than the Breaks button.)
• Background allows you to have an image behind your spreadsheet. (Practically, it’s virtually impossible to get the balance between an image that’s pale enough not to interfere with your data and dark enough to actually read!)
• Print Titles is really handy, as it allows you to print your header row(s) or even certain columns onto every print-out.
• Width and Height are to specify if you want a certain number of rows or columns to fit perfectly. I was just playing around with it and it shrank my spreadsheet to 11% (see Scale, below) – completely unreadable! So steady on with this one!
• Scale will shrink or enlarge your spreadsheet to fit onto one piece of paper – so if you have a single column which falls off onto another page then you can set the scale downwards (say, to 80%).
• Gridlines/Headings: view/print – if you take the tick out of your View box on these, you’ll see the gridlines and/or the bit with your column letters and row numbers disappear. Working without these can be a bit weird, so pop the ticks back in. Now, if you place a tick in the bottom two boxes (for Print), you’ll have the gridlines actually print out (handy!) and/or the column letters and row numbers too (unusual, but you never know). It’s worth doing the Gridlines tick on pretty much most printed spreadsheets, because adding borders manually actually can add a huge amount of bulk to your file size, which can make it slow to open, edit or make other changes.

Print Preview

Many of these options can be accessed from Print Preview as well.

Above, you can see you can change orientation from Portrait to Landscape, change the paper size, change the margins and change the scale. You can also click Page Setup right at the bottom, where you’ll access a dialog box with four tabs:

• The first tab, Page, deals with the orientation, paper size and scaling.
• The second tab, Margins, allows you to set specific margins, as well as to choose to have the printed spreadsheet centred on the page vertically or horizontally (it can look quite crisp, doing this).
• The third tab, Header/Footer, is where you can apply your headers and footers.
• The final tab, Sheet, deals with printing the same rows at the top of every page, printing gridlines, row/column headings, printing comments and printing errors.

As you can see, all of these options (bar printing comments/errors) are available from the Page Layout tab as well – with one important proviso: printing the same rows at the top of every page (see below for how) is NOT available via Print Preview.

Printing rows at the top of every printed-out page

Firstly, then, decide if you want to have certain rows appear at the top of every print-out. In this example, Row 1 has the headings Date, Place, and Country/Area. I have 447 rows, so it would be really useful to have Row 1 appear on every page. This is called Print Titles. From your Page Layout tab, click Print Titles.

This will bring up the Page Setup dialog box. Click in Rows to repeat at top.

Now, when you move your cursor over the worksheet, you will see a small black right-facing arrow. Click once for just Row 1, or click and drag over however many rows you need to have repeated at the top of each page.

Your Page Setup box will then say something like $1:$1 (for Row 1) or $1:$4 (for Rows 1 to 4). Click OK.

You can do the same for columns in the panel below as well, if you need.

You won’t see anything happen to your spreadsheet unless you’re in Page Layout view or in Print Preview. Have a look at Print Preview (because Page Layout view will remove your Freeze Panes) and scroll through to see this. Return to the spreadsheet when you’re done.

Note: you cannot access this from Print Preview, as of course you can’t click on the rows or columns.

Adding Headers or Footers

Your spreadsheet has three areas for a header, and three areas for a footer. There are essentially two ways of accessing it, both with two ways to find – I explain each one and its tools in detail over here.

Getting a good fit onto one page

I tend to go about this in a ham-fisted, messy way – so I’m hoping you’ll learn from my mistakes on this one! For instance, I’ll play around with the scale and page breaks, then realise I want to have a repeating header row on every print-out. Or, I’ll make all the margins crazy small, then decide I want a header or footer. All of this messes up my settings and I have to start again!

Change the orientation

Decide if you think your spreadsheet would work better as Portrait or Landscape .

Either click on Page Layout, Orientation, and set as preferred, keeping an eye on how the dotted lines indicating your page breaks are behaving, OR change this setting via Print Preview.

Check your column widths and row heights

Have a look at your columns – are they all fitting their contents perfectly? For instance, here I could probably get away with narrowing column A.

Now I’ve used Autofit on column A (by double-clicking on the boundary between A and B), you can now see that my dotted line to indicate the edge of the paper has jumped from being between B and C (meaning Country/Area would have fallen onto a new page) to being between C and D (meaning all three columns will print on the same sheet).

Shrinking or enlarging to fit better

It might be that you just have one skinny column at the end that just won’t seem to join the rest of your columns on the one page. You have a choice – you can either tweak the margins or change the scale.

Changing the Margins

This is best done in Print Preview. Look in the very bottom right-hand corner, just above your date and time – you’ll see a couple of small buttons. The one on the left allows you to see the margins on the preview. (The other one zooms out your preview and shows the whole page at once.)

Now you’ll see lines which go top-to-bottom and left-to-right – these show your margins. The pair at the top show the header area, and ditto at the bottom for the footer area.

You can either move your mouse over these vertical or horizontal lines, and drag up/down or left/right as required – this sets them manually – or you can use some of the presets from the Margins option on the left of your preview. I often use Narrow margins.

NOTE OF CAUTION: You can also change the width of your actual columns from here too. See the small black tags at the top of the preview that don’t join up from top to bottom? These are your column width adjusters, and if you change them you will be affecting your actual spreadsheet BUT you CAN’T UNDO if you mess it up! Eek!

Scaling

This can be done by using the Scale percentage button on the Page Layout tab. As you use the up and down buttons, pay attention to where your dotted line is. This will enable you to see exactly when your data fits neatly on one page.

Alternatively, you can use…

Page Break Preview

Switch to View, Page Break Preview. Don’t worry – I know it looks alarming, but it’s really handy to check where the pages start and end if you have different sections in your spreadsheet.

You might have numeric data showing up as a row of #### – don’t adjust, it’s just because it’s zoomed out so you can see the whole page.

Blue dotted lines indicate automatically placed page breaks. Solid blue lines indicate the outer edges of your printed output or manually placed page breaks. In the example here, I want each new year to start on a new page. So I move my cursor over the dotted blue line and drag up until it’s just above the section heading. Once I let go, the line is solid to show it’s manual.

You can use this for adjusting columns as well as rows. Sometimes, I’ve found that the fuzzy line that appears as I’m adjusting the page breaks doesn’t line up with what I want it to – it’s frustrating, but you can work around it if you know what it’s doing.

Don’t forget to return to your Normal view once you’re done – it can be very hard to actually work in this view!

Final touches

Each time I make a change, I have a quick peep at my Print Preview. I can see that it would be handy to have the gridlines printing out as well, so I select Page Layout tab, and put a tick in the Gridlines option.

As you can see from the below, the output is far more readable with the gridlines on.

The only snag is that the lines all add a slight extra bulk, so you may find you’ve now just had one solitary row or two pushed onto a new page – in which case, adjust your margins or page breaks as above. Can you see, for instance, how I have a very tiny page 8 here? In this case, I dragged the dotted line down to match the solid line at the top of page 9.

Printing only a portion of your data

This is known as Print Selection and can be very easy to set up.

1. Start off by highlighting the area you want to have printed.
2. Go to Print Preview.
3. Now, under Settings, click the down arrow next to Print Active Sheets.
4. Choose Print Selection.
5. Check your preview on the right, and if you’re happy with it, click Print.

So that’s a more-or-less exhaustive trawl through all the print settings. As you can see, you have to jump around quite a bit to get it to do what you want – but getting a print-out correct, first time, without wasting sheets and sheets of paper, is well worth it indeed.

If you’re following the suggested Learning Plan, please click here to go to the next post.