Filter Every Nth Row: Excel & Sheets Guide

Filtering every nth row means extracting rows 1, 4, 7, 10… (or any interval) from a spreadsheet, leaving the rest hidden or deleted. You can do this in Excel or Google Sheets using a helper column with a formula, then filtering on that column; the core formula is =MOD(ROW(),n)=0 (or a variation for starting at row 1). This article explains the exact steps, the logic behind the formula, and the most common pitfalls, so you can apply it to any dataset.

Formula

The filtering logic uses the ROW() function, which returns the current row number, and MOD(), which returns the remainder after division. The general pattern is:

MOD(ROW(), n) = remainder

Where:

• ROW() – the row number of the cell (in Excel) or ROW() (in Google Sheets).
• n – the interval (e.g., 3 for every 3rd row).
• remainder – the value that identifies which rows to keep. For rows 1, 4, 7… (starting at row 1), use =MOD(ROW(),3)=1. For rows 2, 5, 8…, use =MOD(ROW(),3)=2. For rows 3, 6, 9…, use =MOD(ROW(),3)=0.

If your data has a header in row 1, you typically want to keep data rows 2, 5, 8…, so the formula becomes =MOD(ROW(),3)=2 (assuming header is row 1). The calculator above gives you the first row and the next row for any interval and starting point, so you can verify your formula before applying it.

Worked Example

Using the default values from the calculator: n = 3, start = 1. The first row to keep is row 1, the next is row 4, then 7, 10, etc. In a dataset with 1,000 rows, you will keep floor(1000/3) = 333 rows. The calculator shows exactly that: first row = 1, next = 4, count = 333.

If your data has a header row, set start = 2. Then the first kept row is 2, next is 5, and the count in 1,000 rows is still 333 (because rows 2 through 1000 contain 999 data rows, and 999/3 = 333).

How to Filter Every Nth Row in Excel

Follow these steps to filter every 3rd row in Excel (works for any interval):

1. Insert a new column to the right of your data (say column B).
2. In cell B2 (if row 1 is a header), enter: =MOD(ROW(),3)=2 – this returns TRUE for rows 2, 5, 8…
3. Copy the formula down the entire column.
4. Select the helper column, go to the Data tab, and click Filter (or use Ctrl+Shift+L).
5. Click the dropdown arrow in the helper column, uncheck FALSE, and leave only TRUE checked. Click OK.
6. Now only the rows with TRUE are visible. You can copy them to a new sheet or delete the hidden rows (select visible rows, press Ctrl+G > Special > Visible cells only, then delete).

If you want to start at row 1 (no header), enter =MOD(ROW(),3)=1 in cell A1 (or B1) and follow the same steps.

How to Filter Every Nth Row in Google Sheets

Google Sheets works almost identically:

1. Add a helper column (e.g., column B).
2. In cell B2, enter: =MOD(ROW(),3)=2 (or =MOD(ROW(),3)=1 if no header).
3. Drag the fill handle down to apply to all rows.
4. Select the data range, click Data > Create a filter.
5. Click the filter icon in the helper column, uncheck FALSE, keep TRUE, and click OK.

Alternatively, you can use the FILTER function to create a new list without touching the original: =FILTER(A:A, MOD(ROW(A:A),3)=2) – this returns every 3rd row starting from row 2. This is useful if you don’t want to modify the original data.

Choosing the Right Starting Row

The most common mistake is using the wrong remainder. The formula =MOD(ROW(),n)=0 gives rows that are multiples of n (e.g., 3, 6, 9…), which is often not what you want. If your data has a header, you usually want rows 2, 5, 8…, so the remainder should be 2 (because 2 mod 3 = 2). Here’s a quick reference:

• No header, start at row 1: =MOD(ROW(),n)=1
• Header in row 1, start at row 2: =MOD(ROW(),n)=2
• Header in row 1, start at row 3: =MOD(ROW(),n)=0 (since 3 mod 3 = 0)

To check your starting row, use the calculator above: enter your interval and the row number you want to keep first, and it will tell you the next row. If the “next” row is exactly your interval apart, you’re good.

Common Problems and Solutions

Here are the typical issues people run into:

• Wrong rows selected – Usually a remainder mistake. Double-check with the calculator: if you want row 2, 5, 8, the remainder is 2, not 0.
• Filter shows no rows – Your formula might be returning FALSE for all rows because the starting row doesn’t match the remainder. For example, =MOD(ROW(),3)=1 will never be TRUE for row 2.
• Copying only visible rows – If you copy filtered rows and paste, Excel may include hidden rows. Use Go To Special > Visible cells only (Ctrl+G > Alt+S > V) before copying.
• Deleting every nth row – To delete, filter to show only the rows you want to remove (e.g., the FALSE rows), select visible cells, and delete. Then clear the filter to see the rest.
• Data starts at a different row – If your data starts at row 5 (with headers above), adjust the formula: =MOD(ROW(),n)=MOD(5,n) – but this works only if the interval divides evenly. Safer: use =MOD(ROW()-start_row, n)=0 where start_row is the first data row. For example, start at row 5, every 3rd: =MOD(ROW()-5,3)=0 gives rows 5, 8, 11…

Alternatives: Without a Helper Column

If you don’t want a helper column, you can use array formulas or built-in tools:

• Excel FILTER function (Excel 365): =FILTER(A:A, MOD(ROW(A:A)-1,3)=0) – returns every 3rd row starting at row 1. Adjust the -1 to change the start.
• Google Sheets FILTER: as above, =FILTER(A:A, MOD(ROW(A:A),3)=2).
• Power Query (Excel): Load your data, add an Index column, then filter by modulo of the index. This is more advanced but doesn’t alter the original sheet.
• Manual selection – For small datasets, you can hold Ctrl and click the row numbers you want, but it’s error-prone for large data.

Frequently Asked Questions

How do I filter every 5th row in Excel?

Use the same method with n=5. If your data starts at row 2 (header in row 1), enter =MOD(ROW(),5)=2 in a helper column, filter for TRUE. This keeps rows 2, 7, 12, 17…

Can I filter every nth row without a helper column?

Yes, if you have Excel 365 or Google Sheets, you can use the FILTER function directly. For Excel: =FILTER(A:A, MOD(ROW(A:A)-1,3)=0) for every 3rd row starting at row 1. For Google Sheets: =FILTER(A:A, MOD(ROW(A:A),3)=1).

Why does my formula return TRUE for the wrong rows?

Most likely because the remainder doesn’t match your starting row. Remember, MOD(ROW(),n) returns 0 for rows that are multiples of n. If you want to start at row 1, the remainder must be 1. If you want to start at row 2, it must be 2. Use the calculator above to verify.

How do I delete every nth row permanently?

Filter to show only the rows you want to delete (e.g., FALSE rows), select the visible cells, press Ctrl+G > Special > Visible cells only, then right-click and Delete Row. Clear the filter afterwards. Always make a backup first.

What if my data has blank rows?

Blank rows still have row numbers, so the formula works the same. If you want to skip blanks, you’ll need a more complex formula or a filter on another column. The MOD approach treats every row equally, including blanks.

Does this work on filtered data?

Yes, the helper column formula works on the full dataset. If you apply another filter on top, the MOD filter still works, but be careful when copying – use “Visible cells only” to avoid hidden rows.

Related

• How to Navigate a Workbook in Excel
• VLOOKUP Override Output: How to Force a Result
• Your Profile Was Signed Out? Causes & Fixes

in n | Every nth row | rows | 3 | 1 | 1000
in start | Start at row number | - | 1 | 1 | 1000000
out first | First row to keep | row # | start | 0
out next | Next kept row | row # | start + n | 0
out count | Rows kept in 1,000 rows | rows | floor(1000 / n) | 0