MVP #14: Transpose in Excel, the easy way and the sure way

There have been times I needed to transpose a table and through experience I have discovered two ways - the easy way, using copy paste special transpose, and the sure way, using formula.

In this post I will show you both ways.

As usual, I have generated a sample data. Shown below.

Now let's transpose this table.
You can do this easily by copying the table and paste special, transpose. This is the easy way.

And here is the result.

Cool.

The issue with this method is that it doesn't work all the time and I'm still yet to figure why. But there is a sure way, using formula. And this always works.

The formula is 
 =INDIRECT("R"&COLUMN(A1)&"C"&ROW(A1),FALSE)

Drag the formula to the right and down.

A clearer screenshot is 

And here is how it works: INDIRECT is an Excel function that allows you to input a dynamic cell range that will take it's column and row ID from another cell's value. 

The formula
 =INDIRECT("R"&COLUMN(A1)&"C"&ROW(A1),FALSE)
simply tells Excel to input into this current cell the value of cell R1C1. The false is to indicate that we are using the R1C1 style and not the A1 style. And if you look closely at the screenshots you will notice that, it simply swaps the row ID for column ID and column ID for row ID. And that is what achieves the transpose.

Now you might say, couldn't we have done the same without using indirect? I have thought long and hard about it and unable to come up with a simpler way. If you by chance find a another way, be kind to share it.

Thanks and don't miss my next MVP post.