How to Copy Every Nth Row in Excel (4 Easy Methods)

You can easily copy data in Excel using the Copy-Paste feature no matter how large the dataset is. But it becomes tricky when you need to copy the alternate rows or every 3rd, 4th, or 5th row, and so on. You can still use the copy-paste feature with smaller datasets. But, it will be very tiring to do this manually with a large dataset. This tutorial highlights 4 easy methods on how to Copy Every Nth Row in Excel.

Download Practice Workbook

You can download the practice workbook from the download button below.

3 Methods to Copy Every Nth Row in Excel

We will use the following dataset to highlight the methods to copy every nth row in Excel. So let’s start!

1. Use OFFSET and ROW Functions

You can create a formula using the OFFSET and ROW functions in Excel to copy every nth row. Follow the steps below to do that.

• First, assume you want to copy every 3rd row starting from the 1st row. Then apply the following formula in cell E5 and drag the Fill Handle icon below. You may need to apply proper formatting to the Date and Sales columns.

• If you want to copy every 4th row starting from the 1st row then change the 3 in the formula to 4.

• Now enter the following formula instead if you wish to copy every 3rd row but do not want to start from the 1st Change the 3 in the formula to 4, 5, 6 to copy every 4th, 5th, 6th row, and so on.

2. Utilize IF, MOD, and ROW Functions

Alternatively, you can create a formula using the IF, MOD, and ROW functions to copy every nth row in Excel. Follow the steps below for that.

• Assume you want to copy every other row starting from the first row. Then apply the following formula in cell E5 and drag the Fill Handle icon below. Change the 2 in the formula to 3, 4, 5 to copy the 3rd, 4th, 5th row, and so on.

3. Copy Every Nth Row in Excel VBA

You can also copy every nth row using VBA in Excel. Follow the steps below to do that.

• First, go to Developer >> Visual Basic or press ALT + F11 to open the VB Editor. Then select Insert >> Module. This will create a new blank module.

• Now copy the following code and paste it onto the blank module.

Sub CopyEveryNthRow()
N = CInt(Application.InputBox("Value of N:", _
"Copy Every Nth Row", , , , , , 1))
ST_Row = CInt(Application.InputBox("Starting row number", _
"Copy Every Nth Row", , , , , , 1))
Set IRange = Application.InputBox(Prompt:="Select range:", _
Title:="Copy Every Nth Row", _
Default:=Selection.Address, Type:=8)
r = 5
Count = CInt(IRange.Rows.CountLarge)
For i = ST_Row To CInt(Count) Step N
IRange.Rows(i).Copy Range("E" & r)
r = r + 1
Next i
End Sub

• Then press F5 to run the code. You will be asked for the value of N, the starting row number, and the data range (B5:C16). Suppose you want to copy every 3rd (N = 3) row starting from the 1st row (row number = 1). Then the output result will be as follows.

How to Copy Every Other Row Using Fill Handle in Excel

Using the Fill Handle tool is perhaps the quickest way to copy every other row or every nth row in Excel. Follow the steps below to do that.

• First, assume that you need to copy every other row in range E:F as follows.

• Then, enter =B5 in cell E5 and drag the Fill Handle icon to the right i.e. cell F5.

• Excel will show an error in cell F5 as the formatting of cell E5 will also be copied there. Click on the error icon and select Update format.

• Next, select these two cells along with the adjacent two cells Then, drag down the Fill Handle icon.

• After that, every other row will be copied as follows.

• Now you can Filter out the blank rows to copy the data as shown below.

• If you want to skip the first row, then start with the reference from the second row (B6) instead.

• Now start with the cell reference from the third row (B7) and drag down two more blank rows with the top row to copy every 3rd row as shown below. Start with the cell reference from the fourth row (B8) and drag down three more blank rows with the top row to copy every 4th row and so on.

Things to Remember

• You need to press CTRL + SHIFT + ENTER to apply the array formulas if you are not using Excel 365.
• Don’t forget to save the file as a macro-enabled workbook to avoid losing the code.

Conclusion

Now you know how to copy every nth row in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.