Filter every nth row

How to filter every nth row in Excel

To filter and extract every nth row in Excel, you can combine the FILTER function with the MOD, ROW, and SEQUENCE functions.

How to filter every nth row in Excel

Here are the steps to filter every nth row in Excel:

  1. Open Excel.
  2. Type =FILTER(data,MOD(SEQUENCE(ROWS(data)),2)=0)
  3. Press Enter.
  4. The formula will return every second row from the specified range.

Example

In the example, we have a named range, data which refers to range B3:B12. To extract every second row from the data set, use the following formula:

=FILTER(data,MOD(SEQUENCE(ROWS(data)),2)=0)

Example to filter every nth row in Excel

Explanation:

The formula =FILTER(data, MOD(SEQUENCE(ROWS(data)), 2) = 0) extracts every second row from the specified range of data.

  1. ROWS(data): This function calculates the total number of rows within the specified range, “data”. For example, if data spans 10 rows, ROWS(data) will return 10. This step is important because it tells the SEQUENCE function how many numbers to generate, matching the number of rows in data.
  2. SEQUENCE(ROWS(data)): SEQUENCE creates a series of numbers starting from 1 and going up to the total row count given by ROWS(data). For example, if ROWS(data) is 10, then SEQUENCE(ROWS(data)) generates {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, where each number corresponds to a row in data. This series allows us to perform calculations row by row, using each row’s position number.
  3. MOD(SEQUENCE(ROWS(data)), 2) = 0: The MOD function finds the remainder when each number in the sequence is divided by 2. This condition MOD(SEQUENCE(ROWS(data)), 2) = 0 checks if the result is zero, which only happens for even numbers. For example, MOD(2, 2) is 0, MOD(4, 2) is 0, and so on. For example, MOD(1, 2) is 1, MOD(3, 2) is 1, meaning odd row numbers do not meet this condition. This condition separates the even-numbered rows in data.
  4. FILTER(data, …): The FILTER function takes data and the condition MOD(SEQUENCE(ROWS(data)), 2) = 0 and returns only the rows where this condition is true. Since this condition selects even-numbered rows, FILTER will return only every second row from the data.

In summary, the formula extracts rows 2, 4, 6, etc., from the range data, effectively selecting every second row based on row position.

Workaround with MOD and ROW

To extract every nth row (in this case, nth = 3) without Microsoft 365 functions, like FILTER and SEQUENCE, you can use the formula below:

=MOD(ROW(B3)-ROW($B$3)+1, 3) = 0

The formula returns TRUE and FALSE values.

filter the helper column

Finally, Filter the helper column to show only TRUE.

Learn more about how to create effective formulas in Excel.