Converting a column of data into a row

If you have a column of data and want to instead display this across a single row, you can do so via the Paste Special tool.

First, copy your column (CTRL+C or via the Copy button). Then select the leftmost cell into which you want your row of data to appear. On the Home ribbon, hit the little down-arrow beneath the Paste button and hit Transpose. The data will be pasted across that row.

If you simply want the values (rather than the formulae), instead of hitting Transpose, hit Paste Special… and further options will allow you to combine a pasting of values with the transpose function.

If you need to copy formulae while retaining their reference points, this takes a little trickery. First, switch to formula view by hitting CTRL+` (the key above the tab key). Highlight your column of formulae and do a find and replace of "=" with "#=". This will replace the "=" at the beginning of each formula with "#=", and will mean that Excel no longer regards these as formulae.

Now do your Copy/Transpose as detailed earlier in this post. And now highlight your newly transposed data and do a find and replace of "#=" with "=". Toggle back to values view by hitting CTRL+`, and you're done.

Cute.

• Tweet