Microsoft Excel: The Beauty of the TRANSPOSE Function
At the Right Times, TRANSPOSE Saves the Day!
If you would like a copy of the Excel file showing the TRANSPOSE process I discuss in this post, you can download it here.
I rarely need the TRANSPOSE function, but when I do…
The Problem To Be Solved?
I have an Excel financial model I use to track and monitor stock holdings and stocks I am interested in watching. I previously discussed this concept here.
The hyperlinks above are populated by a Lookup tablethat is set up like this:
I want to “FLIP” the columns and row descriptions (Symbols across the top link sources for the rows), so it is set up like this:
Because I want to be able to create a simple list of links for each Symbol, like this:
Use TRANSPOSE to Do This
1 — Copy the Lookup Table
Select entire table, then select “copy” (CTRL + C):
2 — Create New Data Table
Go to a cell A1 on a new sheet (it can be anywhere — I prefer a new sheet).
Right mouse-click, select Paste Special → Paste Special.
Select “Values” → “Transpose”, and click OK.
Here’s the table.
3 — Copy and Paste The Stock Symbol References Where Ever I Want :-)
Conclusion
As you can imagine, this table conversion process takes seconds using TRANSPOSE.
It is not something you would frequently need (at least I don’t), but keep it in mind in case you do have a need!
About Don
“It’s time for different”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!
