Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Putting Cell Contents in Footers.

Putting Cell Contents in Footers

by Allen Wyatt
(last updated January 9, 2017)

4

You may find it helpful to sometime place the contents of a cell into the footer of a worksheet, and to have the footer updated every time the contents of the cell changed. The easiest way to do this is with a macro. The following is an example of a macro that will place the contents of cell A1 into the left side of the footer:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.LeftFooter = Range("A1").Text
End Sub

The macro is run every time Excel does its normal recalculation—meaning every time the contents of any cell changes or someone presses F9. If you want the contents to be in a different part of the footer, you can change LeftFooter to CenterFooter, or RightFooter.

To apply any formatting to the footer other than the default you will need to add special formatting codes, and you can also use special data codes that Excel recognizes for headers and footers. Both the special formatting and special data codes are quite lengthy and have been covered in other issues of ExcelTips.

If you are working with a very large worksheet, then changing the footer every time Excel recalculates may unnecessarily slow down your computer. After all, the footer remains invisible to the user until such time as the worksheet is actually printed. In this case, you simply need to rename the above macro to some other name that you would then manually execute as the last step before printing a worksheet.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2522) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Putting Cell Contents in Footers.

Author Bio

Allen Wyatt

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. ...

MORE FROM ALLEN

Switching between Program Windows

It is not unusual for you to work with several (and sometimes many) programs at the same time. Each program is displayed ...

Discover More

Specifying a Paper Tray in a Macro

If you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a ...

Discover More

Stubborn Highlighting

When you make extensive edits to a document and those edits include changing the formatting of numbered or bulleted ...

Discover More

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!

More ExcelTips (menu)

Turning Headers On and Off

Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off ...

Discover More

Header and Footer Formatting Codes

When creating headers and footers in an Excel worksheet, you can use special codes to add or format information. This tip ...

Discover More

Moving Part of a Footer Down a Line

Setting up a single footer line for your printouts is fairly easy. If you want to move part of the footer down a line so ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 6?

2019-03-20 15:21:52

lisa

How can i get the footer to display column g contents for every page break.. for example column g lists department names and my spreadsheet page breaks after every new department name, i would like for the department name to be in the left footer, thanks


2017-01-23 05:57:05

lodi

Can you tell me how to display the cell A1 of sheet 1 in the header of all sheets?

And how can you show different cells that are not next to each other? Like A1; B3 en D6, for example.

Thanks


2016-11-15 03:21:08

Zak

There is a better way.

There's no need to trigger on selection change. Trigger on before-print instead.

You place this event in the ThisWorkbook module and add the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim oSheet As Worksheet
For Each oSheet In ThisWorkbook.Worksheets
oSheet.PageSetup.LeftFooter = "Test"
Next oSheet
End Sub


2015-10-09 15:18:41

Tara

I am trying to auto fill a center footer with a specific cell every time I use the workbook. For example, cell C3 hold a name and when I type a name in that cell, I want it to auto fill in the footer for the second page. I must be using macros incorrectly. Can you advise?


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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.