How to Copy Every Nth Cell in a Column in Google Sheets

Nth Occurrence is one of my favorite topic in Google Sheets. In earlier posts, I’ve detailed about finding nth Occurrences of a lookup value in Vlookup and also nth Occurrences of a Date in a month. Now here let me explain to you how to copy every nth Cell in a column in Google Sheets.

To do this, I am using Google Sheets MOD function. You can find the links that point to the above said other two tutorials at the end of this post.

What’s Meant by Every nth Cell in a Column in Google Sheets?

Here every nth cell means the repetition of a specific number of cells. If your nth number is 5 then it would be like the 5th cell, 10th cell, 15th cell and so on. That means ‘N’ is a user specified number to repeat in rows.

Formula to Copy Every nth Cell in a Column in Google Sheets

In the real sense, I can’t give a formula without knowing the nth cell number you want in the formula.

So in this formula, I am using every 4th cell as my nth cell. Also, I consider Column A in this example. you can change the nth cell and column reference later.

Master Formula:

=ArrayFormula(if(ArrayFormula(mod(row(A1:A25),4))=0,A1:A25,))

See the below screenshot to understand what this formula returns.

Example: Copy every 4th cell in Google Sheets

I have the above formula entered in Cell B1. So the formula copies the values from Column A that found in every 4th row like row # 4, 8, 12 and so on.

You can change the nth row in the formula by only changing the number 4 in it. Changing the number from 4 to 6 copies the values from every 6th rows.

Should I enter this formula in the first row itself?

No! You can insert this formula in any row. It will correctly copy every nth row in the column.

Can you explain how this formula finds every nth row value in Google Sheets?

The secret lies in the MOD function.

Formula Breakup 1:

=ArrayFormula(mod(row(A1:A25),4))

This MOD array formula would return the value 0 in every fourth rows. Using an IF logical test you can copy the value of every nth (here 4th) cell. That is what I did. If you want, I can explain it further. See how If copies values from a column if the cell value is 0.

The below IF based formula would return the value in the range A1: A25 if any of the cells contains the value 0.

=ArrayFormula(if(A1:A25=0,A1:A25,))

In this formula, you only want to change the first A1: A25 range with the MOD formula (Formula Breakup 1).

Actually, the MOD formula acts as the virtual column A that has the value 0 in every nth row. That’s all. This way you can copy every nth cell in a Column in Google Sheets.

Similar:

  1. How to find Nth Occurrences of a lookup value in Vlookup

2. How to find Nth Occurrences of a date in a month

3. How to Sum every Nth Row using Sumif in Gooogle Sheets

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

7 COMMENTS

  1. 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!

  2. 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!

  3. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.