3 Ways to Repeat Headings on Excel Sheets Easily

When working with Microsoft Excel, especially if you're dealing with large datasets or long reports, one might find it beneficial to have column or row headers appear consistently across multiple pages when printed. This practice enhances the readability and interpretation of complex spreadsheets. Below, we'll explore three methods to repeat headings on every page of your Excel document:

1. Using the Page Setup Dialog

• Step 1: Open your Excel workbook.
• Step 2: Click the worksheet you want to format.
• Step 3: Navigate to the “Page Layout” tab.
• Step 4: Click on “Print Titles” in the “Page Setup” group.
• Step 5: In the Page Setup dialog, go to the “Sheet” tab.
• Step 6: In the “Rows to repeat at top” section, click the selection icon (a small red arrow) to the right.
• Step 7: Click on the row numbers you want to repeat at the top. For example, if row 1 contains your headers, select row 1.
• Step 8: Confirm the selection by clicking the same selection icon, which now points up, and then click “OK.”

🔔 Note: This option can also be used to repeat columns on the left side. Just enter the column letters or click in the "Columns to repeat at left" field.

2. Using Excel’s Defined Names

• Step 1: In Excel, navigate to the worksheet you are working on.
• Step 2: Select the rows or columns you want to repeat.
• Step 3: Go to the “Formulas” tab.
• Step 4: Click on “Define Name” in the “Defined Names” group.
• Step 5: In the “New Name” dialog, type a name for your range (e.g., “Headings”).
• Step 6: Set the “Refers to” field to the range of cells you have selected.
• Step 7: Click “OK” to save the defined name.
• Step 8: Repeat the process from the first method, but instead of clicking the small arrow, manually type the name you defined (e.g., “Headings”) in the “Rows to repeat at top” field.

🔔 Note: Using defined names can be helpful for organizing and maintaining complex spreadsheets since you can quickly refer to the named range throughout your workbook.

3. Implementing VBA Macro for Repeating Headers

Creating a VBA (Visual Basic for Applications) macro is a more advanced approach but offers automation and flexibility:

• Step 1: Open your Excel workbook.
• Step 2: Press ALT + F11 to open the VBA Editor.
• Step 3: In the VBA Editor, insert a new module:
  • Right-click on any open project in the left panel.
  • Select “Insert” > “Module.”
• Step 4: Copy and paste the following VBA code into the new module:

Sub SetPrintTitles()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws.PageSetup
        .PrintTitleRows = “1:1”
    End With
    MsgBox “The headers will now repeat on every printed page.”, vbInformation
End Sub

• Step 5: Close the VBA Editor.
• Step 6: To run the macro:
  • Go to “Developer” tab (if not visible, go to File > Options > Customize Ribbon and check Developer).
  • Click “Macros.”
  • Select “SetPrintTitles” and click “Run.”

🔔 Note: This macro will set the print titles for the active sheet. Adjust the "$1:$1" in the code to the appropriate range if you need to repeat different rows.

In conclusion, having headers repeat on each page of your Excel sheets not only aids in readability but also ensures data continuity for extended printouts or when sharing workbooks. By choosing one of these methods, you cater to your specific needs for document organization:

• The Page Setup dialog is straightforward, perfect for one-off setups.
• Using defined names offers a streamlined approach for complex spreadsheets.
• VBA macros provide automation for repetitive tasks across multiple sheets.

Can I repeat headers both horizontally and vertically?

+

Yes, in the “Page Setup” dialog, you can use the “Rows to repeat at top” and “Columns to repeat at left” fields to repeat both row and column headers.

What if I have different headers on different sheets?

+

Each sheet in Excel can have different print settings. Apply the methods mentioned to each sheet individually.

How do I remove headers from repeating?

+

Go back to the Page Setup dialog or your VBA macro and clear or delete the entries for “Rows to repeat at top” and “Columns to repeat at left.”

Can I save these settings with the workbook?

+

Yes, once you set the repeat headers, the settings will be saved with the workbook, and the next time you open it, the headers will still repeat.

Is there a way to repeat headers when using Excel online?

+

Excel Online (Web version) currently does not support setting headers to repeat on printed pages, but you can prepare the file in the desktop version and then use online.