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 ofMOD(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)
Prashanth,
Formula 1 worked perfectly! I can’t thank you enough for helping me with this! No article, self-help guide, YouTube video could help me with this, but YOU came through! MVP!
Thank you so much for this Prashanth!
I’ve been trying to find a way to automatically add every 6th column cell from the 9th row beginning at E9 (So E9, K9, Q9, etc). Can you help me with this? After a full day of trying to figure this out I’m about ready to throw my laptop out of the window…
These cells don’t have nor need references. They’re fully independent cells.
Additionally, new columns are regularly added. If possible I’d like this formula to also automatically add the new columns as they’re added.
Thank you!
Hi,
Already posted a solution.
https://infoinspired.com/google-docs/spreadsheet/sum-every-n-cells-to-the-right-or-bottom-google-sheets/
But I will try to customize it for your range and update you via my next comment.
In the meantime, you can try it using my tips on the linked post.
Just wait! I’ve gone thru’ your comment the second time. Please ignore my previous reply as it’s not what you are looking for. I will update you soon!
Formula 1:
=sum(ArrayFormula(if(mod(sequence(1,COLUMNS(E9:9)),6)=1,E9:9)))
Formula 2:
=ArrayFormula(sumif(mod(sequence(1,COLUMNS(E9:9)),6),1,E9:9))
Try either of the ones. Both formulas are working in my test.
How might one go about deleting every nth row (the entire row and not just it’s content) from the worksheet? So as in your example, deleting every 4th row would result in the series 5, 5, 5, 4, 4, 4, 3, 3, 3, etc. without any blank cells in between.
Hi, Johmann,
Maybe you can do that with a script. Unfortunately, I am not familiar with that 🙁
Here is my workaround using formula.
Delete Every Nth Row in Google Sheets Using Filters