Transpose Data With Paste Special [1 Minute!]

Transposing is rotating or rearranging your data.

Paste Special Transpose is a simple way to change the orientation of your data.

This gives you much better control of your page layout!

Paste Special Transpose

We have a sample sales data set shown below.

Months are given in the column headings and flavours of jam are on the left side, in the rows.

This is a very simple data set, much like one we would use on our London-based Excel courses to help people practice what they’re learning.

We would like to switch the layout of this data set so that flavours of jam are given in the column headings and months are on the left side, in the rows.

1)  Select the data, which in this case is range A1:F5.

2)  Press Ctrl-C on your keyboard in order to copy.

3)  Right-click the cell which will be the upmost left cell, where the data with the new layout will be pasted.

In this case, it is cell A8. Select Paste Special…

4)  In the Paste Special Dialog Box, check Transpose and then click Ok.

5)  The layout of the data has now been changed.

You have now learned how to transpose in Excel using Paste Special in a minute or less.

Why Use Paste Special Transpose?

In most cases, it will involve data that is horizontally laid out that you’d like to change to a vertical layout.

It is most helpful, when you have a larger data set that you’d like to rearrange and doing it manually will take up time, and it’s a one-off task.

It is ideal for users who are just getting started with Excel, since it is a simple method to use and understand.

You can then add simple borders to your data to make it even more visually appealling!

Conclusion

Transposing data allows you to switch rows and column headings in your data set or change your layout from horizontal to vertical.

You can consequently speed up your workflow, when you need to rearrange data.

Special thank you to Taryn Nefdt for collaborating on this article!