How to Copy Every Nth Row in Excel: 6 Best Methods

Have you ever found yourself overwhelmed with data, desperately needing to extract specific rows in Excel? Look no further, because today I’m unveiling the secret technique that will transform your data manipulation game: copying every Nth row in Excel.

Whether you’re a data analyst, a spreadsheet enthusiast, or simply someone who wants to optimize their workflow dealing with numerical data, this powerful trick will save you precious time and effort. Get ready to unleash the true potential of Excel as you dive into the step-by-step guide to effortlessly copy and transform your data with precision. Let’s get started!

Also read: How To Average Every Nth Cell In Excel

Reasons to Learn to Copy Every Nth Row in Excel

Find below why you may want to learn this Excel skill of data manipulation:

1. You can easily remove duplicates, filter out irrelevant or erroneous entries, and ensure data accuracy by focusing on specific rows.
2. When presenting performance reports to stakeholders or clients and need to create a custom visualization of data by selecting every nth row, this skill will help.
3. By utilizing formulas and functions, you can set up dynamic copying mechanisms that automatically update as your data changes.
4. Highlighting every nth row in Excel and analyzing the selected data is a must-have skill for data analysts.

Let’s explore multiple methods to copy every nth row in Excel below:

1. Copy Every Nth Row Using the Fill Handle

Here’s how you can use the fill handle of Excel to populate every nth row in different cell references or locations by copying data:

1. Select a cell and enter an equals sign and type in the cell reference of the first row of data.

2. Hit Enter and use the fill handle horizontally to populate data for the entire row.

3. Now, select the new cell and a few blank cells below it.
4. The number of blank cells you must select will depend on the value of N.
5. If you need to copy every third cell from the original dataset, then choose two blank cells along with the new cell.
6. Drag the fill handle across the column until you’re able to selectively copy every third row.

That’s it! You’ve successfully copied every nth row without creating any custom formula or writing any codes on Excel.

Also read: How To Insert Every Other Row In Excel

2. Using A Helper Column to Copy Every Nth Cell

In the above method, you can copy every nth row easily but there are also unnecessary blank rows in between two adjacent rows. To avoid such blank rows you can use a helper column. Here’s how it’s done:

1. Create a Helper Column on the right side of the existing dataset.
2. Now, populate the helper column with a series of numbers 1,2,3… until the end as shown in the above picture.
3. Then, create another column to the right, name it Mod, and apply the following formula in the first cell:

=MOD(C2,3)

4. Now, use the fill handle to drag and copy the same formula until the end.

5. Select the column headers and hit Sort & Filter button on the Home menu on the Excel ribbon.
6. Click on the Filter drop-down in the Mod column.
7. In the filter dialog box, uncheck every value except zero (0).
8. Hit the OK button to filter the dataset according to every third row from the first row of the dataset.

9. Copy (Ctrl + C) the filtered data to another location where you wanted to copy every nth from a table in Excel.
10. Don’t copy the helper column data though. You can delete the helper columns or hide them if you need the columns in the future.

If you wish to copy every fourth row, fifth row, and so on, simply change the Divisor in the MOD function, which is 3 in the above example.

3. Using OFFSET and ROW Functions

If you find the above two methods inefficient for an Excel worksheet with thousands of row data, then you can use this combination of OFFSET and ROW functions.

1. Highlight a cell in your Excel worksheet and enter the following function:

=OFFSET($B$2,(ROW(B1)-1)*3,0)

2. In the above formula, you must make adjustments according to the actual Excel dataset that you’re organizing.
3. Here are the changes you must make:
   • $B$2: is the first row of your dataset
   • B1: is the first cell under the column header where data exists
   • *3: is the N.
4. Hit the Enter key to populate the first copied data in the selected cell.
5. Now, drag the fill handle until the end to copy every third row from the adjacent column.
6. The formula will start populating zeros (0) when there’s no every third row in the Excel dataset you’re referring to.

4. Utilizing IF, MOD, and ROW Functions

Another function that you can use to select and copy every nth cell from one dataset to another is by combining the IF, MOD, and ROW formulas. Here’s how it’d done in step-by-step:

1. Select an empty cell where you want to populate copied data from an adjacent row.
2. Type the following formula into the selected cell and hit Enter to import the first row.

=IF(MOD(ROW(A1),3)=1,B3:C3,"")

3. Now, drag the fill handle by selecting the first cell or copy-paste the same formula until the end.
4. Adjust the formula according to your input data. For example, you may need to change the following formula parameters:
   • ROW(A1),3: change A1 to another cell range that works as the anchor cell from which you’re counting the nth row (in this example third row)
   • B3:C3: this cell range tells Excel which dataset to refer to copy and move every nth row
   • ROW(A1),3: replace 3 with other integers like 4, 5, 6, etc., to copy every 4th, 5th, 6th, etc., rows

Also read: How To Sum Every Nth Cell In Excel

5. Copy Every Nth Row Using These VBA Scripts

The methods you learned so far don’t offer any automation possibilities. Hence, you need to use Excel VBA if you want to automate the whole process. You can also attach the Excel VBA code to an existing data science project code on Excel to automatically organize the dataset by copying every nth row. Find below two different VBA scripts that you can try:

VBA Script With an Input Interface

This VBA script will prompt you for all the inputs. Thus, you don’t need to manually change the back-end VBA script on the Excel worksheet according to your sample datasets. Here are the steps to use the script:

1. Ensure you save your Excel worksheet in XLSM format by clicking File > Save As > Browse.
2. The Save As dialog box will pop up. There select a local folder on your computer.
3. Also, click the Save as type drop-down list and choose Excel Macro-Enabled Workbook.
4. Hit the Save button to complete the process.

5. Now, press the Alt + F11 keys together to bring up the VBA Editor on your Excel worksheet.
6. Click the Insert menu and then choose Module from the context menu that pops up.
7. A blank Module will show up on the VBA backstage (the grey board).
8. There, copy and paste the following VBA Script as is:

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

9. You can change r = 5 to r = 2 and Copy Range("E" & r) to Copy Range("D" & r) if you want to populate data from cell D2. Now, hit Save and close the VBA Editor.

Now, hit the Alt + F8 keys together to bring up the Macro dialog box. There, select the CopyEveryNthRow macro and hit the Run button. You’ll see the following prompts:

In the above box, you need to specify the value of N, like 2, 3, 4, etc., for every 2nd row, 3rd row, 4th row, etc.

In the Starting row number prompt, enter the row number from which you want Excel to count the nth row. In the above example, I’m entering 1. It means, Revenue data from January will be the first row and the next 3rd row will be the Revenue data for April, which is $5,000. Excel will consider row A1 as the header column row and ignore it from the current calculation.

Finally, you simply need to select the cell range from which Excel needs to copy every nth row. Click the OK button and you get your copied nth row under column E. You can change the destination if you need to.

Copy Every Nth Cell Using an Instant VBA Script

If you need a no-prompt approach script for the Excel VBA to copy every nth row, you can use the following script instead:

Sub SelectAltRows()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim x As Integer
    Dim NoRws As Integer
    'select the range
    Set rng1 = Range("b2:b14")
    'count the rows
    NoRws = rng1.Rows.Count
    'loop through every second cell of the range
    For x = 1 To NoRws Step 3
    'put the value from column c into column d
        rng1.Cells(x, 1).Offset(0, 1) = rng1.Cells(x, 1)
    Next
End Sub

In the above code, you must specify the cell range from which to copy every nth row in the code element Set rng1 = Range("b2:b14").

6. Copy Every Nth Row Using Power Query

If you’re importing a dataset from an external source and want to organize the data inside Power Query by copying every nth row, follow these steps:

1. On your Excel worksheet, go to Data and click Get Data to import data from available sources.
2. The imported data will open inside the Power Query Editor.

3. Add a new column named Serial No. and populate series like 1, 2, 3, etc.
4. Select the Serial No. column and click the Transform menu.

5. There, click the Standard option and choose Modulo.
6. Now, enter a value for Modulo. This is the value for N. For example, for every third row, enter 3.
7. Click the Filter menu on Serial No. column, check 0 and uncheck everything else.

8. Now, click File > Close & Load to import this structured data into your Excel worksheet for further analysis.

Conclusion

Now you know how to copy every nth row in Excel using different methods of varying complexities. If you’re a beginner-level Excel user, you should go for the Excel Fill Handle and helper column-based methods.

Contrarily, if you know a bit of formula editing in Excel, you can easily choose the OFFSET & ROW pair or IF, MOD, & ROW trio. Finally, if you’re an expert-level Excel user and looking to automate the whole thing, you may want to choose the VBA scripts to copy every nth row in Excel.

Give the above methods a spin and share your experience with the above Excel tricks by writing a comment below.