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.

Image for post
Image for post
Dynamic Stock Dashboard Report — with Hyperlinks

The hyperlinks above are populated by a Lookup tablethat is set up like this:

Image for post
Image for post
Lookup Table used to Populate the Dashboard Links

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:

Image for post
Image for post
“Flipped” table for use elsewhere

Because I want to be able to create a simple list of links for each Symbol, like this:

Image for post
Image for post
List of Links by Symbol — for ease of use elsewhere

Use TRANSPOSE to Do This

1 — Copy the Lookup Table

Select entire table, then select “copy” (CTRL + C):

Image for post
Image for post
Select the Table to Copy

2 — Create New Data Table

Go to a cell A1 on a new sheet (it can be anywhere — I prefer a new sheet).

Image for post
Image for post
Select Different Worksheet, cell A1

Right mouse-click, select Paste Special → Paste Special.

Image for post
Image for post
Pasting the Copied Table

Select “Values” → “Transpose”, and click OK.

Image for post
Image for post
Transpose the Table

Here’s the table.

Image for post
Image for post

3 — Copy and Paste The Stock Symbol References Where Ever I Want :-)

Image for post
Image for post
List of Links by Symbol — for ease of use elsewhere

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

Image for post
Image for post

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

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store