Software

Quickly add a header or footer to every sheet in an Excel workbook

You don't have to add a header to every Excel worksheet individually when you can group them first.

Adding a header (or footer) to an Excel sheet is a simple task using Excel's Page Layout view. Adding the same header to several or even all of the sheets in the workbook could mean a lot of repetitive work, unless you group the sheets first.

The first step is to select all the sheets-an easy trick for most Excel users:

  1. Right-click a sheet tab.
  2. Choose Select All Sheets from the resulting shortcut menu.

After grouping the sheets, you're ready to add the header data. To do so, click the Page Layout button in the bottom-right corner to switch to Page Layout view. In Excel 2003, choose Header and Footer from the View menu. Page Layout is new to 2007.

Now you're ready to add the header data:

  1. Click the Click To Add Header prompt and enter your header text. The layout view supports the left, center, and right sections that you're probably familiar with from earlier versions.
  2. Click the left section and using the contextual Design tab, click Picture in the Header & Footer Elements group. Locate the picture file and click Insert. Excel will display the &Picture code until you move to another section of the header.
  3. To display the sheet name, click Sheet Name in the same group.
  4. You can also enter descriptive text by simply typing it.

When you're done, click any of the sheet tabs to remove the grouping. As you move from sheet to sheet, you'll see that they all have the same header. This technique works with footers too. Headers and footers are visible only in Page Layout view and in Print Preview.

This simple grouping technique can save you a lot of time for many tasks, not just creating headers and footers. Anything you need to repeat a task on more than one sheet, grouping might be a great time saver.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
barna11
barna11

thnx a lot...  it helps me much..

spiras
spiras

By the way, if you want to insert a picture into the header, it should be the size of an icon, say, 39 X 39. If you insert a large size picture into the header, it will actually become a background picture of the entire sheet.

gsirmon
gsirmon

This procedure doesn't work in Excel 2003 - at least not in mine. Just checked it to be sure, and it only added the header to the open worksheet. Maybe I'll be upgraded to 2007 sometime in the future! That would be a very helpful capability in my work! Thanks for the article.

Sunny Puddle
Sunny Puddle

Thanks for the tip! I noticed a small spelling error near beginning (right before the first graphic" "Page Payout is new to 2007" - should that be Layout instead of Payout? Thanks for the article.

DonG43
DonG43

Excellent tip and a real time saver. But where is the "Click To Add Header prompt"? I could follow the steps to that, but can't find that prompt and the Excel Help does not mention it.

DBlayney
DBlayney

For example: Turning protection on or off. Unless someone else knows how to do this ...

hougs
hougs

You can resize the picture, crop it, and do other things to it. The icon next to the Insert Picture is Format Picture. Click on it and a dialog box opens. (The icon names may differ from what I write; I am not using an English language version of Excel).

michael_boardman
michael_boardman

It should work in the same way Susan described. I haven't got access to Excel where I am right now, but I'm sure I can remember doing it when I was still at work, several years ago now. The only difference is that (as far as I recall) 2003 has no "Add to header" prompt - you just have to use the dialog boxes.

ssharkins
ssharkins

It's a "link" at the top of the screen. It's circled in one of the figures. If you're not seeing this, you're probably not in the right spot.