Select Every Third Row? 3 Quick Ways

To select every third row in a spreadsheet, you can use a helper column with a formula, Excel’s Go To Special feature after a filter, or a simple VBA macro. This article explains each method step by step, so you can highlight, delete, or copy every 3rd, 5th, or Nth row in your data.

Formula

If you want to select every Nth row, the math is simple: the rows you want are 1, 1+N, 1+2N, and so on. The number of rows you will select is floor(total_rows / N), and the last selected row is N * floor(total_rows / N).

For example, with 100 total rows and N=3, you select rows 1, 4, 7, … 100. The count is floor(100/3) = 33, and the last row is 3 * 33 = 99. Note that row 100 is not selected because 100 is not a multiple of 3.

Worked Example

Using the defaults from the calculator above (N=3, total rows=100):

1. Divide total rows by N: 100 ÷ 3 = 33.33
2. Round down to the nearest whole number: 33
3. That is the number of rows you will select: 33
4. Multiply N by that number to get the last selected row: 3 × 33 = 99

So you will select 33 rows, and the last one is row 99. The calculator above gives the same result.

Method 1: Helper Column with MOD Formula

The most reliable way to select every third row in Excel or Google Sheets is to add a temporary helper column. This works for any interval—3rd, 5th, 10th—and does not require any programming.

1. Insert a new column to the right of your data (say column B if your data starts in A).
2. In the first data row (e.g., B2), enter: =MOD(ROW(),3)=0 for every 3rd row, or replace 3 with your interval.
3. Drag the formula down to cover all your rows.
4. Filter on that column for TRUE (Excel) or check the box (Google Sheets).
5. Now only the rows where the formula returns TRUE are visible. Select them all, then delete, copy, or format as needed.
6. Clear the filter and delete the helper column when done.

Why MOD(ROW(),3)=0? The MOD function returns the remainder after division. Row 3 gives 0, row 6 gives 0, and so on. If your data starts on row 1, this works directly. If your data starts on row 2 (because row 1 is headers), use =MOD(ROW()-1,3)=0 to start counting from the first data row.

Method 2: Go To Special After Filtering

This is a variation of the helper column method but uses Excel’s built-in Go To Special to select only visible cells. It is handy when you want to select the rows without manually dragging the selection.

1. Add the helper column as described above.
2. Filter the helper column to show only TRUE.
3. Select the entire range of your data (including hidden rows).
4. Press F5 or Ctrl+G to open the Go To dialog.
5. Click Special, then choose Visible cells only, and click OK.
6. Now only the visible (every third) rows are selected. Copy, delete, or format them.

This method is useful when you want to copy the selected rows to another location without dragging through hidden rows.

Method 3: VBA Macro (for Excel)

If you need to select every third row frequently, a small VBA macro can automate it. This is a permanent solution that works with a single click.

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the following code:

Sub SelectEveryNthRow()
    Dim rng As Range
    Dim i As Long
    Dim n As Long
    n = 3 ' change this to your interval
    Set rng = Rows(1)
    For i = 1 To ActiveSheet.UsedRange.Rows.Count Step n
        Set rng = Union(rng, Rows(i))
    Next i
    rng.Select
End Sub

4. Close the editor and run the macro (Alt+F8, then select it).

This selects rows 1, 4, 7, etc. across the entire used range. Adjust the starting row if you have headers—change Rows(1) to Rows(2) and start the loop at 2.

How to Choose the Right Method

Your choice depends on your skill level and how often you need to do this:

• One-time task: Use the helper column with MOD. It is quick, visual, and easy to undo.
• Repeated task: Use the VBA macro. Once set up, it is one click.
• Google Sheets: Use the helper column method; VBA is not available. You can also use the FILTER function to create a new list of every third row.
• Large datasets: The helper column method can be slow on millions of rows, but it still works. VBA is faster for very large ranges.

If you want to select every third row based on a value in a column (not row number), use a different formula: =MOD(COUNTIF($A$2:A2,A2),3)=0 to select every third occurrence of each value.

Common Mistakes and Pitfalls

Here are the most frequent errors people make:

• Off-by-one errors: If your data starts on row 2, using MOD(ROW(),3) will select rows 3, 6, 9… which is actually every third row but starting from row 3, not row 2. Use MOD(ROW()-1,3) to start from row 2.
• Forgetting to adjust for headers: Always check the first data row number.
• Filtering without selecting visible cells: If you copy filtered data without using Go To Special, you might copy hidden rows too.
• Using the wrong interval: Double-check that you replaced the 3 in the formula with your desired interval.
• Deleting the helper column before filtering: You must filter first, then delete the column after clearing the filter.

Alternatives: Using Formulas to Extract Every Third Row

Instead of selecting rows, you might want to extract them into a new list. This is common when you want to sample data or create a subset.

In Excel, use the formula: =INDEX(A:A, ROW()*3) in a new column. Drag down, and it will pull the value from row 3, 6, 9, etc. If you want to start at row 1, use =INDEX(A:A, (ROW()-1)*3+1).

In Google Sheets, the FILTER function is cleaner: =FILTER(A:A, MOD(ROW(A:A),3)=0) returns every third row from column A.

These methods are dynamic—if you change the source data, the extracted list updates automatically.

Frequently Asked Questions

How do I select every third row in Google Sheets?

Google Sheets does not have VBA, so use the helper column method. Add a column with =MOD(ROW(),3)=0, filter for TRUE, and select the visible rows. Or use the FILTER function to create a new list: =FILTER(A:A, MOD(ROW(A:A),3)=0).

Why does my MOD formula not work when I have headers?

Because the row numbers shift. If your data starts on row 2, use =MOD(ROW()-1,3)=0 so that row 2 is treated as the first data row. Adjust the subtraction to match your header row count.

Can I select every 3rd row without a helper column?

Yes, you can use VBA in Excel, or you can use the Name Box trick: type 1:1,4:4,7:7 in the Name Box (next to the formula bar) and press Enter. This works for small ranges, but it is tedious for large ones.

How do I delete every third row permanently?

Use the helper column method, filter for TRUE, select all visible rows, then right-click and choose Delete Row. Make sure you have a backup first. After deleting, clear the filter and remove the helper column.

What if my data has blank rows?

Blank rows do not affect the MOD formula because it uses row numbers, not cell values. If you want to skip blank rows, you need a more complex formula that counts non-blank cells, such as =MOD(COUNTIF($A$2:A2,"<>"&""),3)=0.

Related

• How to Use AI at Work Without Replacing Your Judgment
• LiteLLM vs OpenRouter: Which Is Safer for Production?
• Copy.ai Competitors and Alternatives Compared

in n | Interval (N) | rows | 3 | 1 | 100
in total | Total rows in data | rows | 100 | 1 | 1000000
out count | Rows to select | rows | floor(total / n) | 0
out last | Last selected row | row number | n * floor(total / n) | 0