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

Get FREE Advanced Excel Exercises with Solutions!

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 3 easy methods on how to copy every Nth row in Excel.


How to Copy Every Nth Row in Excel: 3 Methods

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

dataset to copy every nth row


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.
=OFFSET($B$5:$C$5,(ROW(A1)-1)*3,0)

formula to copy every nth row

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

formula to copy every 4th row

  • 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.
=OFFSET($B$5:$C$5,ROW(A1)*3-1,0)

OFFSET function to copy every nth row


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.
=IF(MOD(ROW(A1),2)=1,B5:C5,"")

create formula using IF, MOD and ROW function


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.

insert module in VB editor

  • 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

VBA code to copy every nth row

  • 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.

VBA returns every nth row

Read More: Copy Alternate Rows in Excel


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.

copy to range

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

copy first row

  • 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.

update format error

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

select top row with n blank rows

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

copy every other row

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

filter out blank rows

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

skip the first row

  • 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.

copy every 3rd row using Fill Handle

Read More: Copy and Paste Thousands of Rows in Excel


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.

Download Practice Workbook

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


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.

Stay with us and keep learning.


Related Articles


<< Go Back to Copy Rows | Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo