Learn how to print the top row on each page in Excel in easy steps.

Imagine you’re organizing a big meeting, where each team member has a different agenda to follow. Now, picture that every time someone turns a page in their notes, they lose track of the main agenda items.

That’s what can happen in Excel when you’re printing a large worksheet without keeping the header row visible on every page. This skill can seem tricky at first, but with the right guidance, it’s easier than you think.

In this tutorial, I’ll show different approaches for printing the top row on each page which you can implement in your worksheets so your readers never lose sight of important information. Keep reading and follow along with the methods as you progress through the tutorial.

Using the Page Setup Tool

Open the source worksheet and go to the Page Layout tab.

Click on the tiny down arrow icon in the bottom right corner of the Page Steup block.

The Page Setup dialog box will open.

Now, go to the Sheet tab and click on the Rows to repeat at top field.

Select the top row you want to print from the active worksheet. A cell range address, like $1:$1 will show up inside the Rows to repeat at top field.

Click OK to apply.

Now, check the print preview for the worksheet to be printed, scroll down, and you should see that Excel is set to print the selected row on the top of all the pages.

Using a Defined Name Range

You can convert the top row into a named range. That should also allow you to print it on all the pages of the worksheet.

To try this method, click on the Define Name command inside the Defined Names block of the Formulas tab.

The New Name dialog box will open.

Click on the Name field and enter Print_Titles.

Change the Scope field to Workbook or Worksheet, whichever you prefer.

Choose Workbook if there is more than one worksheet for which you need to print the top row. Also, the top row for all those spreadsheets are same.

Contrarily, select the specific worksheet’s name, like Sheet1, if you only want the trick to work on the selected worksheet.

Click on the Refers to field and select the entire top row in the active worksheet.

Click OK to create the named range.

Using Excel VBA

If you like to automate various tasks on Excel using VAB macros, you’ll like this approach. Here, you can configure a worksheet to print a custom top row on each page with the click of a button. The VBA macro will guide you through visually, so you need not memorize the steps involved in this method. So, it’s more convenient than the previous ones.

Before you can begin using the macro, you must set up one using the VBA script given in this tutorial. If you already know the process, skip to the script below. If you’re new to Excel VBA programming, go through the following tutorial first:

📒 Read More: How To Use The VBA Code You Find Online

Here’s the VBA script that configures the active worksheet to print the top row on each page:

Sub SetTopRowToPrint()
    Dim selectedRow As Range
    Dim ws As Worksheet
    Dim message As String
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Display an input box to select the row to be printed on top of each page
    On Error Resume Next
    Set selectedRow = Application.InputBox("Select the row to be printed at the top of each page:", "Select Top Row", Type:=8)
    On Error GoTo 0
    
    ' Check if the user selected a row
    If selectedRow Is Nothing Then
        MsgBox "No row selected. Operation cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' Ensure the selected range is a single row
    If selectedRow.Rows.Count > 1 Or selectedRow.Columns.Count <> ws.Columns.Count Then
        MsgBox "Please select a single row that spans all columns.", vbExclamation
        Exit Sub
    End If
    
    ' Set the selected row as the print title rows
    ws.PageSetup.PrintTitleRows = selectedRow.Address
    
    ' Confirmation message
    message = "The top row will be printed on each page:" & vbCrLf & selectedRow.Address
    MsgBox message, vbInformation, "Print Settings Updated"
End Sub

When your macro is ready, press the Alt + F8 keys together to launch the Macro dialog box.

Select the SetTopRowToPrint macro and hit Run.

Excel VBA will show a dialog box so you can select the custom top row to be printed.

Once you supply the top row address, either by clicking on the row or by typing in the cell range reference, click OK.

Excel VBA will configure the Page Setup settings accordingly.

You can access the print preview mode to test if the program worked or not.

Using Office Scripts

The newest Excel automation feature, Office Scripts, allows you to remotely perform tasks in a workbook with the help of Power Automate. So, if you often use such remote automation in Excel using Power Automate flows that also involve printing the top rows on all the pages, this should be the approach you must follow.

If you see the Automate tab on your Excel desktop app, proceed with the rest of the instructions.

Go to the Automate tab and click on the New Script button.

The Code Editor navigation panel will open on the right side.

Delete the existing script, if any, and then copy and paste the following code into the Code Editor:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    let sheet = workbook.getActiveWorksheet();

    // Set the top row (row 1) to repeat on each printed page
    sheet.getPageLayout().setPrintTitleRows("$1:$1");

    // Log a confirmation message
    console.log("The top row has been set to print on each page.");
}

Click on the Save script button to save the script for future use or sharing purposes.

Now, hit Run to execute the Office Script automation.

Excel will automatically configure the worksheet to print the top row, which is =Sheet1!$1:$1.

The Excel for the web app doesn’t have the required settings to print the top row on each page in its Page Setup dialog box. There, you can use this Office Scripts code as a workaround.

The above screenshot shows that I’ve successfully configured Excel for the web to print the top row on each page.

📝 Note: Office Scripts is only available if you own a Microsoft 365 Business Standard or better subscription plan. Also, it requires an internet connection since the script is run on the Microsoft cloud infrastructure.

Conclusions

By now, you’ve learned four powerful ways to print the top row on each page in Excel: Page Setup, Defined Name, Excel VBA, and Office Scripts.

Each method offers a unique solution, depending on your needs and level of expertise. Whether you prefer a straightforward option like Page Setup or an advanced approach like Office Scripts, this Microsoft Excel guide has you covered.

Take some time to try these methods in your own projects and see which one works best for you.

If you found this tutorial helpful or have any questions, let me know in the comments—I’d love to hear from you and respond to any questions you might have.