How to get every nth row in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0)Keep the rows whose position divides by n: =FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0) returns every 3rd row when E1 is 3.
- Result on the example
- a 4-row list that spills from D3 in
D3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D3. Change any input and the result updates, or edit the formula to experiment.
How it works
SEQUENCE numbers the rows of the data 1 to 12. MOD gives the remainder after dividing each number by 3, which is 0 for rows 3, 6, 9 and 12. FILTER keeps those rows. Change E1 to 2 for every other row.
=FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0)Result{4 rows × 2 cols} 46084, 14931, 46087, 15108, …
A2:B1312 rows × 2 cols: 3/1/2026, 14,820, 3/2/2026, 14,876, …A range: 12 rows × 2 columns.
A2:A1312 rows × 1 col: 3/1/2026, 3/2/2026, 3/3/2026, 3/4/2026, …A range: 12 cells in one column.
ROWS(A2:A13)12ROWS: Number of rows in a range.- array
A2:A1312 rows × 1 col: 3/1/2026, 3/2/2026, 3/3/2026, 3/4/2026, …
- array
SEQUENCE(ROWS(A2:A13)){12 rows × 1 col} 1, 2, 3, 4, …SEQUENCE: A grid of sequential numbers. Spills.- rows
ROWS(A2:A13)12
- rows
E13The value in E1.
MOD(SEQUENCE(ROWS(A2:A13)),E1){12 rows × 1 col} 1, 2, 0, 1, …MOD: Remainder after division.- number
SEQUENCE(ROWS(A2:A13)){12 rows × 1 col} 1, 2, 3, 4, … - divisor
E13
- number
MOD(SEQUENCE(ROWS(A2:A13)),E1)=0{12 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0){4 rows × 2 cols} 46084, 14931, 46087, 15108, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:B1312 rows × 2 cols: 3/1/2026, 14,820, 3/2/2026, 14,876, … - condition1
MOD(SEQUENCE(ROWS(A2:A13)),E1)=0{12 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),E1)=0). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =CHOOSEROWS(A2:B13,SEQUENCE(ROWS(A2:A13)/E1,1,E1,E1)) | spills 4 × 2 | Builds the row numbers 3, 6, 9, 12 directly and picks those rows. |
| =FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13))-1,E1)=0) | spills 4 × 2Different result | Subtract 1 to start from the first row instead. Rows 1, 4, 7 and 10: March 1, 4, 7 and 10. |
Common mistakes
- Using
ROW(A2:A13)instead of SEQUENCE counts from the sheet's row 1, so the picks shift by the header rows. SubtractROW(A1)if you use ROW. - Sorting the source changes which rows are picked, since the positions are counted fresh each time.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get every nth row
D3, return every E1th row of A2:B13: the 3rd, 6th, 9th and so on.Questions
How do I get every other row?
Set n to 2: =FILTER(A2:B13,MOD(SEQUENCE(ROWS(A2:A13)),2)=0), or =1 for the odd rows.
How do I sum every nth row?
Wrap the filtered column in SUM: =SUM(FILTER(B2:B13,MOD(SEQUENCE(ROWS(B2:B13)),E1)=0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.