Excel Rows to repeat at top

When your Excel spreadsheet spans several pages, the data is easier to follow when you print the column titles at the top of every page. Instead of manually repeating the title row, you can command Excel to automatically repeat the row on every page using the Print Titles feature. Excel also includes features that make it easier to repeat rows when you're entering duplicate data into the spreadsheet.

Title Rows

1. Click the "Page Layout" tab, and select "Print Titles" from the Page Setup section of the ribbon.

2. Click inside the box next to "Rows to Repeat at Top" in the Page Setup dialog box.

3. Click in the worksheet on the row you want to repeat. The cell values appear automatically in the dialog box.

4. Click "OK" to save the change and close the dialog box. The row you selected appears at the top of every page when you print the worksheet.

Duplicate Rows

1. Select the row or rows you want to repeat.

2. Move your pointer over the small square fill handle in the bottom left corner of the selection area so that the pointer turns into a black "+" sign.

3. Drag the fill handle down over the rows into which you copy the original data, and then release the mouse button. Excel automatically fills the rows with the repeated data. If you already have data in the other rows that you don't want to copy over, use the next method instead.

4. Select the row or rows you want to repeat. Right-click the selection and click "Copy."

5. Select the rows into which you want to copy the original row or rows. Right-click the selection, and then click "Insert Copied Cells." Excel inserts the repeated data into the new rows, moving the existing rows down.

When you work with data in Excel, there is a handy feature that allows you to freeze the top row and header columns (or even lock multiple top rows or left columns).

So when you scroll down, the headers are always visible.

But that’s not the case when you print your reports.

By default, a printed report would only have the header row at the top of the first printed page, and then rest all the other pages won’t have it.

This can make going through the reports a nightmare. Imagine being on the third page and not knowing what a data point represents (and the back and forth to check the headers can be maddening).

The solution – have the headers repeat on every printed page of the report.

In this tutorial, I will show you how to print the top row on every page in Excel. You can also configure it so that multiple top rows or left columns repeat on every page.

Let’s see how to do this!

• How to Print the Top Row on Every Page in Excel
• Print the Rows on Every Page Using the NameBox Trick
• How to Repeat Header Rows on Every Page Except the Last Few Pages?

How to Print the Top Row on Every Page in Excel

Suppose you have a dataset as shown below.

If you print this data, it would take up multiple pages, but the heading will only appear on the first page.

Below are the steps to make sure that the header row repeats on every page that is printed:

1. Click the ‘Page Layout’ tab
   
2. In the ‘Page Setup’ group, click on the dialog box launcher (the small tilted arrow as shown below)
   
3. In the ‘Page Setup’ dialog box, click on the ‘Sheet’ tab
   
4. Click on the field next to the ‘Rows to repeat at top’ option
   
5. Select the top row (you will notice that $1:$1 is automatically inserted in the “Rows to repeat at the top” field.
   
6. Click OK.

Now, when you print this data, you will notice that the top row header repeats on every page that is printed.

You can check this by Clicking on the ‘File’ tab and then clicking on the ‘Print’ option. This will open the Print Preview pane.

Click on the arrow icons at the bottom of the Print preview pane, and you should see the headers repeat on each of the pages.

Just like we have configured the settings to print the top row on every page, you can also set it to print multiple header rows on every page.

To do this, in Step 5 of the above steps, instead of selecting the top row, select multiple top rows that you want to repeat on every printed page.

Note that you need to select contiguous rows (i.e., you can set row number 1, 2, and 3 to be printed on every page, but you cannot set row number 1 and 3 to be repeated on every printed page)

You can also set the left-most column (or multiple left-most columns) to repeat on every page when printed. The process is exactly the same, where, in Step 5, instead of choosing the row, you can select the column that you want to repeat.

And of course, you can also set the top row and the top column to repeat on every printed page.

Print the Rows on Every Page Using the NameBox Trick

Now, let me also share an amazing Excel trick that not many people know about.

The benefit of using the above method (where we use the Page Setup dialog box) is that it gives you a lot more options when you’re printing your reports.

But if all you want to do is make sure that the top through or the leftmost column repeats on every printed page, this NameBox trick is a lot faster.

Suppose you have a dataset as shown below.

Below are the steps to make sure the headers in printed on every page

1. Select the header row
   
2. Click on the ‘NameBox’ field
   
3. Manually enter the text Print_Titles
   
4. Hit the enter key

That’s it! Now when you print the dataset, the first row would repeat on every page.

In case you want to repeat multiple header rows or columns, select those first and then name these as Print_Titles

Why this Works?

When you use the Page Setup dialog box to set the rows and columns that should be repeated, Excel automatically creates a Named Range with the name Print_Titles.

So, instead of going the Page Setup dialog box route, if you create the same Named Range yourself, that would work too.

How to Repeat Header Rows on Every Page Except the Last Few Pages?

Unfortunately, there is no way in-built way to make sure that the headers print on every page except the last page (or the last few pages).

One workaround could be to have the pages, where you do not want the headers to repeat, so be in a separate worksheet.

But this may not be ideal for everyone.

Below is the VBA macro code that will do this.

Sub RepeatHeadersPrintExceptLastPage()

Dim TotalPages As Long

TotalPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"

ActiveSheet.PrintOut From:=1, To:=TotalPages - 1
.PrintTitleRows = ""

ActiveSheet.PrintOut From:=TotalPages, To:=TotalPages

End With
End Sub

The above code uses Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)”) to get the total number of pages in the dataset that will be printed.

It then prints all the pages (except the last one), and during this time, the PrintTitleRows is set to $1:$1. So the first row will be printed as a header on all pages except the last one.

Then the last page is printed where the PrintTitleRows property is set to null, so no header rows are printed on the last page.

This is a clumsy workaround, but if all you want to do is print all the data in the worksheet so that the header repeats on every page except the last page, this would work.

You might have to modify the code a little bit in case you want more headers to repeat or also want the column headers to be printed on each page.

I hope you found this tutorial useful!

Other Excel tutorials you may like:

• How to Print Excel Sheet on One Page (Fit to One Page)
• How to Print Multiple Sheets (or All Sheets) in Excel in One Go
• How to Print Comments in Excel (Step-by-Step Guide)
• How to Set the Print Area in Excel Worksheets

How do I make rows repeat at top in Excel?

Why can't I set rows to repeat at top in Excel?

How do I repeat rows at the top and bottom in Excel?

How do you keep top two rows always on top when scrolling?