Transpose a values in a row to column using formulas When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in rows. How do I do this? Something like this:

Transposing values in a row to column using formulas

If it is a onetime process, my friend can use Paste Special > Transpose feature and be done. But this is no one time business. So lets understand which formula helps us do this.

1.       Lets assume original data is in $F$4:$J$5. Row 4 has card names & Row 5 has amounts.

2.       Wherever you want the out put, just list running numbers (1,2,3….) in a column. Lets say these are in cells D10:D14.

3.       To get the first card name, you can use the formula =INDEX($F$4:$J$4, $D10).

4.        To get the first amount due, use the formula =INDEX($F$5:$J$5, $D10)

5.       Now drag both these formulas down and you are done!

This is good, but I don’t like the extra column…

If that is the case, you can use the ROWS() formula to generate these running numbers for you on the fly. For example,

=INDEX($F$4:$J$4, ROWS($A$1:A1)) would work perfectly.