Excel tutorials

How to Copy and Transpose Each Nth Cell from Row or Column

Copy or Display and Transpose Every N-th Cell in Excel

This Excel tutorial describes ways of copying each Nth cell in some continuous interval or so to make copy from the horizontal line of the vertical column and the column to the row. If you are any of these terms under then continued to read the tutorial.

  • How to copy every Nth cell from a column in the order
  • How to copy every Nth cell from among the column
  • Copy and transpose value from every 5th column in the same row
  • Copy and transpose value from every 5th row in the same column
  • How to Copy and Transpose Every Nth Cell from a Column in the Row

In the first case a copy of every Nth cell values or note Column 'A' and 'C' containing values. We want a horizontal copying formulas using one copy each Nth cell, every third cell, or every fifth cell.

How to copy and transpose every Nth cell from a column in the row

The formula in cell E4 is as follows: This formula is copy every third value, the first value is in cell A2.

=OFFSET($A$2;(ROW(INDIRECT("A"&COLUMN(A1);1))-1)*3;0)

The formula in cell E7 is as follows: This formula is copied every fifth value, the first value is in cell A4.

=OFFSET($C$4;(ROW(INDIRECT("A"&COLUMN(A1);1))-1)*5;0)

Copy and Transpose Each Nth Cell from Row to Column

If you want to copy values from the same row or so make up your vertical column then use the following formulas.

The formula in cell A6 is as follows: This formula copies the first value that is in cell C2. (number of +2 in the formula indicates the two columns before the first cell from which return value).

=INDEX($A$2:$AF$2;ROWS($A$1:A1)+2)

Instead of the above-mentioned formula we can write a simple formula =C2

The formula in cell A7 is as follows: This formula is copy every fifth value, the first value is in cell C2, copy this formula down.

=INDEX($A$2:$AF$2;ROWS($A$1:A1)*5+3)

Another way and an example of copying the value of the row to a column or vertical copying cells from the rows

The formula in cell A14 is the following: This formula copies the value of every fifth row in the column, the first value is in cell I3, copy this formula down.

=INDEX($A$3:$AF$3;ROWS($A$1:A1)*3+6)

How to copy every Nth cell from among the column