In this article, we will learn to Repeat Rows at Top of Specific Pages in Excel. Microsoft Excel provides users with the opportunity to enlist large datasets. Whenever we have large datasets, excel prints them on different pages. Sometimes, we need to repeat the same rows at the top of specific pages. Unfortunately, Excel doesn’t have any direct solution for it. Because it repeats the rows on every page. But today, we will demonstrate step-by-step procedures. Using these procedures, you can easily repeat rows at the top of specific pages. So, without any delay, let’s start the discussion.
Download Practice Book
You can download the practice book from here.
Step-by-Step Procedures to Repeat Rows at Top of Specific Pages in Excel
Before providing the solution, let’s take a look at the problem and dataset. Here, we will use a Home Loan EMI Calculator as our dataset. The dataset contains a lot of information. We want to repeat Rows 2 to 6 at the top of specific pages.
Now, if we want to print it, it will not contain the desired rows at the top of specific pages. If you press Ctrl + P to see the print preview, you will see the rows on the first page.
But if you move to the second page, then it will not repeat the desired rows. The same thing happens for the rest of the pages.
In the following steps, we will show the procedure to repeat rows at the top of specific pages in Excel. We will print pages 1 to 7 with repeated rows at the top and others with no repeated rows. You need to observe each step to get the expected result.
STEP 1: Select Rows to Repeat at Top of Pages
- In the first place, we will select the rows we want to repeat at the top of the pages.
- To do so, go to the Page Layout tab and select the Print Titles option. It will open the Page Setup dialog box.
- In the Page Setup dialog box, select Sheet and click on the arrow sign of the ‘Rows to repeat at top’ field.
- The click on the arrow sign will open a small box.
- Now, select the rows you want to repeat. We have selected Rows 2 to 6 in this case.
STEP 2: Repeat Rows at Top of Every Page
- Secondly, we need to repeat rows at the top of every page.
- For that purpose, after selecting the rows, click on the drop-down arrow of the small box.
- After that, the original Page Setup box will appear.
- Click OK to move forward.
STEP 3: Check Print Preview of All Pages
- Thirdly, you need to check the print preview of all pages.
- To see the print preview, press Ctrl + P.
- Alternatively, you can click on the Home tab and select the Print tab to view the preview.
- If you observe the preview, then you will see the rows on the top page.
- You can also see there are 12 pages.
- The number of pages has increased after applying the setting.
- Meanwhile, if you move to the second page you will see the desired rows again.
- Furthermore, you will see the desired rows at the top of all pages.
- However, to repeat rows at the top of specific pages we need to use a trick. We will talk about the trick in the following section.
STEP 4: Print Specific Pages with Repeated Rows
- At this moment, we will print specific pages with repeated rows.
- We want the rows to repeat at the top of pages 1 to 7.
- So, type 1 and 7 respectively in the Pages field and click on the Print option.
STEP 5: Note Data of Last Page
- When you are printing specific pages with repeated rows, then, you must remember the information on the last row of the last printed page.
- Because after removing the repeated rows, the arrangement will change and some information may not be printed.
- In our case, the last row of page 7 contains the information for month number 145.
STEP 6: Change Settings after Printing Specific Pages
- After printing desired pages with repeated rows, we need to change the previous settings.
- For that purpose, go to the Page Layout tab and select Print Titles.
- After that, remove the row numbers from the ‘Rows to repeat at top’ field.
- Click OK to proceed.
STEP 7: Adjust Data to Print Other Pages Correctly
- This is the most important step as the page break changes after changing the settings.
- First of all, press Ctrl + P to open the print preview.
- You can see the rows on the first page.
- Also, the number of pages has decreased. This is the result of removing the row numbers in the previous step.
- However, if you move forward to page 7, you will see this page starts from month number 150.
- But, when we were printing the repeated rows, page 7 ended with the month number 145. So, we need to adjust the data to get all the information correctly.
- To adjust the data, go to the Excel sheet first and find month number 146.
- As the last row of the last page contained month number 145 in the previous case, that’s why we need to start from month number 146 this time.
- After finding month number 146, try to locate the next page break.
- In this case, the page break is between Rows 156 and 157.
- Now, right–click on Row 153 and select insert from there. It will insert a new empty row.
- Repeat the same step till month number 146 comes just below the page break.
STEP 8: Examine New Print Preview
- In this step, press Ctrl + P and examine page 7.
- You can see the data starts from month number 146.
STEP 9: Print Other Pages Without Repeated Rows
- Finally, type 7 and 10 respectively in the Pages field and click on the Print option to print other pages without repeated rows.
In this article, we have discussed step-by-step procedures that Repeat Rows at Top of Specific Pages in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
- How to Repeat Rows in Excel When Printing (3 Effective Ways)
- Repeat Cell Values in Excel (6 Quick Methods)
- How to Repeat Formula for Each Row in Excel (7 Easy Ways)
- Repeat Cell Value X Times in Excel (6 Easy Methods)
- How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)
- How to Repeat Rows in Excel Based on Cell Value (4 Easy Ways)