How do you transpose data?

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?



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!