# 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:

- You can easily remove duplicates, filter out irrelevant or erroneous entries, and ensure data accuracy by focusing on specific rows.
- 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.
- By utilizing formulas and functions, you can set up dynamic copying mechanisms that automatically update as your data changes.
- 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:

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

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

- Now, select the new cell and a few blank cells below it.
- The number of blank cells you must select will depend on the value of
**N**. - If you need to copy every third cell from the original dataset, then choose two blank cells along with the new cell.
- 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:

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

`=MOD(C2,3)`

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

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

- Copy (
`Ctrl`+`C`) the filtered data to another location where you wanted to copy every nth from a table in Excel. - 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.

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

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

- In the above formula, you must make adjustments according to the actual Excel dataset that you’re organizing.
- 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.

- Hit the
`Enter`

key to populate the first copied data in the selected cell. - Now, drag the fill handle until the end to copy every third row from the adjacent column.
- 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:

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

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

- Now, drag the fill handle by selecting the first cell or copy-paste the same formula until the end.
- 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:

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

- Now, press the
`Alt`+`F11`keys together to bring up the**VBA Editor**on your Excel worksheet. - Click the
**Insert**menu and then choose**Module**from the context menu that pops up. - A blank
**Module**will show up on the**VBA backstage**(the grey board). - 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
```

- 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:

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

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

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

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