How to print X rows per page in Excel?
When working in Excel, you might often face situations where you need to print a specific number of rows on each page. For example, if you have a large dataset but want your printouts to show only 3 rows on every printed page for easier reading or processing, the default print settings in Excel do not provide a direct option for this requirement. This tutorial will walk you through several practical ways—ranging from manual steps to automated approaches—to print a fixed number of rows per page in Excel. These methods cater to a variety of skills, whether you prefer straightforward actions, script-based solutions, or using add-ins to simplify the process. We will also highlight the scenarios where each method is most effective, as well as key tips and things to be aware of for the best results.
Print X rows per page by insert page break manually
Print X rows per page by running VBA
Print X rows per page by Kutools for Excel 
Print X rows per page by using formulas and helper columns
Print X rows per page by insert page break manually
For small- or medium-sized data ranges, you can easily manage how many rows appear on each printed page by manually inserting page breaks in Excel. This method provides direct control and is ideal when you only need to set page breaks a few times or have a relatively short list. However, with larger datasets, this approach may become time-consuming.
To demonstrate, let's consider the goal of printing 3 rows per page. The manual insertion process is as follows:
1. Select the cell in the first column of the row just below the last row you want on the page. For example, to print rows 1 to 3 on the first page, select the cell in the 4th row. See screenshot:
2. Navigate to the Page Layout tab, and then click Breaks > Insert Page Break. Refer to the screenshot below:
After this action, a page break line appears below the 3rd row—meaning the preceding rows will print on a separate page.
Repeat these steps for every set of rows you wish to group on a separate page. For example, select the 7th row to insert a page break after the6th row, select the10th row for a break after the 9th row, and so on.
3. Once all required page breaks are set, go to File > Print to preview and print your data—verifying that only your specified number of rows appear on each page.
This method is best suited for small datasets or when you only need to occasionally control page breaks for printing. However, be aware that if you add or remove rows after setting page breaks, you may need to re-adjust them manually. It's also easy to accidentally skip a row, so double-check your print preview before printing a large batch.
Print X rows per page by running VBA
For large or frequently changing datasets, manually inserting page breaks can become inefficient and error-prone. If you're comfortable with basic scripting, using VBA (Visual Basic for Applications) is a much faster way to insert page breaks at regular intervals, such as after every 3 rows, throughout your worksheet. This automated approach is especially valuable when rows are added or removed regularly or when you have to repeat this task multiple times.
Before you start, remember to save your workbook, especially if this is your first time using VBA, as running code make changes you might wish to undo. Also, this method modifies your active worksheet, so double-check you’re working on the correct sheet.
Follow these steps to insert page breaks at every specified interval using VBA:
1. Open your target worksheet. Then press Alt + F11 to launch the Microsoft Visual Basic for Applications editor window.
2. In the VBA editor, click Insert > Module to insert a new code module. Copy and paste the following VBA code into the module window:
VBA: Insert page breaks every nth row.
Sub formatSheets()
For i = 3 To 11 Step 3
ActiveSheet.HPageBreaks.Add Before:=Cells(i + 1, 1)
Next
End Sub
3. With the code in place, press F5 or click the Run button to execute it. The macro will automatically insert page breaks after every 3rd row in the range specified within the code. Review your worksheet to confirm the results.
For i =3 To 11 Step3 means page breaks are placed after every3 rows, starting from row 3 up to row 11. You can easily adjust the starting row, ending row, and the step value (interval) to suit your printing needs. For example, if you want breaks every 5 rows up to row 50, change it to For i =5 To 50 Step5.This VBA solution excels in handling large datasets effortlessly. However, make sure to double-check the intervals, and always preview before printing. If there are existing manual page breaks, they may need to be cleared or adjusted.
Print X rows per page by Kutools for Excel
While VBA is efficient for those familiar with coding, not everyone is comfortable using macros. In addition, manual methods may quickly become tedious for large tables. For a more user-friendly, efficient, and error-proof process—especially suited for users who frequently need to customize page breaks—using Kutools for Excel is an excellent alternative. Kutools provides an Insert Page Break Every Row tool that allows you to quickly and easily insert page breaks at a chosen interval across any number of rows, without writing any code or manually selecting rows.
Here’s how you can use Kutools for Excel to meet your printing needs:
1. Open the worksheet you wish to print, then navigate to Kutools Plus > Printing > Insert Page Break Every Row.
2. In the Insert Page Break Every Row dialog box, simply type the number of rows you want per page (for instance: 3), and then click OK. A message will appear showing you how many page breaks have been inserted. Click OK in the dialog to close it.
Kutools’ approach is highly recommended for beginners, frequent users who handle many worksheets, or those who want to streamline repetitive tasks without manual intervention. It’s also useful because it minimizes the chance for human error during the page break setup and offers convenient reminders about inserted breaks. Just remember: for optimal results, check your print preview before finalizing, especially if your data may change after setting the page breaks.
Print X rows per page by using formulas and helper columns
Sometimes, you may want more flexibility on how your data is laid out across print pages, especially when you want to add custom titles, page numbers, or section markers. Although Excel does not offer a direct way to split printing dynamically based on row counts without inserting page breaks, you can use helper columns with formulas to flag each group's start, making it easier to manually adjust or format your sheet before printing. This approach is helpful if you want to visually separate printed groups or add additional notes per page. Here’s an example of how to use formulas for grouping data before printing:
1. Add a helper column next to your data range (e.g., insert a new column B if your data is in column A). In the first cell of the helper column (B1), enter the following formula to number each row in groups of 3:
=INT((ROW(A1)-1)/3)+12. Drag this formula down along your data rows to extend the numbering. Each block of 3 rows will share the same number. This identifies which rows should belong to the same printed page.
3. You can then use filters, conditional formatting, or manual page breaks aligned to these grouped rows for further customization when printing.
This solution is especially useful when you need to prepare handouts, create batches for review, or manage grouped data prints. However, it doesn't automatically insert page breaks but assists in planning and checking your manual or automated breaks for accuracy.
When working with these methods, always preview your printouts using the Print Preview function to check if the page breaks appear as expected. If you notice that page breaks aren't aligned correctly, double-check the step value or interval you've set in VBA, Kutools, or your manual selections. Adding or removing rows after setting page breaks will require reapplying the chosen method to maintain correct pagination. For shared workbooks or templates, consider documenting your row intervals so future users can maintain the print layout easily.
Demo: Insert Page Break In Every X Row using Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in