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 :-)
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!
“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!