Sunday 28 March 2021

Excel Basics - Paste Special + Transpose to Turn a Column of Values into A Row

Sometimes in Excel you have values lined up in a single column and you want to get all the values in a single row.  Maybe you want to create a matrix where rows and columns are compared with each other.

Here's an image with made up data to illustrate this:












Let's say I want all the values in column B to actual be across row 1.  So for example "Turnover" goes in cell B1, "Juice" goes in cell C1, "Split" goes in cell D1 and so on.  I could move them one by one but that would be a real pain, especially as the number of values in the column grows.  Instead I use Paste Special and Transpose.  Transpose is the fancy name for changing a row to a column and vice versa.

First, copy the values you want to transpose by selecting them, right clicking and selecting "Copy":












Next select the cell where you want the row of values to be Transposed to. In my case I want the first value in cell B1 so I select that.  I then right click and select "Paste Special...".  The form shown below comes up.  The part of the form I'm interested in is circled:

















Tick the "Transpose" box then hit OK.  Lo and behold, what was in the column is now in the row!













You still have your original column of data but you can delete those values and start doing some form of row column comparison!













...and so on!

Note you can also transpose rows to columns.  Comment below if you use this feature!

No comments:

Post a Comment