Link an Excel Cell Value to a Header or Footer
Question details
Display the value of an Excel cell in a worksheet header or footer and create a similar dynamic header in Word.
- Product
- Microsoft Excel and Microsoft Word
- Device & OS
- not provided
- Scenario
- A worksheet header should reflect cell A1 as its value changes, and a Word header should display changing source text.
- Observed behavior
- The desired result is a header or footer that reflects its source value rather than requiring manual retyping.
Identify the worksheet name and source cell, and check that you are using a desktop version of Excel that supports VBA. For Word, decide whether the header should repeat text from the document or a value stored elsewhere.
Use Excel VBA to copy cell A1 into a header
Use workbook events to refresh the header when the source sheet changes or recalculates and before printing.
Excel does not offer a standard header formula that directly references a cell. This example reads the displayed text from A1 on Sheet1 and writes it to that sheet's center header.
The code overwrites the center header. Keep the source text short enough for Excel's header limit, and widen column A if the displayed value appears as number signs.
In desktop Excel, open the workbook and select Developer > Visual Basic. In the Project pane, double-click ThisWorkbook.
Paste this code into ThisWorkbook, replacing Sheet1 with your worksheet's name if needed: Private Sub UpdateHeader() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ws.PageSetup.CenterHeader = Replace(ws.Range("A1").Text, "&", "&&") End Sub Private Sub Workbook_Open() UpdateHeader End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = "Sheet1" Then UpdateHeader End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) If Sh.Name = "Sheet1" Then UpdateHeader End Sub Private Sub Workbook_BeforePrint(Cancel As Boolean) UpdateHeader End Sub
Select File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros only if you trust the workbook, change A1, and check File > Print to see the updated header.
To write to the center footer, return to ThisWorkbook in the VBA editor and replace CenterHeader with CenterFooter in the UpdateHeader procedure.

Use a Word StyleRef field for a dynamic header
Use this method when the source text is in the Word document itself.
Edit spreadsheet headers with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for editing spreadsheets and documents. Open compatible Office files and continue working across formats; it does not fix Excel's VBA behavior.

Frequently Asked Questions
Can I type a cell formula directly into an Excel header?
No. Excel headers and footers do not evaluate standard cell-reference formulas. Use VBA to copy the value into a PageSetup header or footer property.
How do I put the value in the left or right header?
In the VBA procedure, replace CenterHeader with LeftHeader or RightHeader. Use LeftFooter, CenterFooter, or RightFooter for a footer.
Will the Excel header update if macros are disabled?
No. The VBA event procedures must run to refresh the header. Save the file as .xlsm and enable macros only when you trust the workbook.
Why does the header show number signs instead of the cell value?
The example uses the cell's displayed text. Widen column A or change the cell format so A1 displays the intended value, then edit the sheet or print again to refresh the header.
Can Word's StyleRef field read an Excel cell directly?
No. Insert > Quick Parts > Field > StyleRef references styled text in the Word document. Reading an Excel cell requires a separate link or VBA approach.




