Working with “Nth Occurrence” is one of my favorite topics in Google Sheets! I’ve written several tutorials on related topics, which I’ll link in the resources section at the bottom. In this guide, I’ll explain how to copy every nth cell from a column in Google Sheets.

To accomplish this, we’ll use the MOD function alongside the SEQUENCE function. This combination helps us identify every nth row in a column. To actually extract values from these rows, you can use the IF function or the FILTER function.

What Does “Every nth Cell from a Column” Mean in Google Sheets?

In Google Sheets, “every nth cell” means selecting cells at specific intervals within a column. For example, if n is 4, you might refer to either of these patterns:

• The 4th cell, 8th cell, 12th cell, and so on
• Or, the 1st cell, 5th cell, 9th cell, and so on

You can choose the interval (n) and the starting position based on your needs. If you start from the 1st row with an interval of 4, you would select every 4th cell starting from that row.

Formula to Find Every nth Cell from a Column in Google Sheets

To provide a formula, we need to know the interval number you want (i.e., the value of n). For this example, let’s say we want to select every 4th cell, and our data range is A2:A21. This formula can be easily adjusted for different ranges and values of n.

Generic Formula:

=ArrayFormula(MOD(SEQUENCE(ROWS(range)), n))

Example Formula:

If the range is A2:A21 and n is 4 (meaning every 4th cell), the formula becomes:

=ArrayFormula(MOD(SEQUENCE(ROWS(A2:A21)), 4))

Explanation of the Formula:

• SEQUENCE(ROWS(range)): This function generates a sequential array (1, 2, 3, …) up to the total number of rows in the specified range. For A2:A21, it will produce {1, 2, 3, …, 20}.
• MOD function: The MOD function calculates the remainder when each sequential number is divided by n. Here, since n is 4:
  • Every 4th cell (e.g., 4th, 8th, 12th, etc.) will yield a remainder of 0.
  • Cells at positions like the 1st, 5th, 9th, and so on, will yield a remainder of 1, and so forth.

This formula highlights which cells fall on the specified interval, allowing you to identify or filter every nth cell.

Copying Every nth Cell

To actually copy or extract the values at these intervals, we can use an IF function with a logical test. Here’s the formula:

=ArrayFormula(
   LET(
      nth, MOD(SEQUENCE(ROWS(A2:A21)), 4), 
      IF(nth=0, A2:A21, )
   )
)

Explanation:

• LET function: We name the calculated nth positions as nth.
• IF logical test: It checks if nth=0. If true, it returns the value in the corresponding row of A2:A21. If false, it returns a blank.

With this formula, only values from A2:A21 that are in every 4th row will be displayed.

Try changing the condition nth=0 to nth=1 and observe how the output shifts to display every 1st, 5th, 9th, etc., cell instead.

Filtering to Display Only nth Rows

To create a list that includes only every nth row, without blank cells, use the FILTER function with LET as follows:

=ArrayFormula(
   LET(
      nth, MOD(SEQUENCE(ROWS(A2:A21)), 4), 
      FILTER(A2:A21, nth=0)
   )
)

Explanation:

• LET function: Defines nth as the result of MOD(SEQUENCE(ROWS(A2:A21)), 4).
• FILTER function: Filters the range A2:A21 to include only rows where nth=0.

This formula will produce a clean, compact list displaying only every 4th cell in the specified range.

You can also adjust the condition by replacing nth=0 with nth=1 to display every 1st, 5th, 9th cell, and so on.

Resources

Here are some additional resources for related tutorials on working with intervals and nth occurrences in Google Sheets:

• How to Return Nth Occurrence of a Day in a Month in Google Sheets
• Using VLOOKUP to Find the Nth Occurrence in Google Sheets
• How to Sum Every Nth Row or Column in Google Sheets
• How to Highlight Every Nth Row or Column in Google Sheets
• Delete Every Nth Row in Google Sheets Using Filters
• Import Every Nth Row in Google Sheets Using Query or Filter (Same File)
• Date Sequence Every Nth Row in Excel (Dynamic Array)