Working with large datasets in Excel that have a lot of columns may be exhausting. Having to scroll up and down, back and forth to study data can be tiresome since you might quickly forget what each row stands for or signifies.
Fortunately, Excel has an in-built feature that allows you to set a row to print on every page (or set a column to print on every page).
In this lesson, I’ll walk you through the procedures necessary to have the headers appear on every printed page.
Assume you have the dataset displayed below.
If we print this worksheet in this format, the heading or first row will most likely only display on the first page. The first row, which specifies what each column stands for, will be missing from the subsequent pages.
The following steps will guide to set the top row or header to appear on every page.
- Locate the Page layout tab on the excel menu bar and click on it.
- Locate the dialog box launcher in the page setup group and click on it.
- Click on sheet tab in the dialogue box that appears.
- And then click on the arrow next to the empty space next to the “rows to repeat at top”
- A popup box appears and you will be requested to select the row you wish to set to print on every page
- After selecting the row to print on every page, you will notice that $1:$1 is automatically inserted in the “Rows to repeat at the top” field.
- Click on OK
When you print this data, you will notice that the top row header is repeated on every page.
You can crosscheck or verify this by clicking on the file tab > and selecting the print option. The Print Preview pane will be displayed as a result of this action. There you can see what your final printout is going to look like.
When you click the arrow icons at the bottom of the Print preview pane, the headers should repeat on each page.
It’s also not necessary to have the rows you want to repeat at the top all the time. So, you can press Ctrl+ the row and select more than one row. The rows selected will appear at the top of the table.
When printing, you can also have the left-most column (or multiple left-most columns) repeated on every page. The procedure is identical, except that in Step 5, instead of selecting a row, you can select a column to repeat. Of course, the top row and column can be set to repeat on every printed page.
Using the NameBox trick to set first row to print on every page
The advantage of using the aforementioned procedure (in which we use the Page Setup dialogue box) is that it provides you with many more options when printing your reports.
However, if all you want to do is ensure that the top through or leftmost column repeats on every printed page, this NameBox trick is much faster.
For instance, assuming we have this data set below:
Follow the procedures to exploit the namebox trick mentioned above.
Click on the header row and select the namebox field
Input Print_Titles into the text field and hit the enter key.
If you want to repeat multiple header rows or columns, first select them and then name them Print Titles.
In some situations, you might want to print the header row on some specific pages and leave others out. How can this be done since there is no in-built function to perform this task?
You could use the VBA macro code posted below to execute this function.
To determine the total number of pages in the dataset that will be printed, use Application.ExecuteExcel4Macro(“GET.DOCUMENT(50)” in the preceding code.
It then prints all of the pages (except the last one), with the PrintTitleRows set to $1:$1. As a result, all pages except the last will have the first row printed as a header.
The last page is then printed with the PrintTitleRows property set to null, resulting in no header rows being printed on the last page.
This is quite cumbersome but it will suffice 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.