Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell

Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list. It is especially handy when your source list is hundreds or thousand of rows deep.

In the pictured example, the formula in cell F2, which was copied down to cell F5 is
=OFFSET($C$2,ROW(C2)*6-6,0)

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,
5 comments on “Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell
  1. Najib says:

    Please elaborate formula more clear, it implies but shows 0 value.
    thkx

    • Tom Urtis says:

      Honestly, I don’t know how I can elaborate further. I showed pictures, I showed the formula, and I showed the results which display a clear correct result. Perhaps you can explain what exactly it is that is not working for you.

  2. Colin Macleod says:

    You can also use a combination of the Index and Row functions to do this.

    • Tom Urtis says:

      It always helps if an example (formula in this case) is provided rather than just a theory statement. There are many ways to accomplish the same result in Excel but that does not mean the alternative way is more efficient or less prone to errors. Or the alternative solution might be better but we’ll never know unless an example is posted to support its viability.

  3. Colin Macleod says:

    In cell F2 you can enter
    =index(C:C,ROW()*6-4)
    and copy down

Leave a Reply

Your email address will not be published. Required fields are marked *

*