Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Find and Replace in Headers.
Written by Allen Wyatt (last updated May 11, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
One of the very useful tools provided in Excel is Find and Replace, which allows you to locate and change information stored in cells. One place that Find and Replace won't work, however, is with information stored in headers or footers for your worksheets.
The only way to handle the finding and replacing of information in a header or footer is to use a macro. It is a rather trivial task to access what is stored in the various parts of the header and footer, check them for what you want to find, and then replace it with some new text. The following macro provides an example.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Sub FnR_HF()
Dim sWhat As String, sReplacment As String
Const csTITLE As String = "Find and Replace"
sWhat = InputBox("Replace what", csTITLE)
If Len(sWhat) = 0 Then Exit Sub
sReplacment = InputBox("With what", csTITLE)
With ActiveSheet.PageSetup
' Substitute Header/Footer values
.LeftHeader = Application.WorksheetFunction.Substitute( _
.LeftHeader, sWhat, sReplacment)
.CenterHeader = Application.WorksheetFunction.Substitute( _
.CenterHeader, sWhat, sReplacment)
.RightHeader = Application.WorksheetFunction.Substitute( _
.RightHeader, sWhat, sReplacment)
.LeftFooter = Application.WorksheetFunction.Substitute( _
.LeftFooter, sWhat, sReplacment)
.CenterFooter = Application.WorksheetFunction.Substitute( _
.CenterFooter, sWhat, sReplacment)
.RightFooter = Application.WorksheetFunction.Substitute( _
.RightFooter, sWhat, sReplacment)
End With
End Sub
Note how the macro does the replacements in all three parts of the header and all three parts of the footer.
If you prefer to not use your own macro, or if you want a more full-featured Find and Replace for Excel, you might consider the free FlexFind add-in from Excel MVP Jan Karel Pieterse:
https://jkp-ads.com/excel-flexfind.asp
This add in searches regularly, but also searches in lots of other areas including headers and footers.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3388) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Find and Replace in Headers.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
When you use Find and Replace, Excel normally looks through all the cells in a worksheet. You may want to limit the ...
Discover MoreExcel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or ...
Discover MoreNeed to add some characters to the beginning of the contents in a range of cells? It's not as easy as you might hope, but ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-07 20:18:19
J. Woolley
My Excel Toolbox includes the following function to return a worksheet's page setup properties, including headers and footers:
    =ListPageSetup()
Expect 2 columns and 38, 44, or 50 rows depending on the presence of different first page and/or even page headers and footers.
See https://sites.google.com/view/MyExcelToolbox/
2024-05-24 12:23:25
J. Woolley
Excel's WorksheetFunction SUBSTITUTE is case-sensitive. Here is a version of the Tip's macro that permits the Find text to be case-insensitive. And it uses StrPtr(...) to recognize the difference between null replacement text and the Cancel button.
Sub FnR_HF2()
    Const csTITLE As String = "Find/Replace Header/Footer"
    Dim sWhat As String, sWith As String, msg As String, typ As Integer
    msg = "Find what in header and footer:"
    sWhat = InputBox(msg, csTITLE)
    If sWhat = "" Then Exit Sub
    typ = IIf(MsgBox("Match case?", vbYesNo, csTITLE) = vbYes, _
        vbBinaryCompare, vbTextCompare)
    msg = "Find (" & IIf(typ = vbBinaryCompare, "match", "ignore") _
        & " case):" & vbLf & sWhat & vbLf & vbLf & "Replace with:"
    sWith = InputBox(msg, csTITLE)
    If StrPtr(sWith) = 0 Then Exit Sub 'user clicked Cancel
    With ActiveSheet.PageSetup
        .LeftHeader = Replace(.LeftHeader, sWhat, sWith, , , typ)
        .CenterHeader = Replace(.CenterHeader, sWhat, sWith, , , typ)
        .RightHeader = Replace(.RightHeader, sWhat, sWith, , , typ)
        .LeftFooter = Replace(.LeftFooter, sWhat, sWith, , , typ)
        .CenterFooter = Replace(.CenterFooter, sWhat, sWith, , , typ)
        .RightFooter = Replace(.RightFooter, sWhat, sWith, , , typ)
    End With
End Sub
Notice both versions ignore different first page and even page headers and footers.
2024-05-11 12:19:25
J. Woolley
Although having nothing to do with headers or footers, the ReplaceShapeText macro in My Excel Toolbox will find and offer to replace specified text in each shape on the active sheet or on all sheets of the active workbook. It includes logic to search grouped shapes plus options to match alphabetic case, whole words, and hidden shapes (such as Comments). Text formatting is preserved. This macro does not apply to chart labels but does apply to text boxes or other shapes added to charts. A log of the results can be copied to the clipboard then pasted into a worksheet or other document.
See https://sites.google.com/view/MyExcelToolbox/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments