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)

3

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.

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

Automatically Inserting Brackets

Want a fast way to add brackets around a selected word? You can use this simple macro to add both brackets in a single step.

Discover More

Merge and Center Not Available

What are you to do if you are trying to format a worksheet, only to find out that one of the tools you need is not available? ...

Discover More

Moving Comment Background Pictures to Cells

When formatting comments, you can use a graphic as a background for the comment box. If you later want to move this graphic ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Header and Footer Background Color

Want to add some color to the printing of your page headers and footers? Your options are limited, as disclosed in this tip.

Discover More

Multiple Line Headers and Footers

When working with headers and footers in a macro, you might find this tip helpful. It describes how you can create headers or ...

Discover More

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
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 8Mpixels. 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 7 - 0?

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.