How do you transpose data?

Dakota

I need the information from Column 1 - rows 2-10 to be Flipped or transposed into Row 1 - Columns 2-10. In the sheet below 1 would stay in the same spot. Cell with "2" would need to move to Row 1, Column2. What function can I use to complete this?

Genevieve P.

Hi @Dakota

You could use an INDEX Function to bring data into your top row. For example for Row 2's information in Column 2, you could use:

=INDEX([Column1]:[Column1], 2)

The number 2 at the end of the Index function identifies what row to bring back. This means for column 3, you could adjust it to be:

=INDEX([Column1]:[Column1], 3)

And so on.

If you don't want to have to update the formula, you could use a helper row at the top of the sheet to list out the row numbers you want to bring back. Then reference the cell in the top of the current column instead of typing the number. For example:

=INDEX([Column1]:[Column1], [Column2]$1)

Then if you drag this formula over into Column3, the column name will update so it's now looking in the top row for Column3:

=INDEX([Column1]:[Column1], [Column3]$1)

Cheers,

Genevieve

mpostrel

Hi @Genevieve P. I tried doing exactly your formula, but it is not working.

=INDEX([Column1]:[Column1], [Column2]$1)

I am simply trying to transfer a vertical row of cells to horizontal, I am sure there must be an easy solution to this? I was looking to copy and paste horizontally, but could not find a solution.

Here is a screen shot of my example. After writing this formula in "Column2" I tried to drag the formula horizontally. All cells are UNPARSEABLE.

Genevieve P.

Hi @mpostrel

The formula you're referencing needs the header-row with the helper numbers to work 🙂

Row 1 = helper column with numbers 1, 2, 3, etc.
Row 2 = formula

For example:

Cheers,
Genevieve