Transposing Microsoft Excel rows to columns and columns to rows

Excel data can be easier to interpret as rows or as columns depending on the context. Sometimes it is necessary to switch the orientation to make it easier to understand, more efficient to work with, or more presentable. An example could be that the annual sales figures have always been presented with stores across and years going down. Due to expansion there are now lots of stores and the table is too wide to print easily, so it makes more sense to present it with the years going across and the stores going down.

Select the cells you want to transpose from rows to columns, or columns to rows.

First, make sure you have a suitable place for the new data to go, as this task is achieved using copy and paste functions.

Select the cells that you need to transpose, which is Excel’s name for changing rows in to columns.

If you are using Microsoft Excel 2007 or Microsoft Excel 2010, on the Home tab, in the Clipboard  group, click Copy. If you are using Excel 2003 or earlier, click the Copy button on the Standard toolbar. Alternatively in any version, right-click and click Copy or press Ctrl + C.

Next, click an empty cell where the transposed data will go.

In the Home tab, on the ribbon, click the downwards arrow next to Paste and then choose the Transpose feature on the menu that appears. In Microsoft Excel 2007, it looks like:

In Microsoft Excel 2010, it looks like:

You need to select the 3rd button on the second row.

In Microsoft Excel 2003 or earlier, on the Standard toolbar, from the Paste dropdown, click Transpose.

Your data is copied with the rows becoming columns and the columns rows.

Find out more about our Microsoft Excel training London, UK or our Microsoft Excel training Sydney, Australia.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Office 2007 and 2010 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s