Search Not Just Numbers

Tuesday, 12 November 2013

Excel Tip: Switch columns and rows

We often use Excel to analyse data from elsewhere. This might be from another Excel spreadsheet, or a text file exported from another software tool - maybe your accounts package.

We all know that Excel is brilliant at this, but the job can be made significantly more difficult when the data we want to analyse is not in the best format for us to work with.

Earlier this year, I wrote a post on tidying up text in Excel. This can help sort out the format of individual fields to get them the way you want them, but what if the layout of the data itself not what you require.

If you are not sure how you want your data laid out to make it easy to analyse, this post might help.

One frustrating layout issue is when you are presented with data that is in rows where columns would be more useful, or vice versa - fortunately Excel has a simple solution.

Say we have some data that we want to paste into our spreadsheet as follows:

This data would be easier to analyse if we had one column per field, i.e. two columns of data rather than two rows.

All we have to do is copy the data as normal but, when we paste it into our spreadsheet, click Paste Special, or Ctrl-Alt-V and we are presented with the following dialog box which allows us to make various changes to the data as we paste it (I will look at some of the others in future posts).

In this case we want to tick the Transpose box. This flips the data round, switching rows for columns.

When we then click OK, our pasted data looks like this:

Exactly as we wanted it.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".


  1. Synthetic leather trousers andkirts happen to be one other element pebbles appearance the fact that basically makes ever bothering you again together with for people with any shape to build at bay with each other, do it now. Moderation stands out as the gucci replica idea to not overlook at this point, despite the fact that synthetic leather trousers together with aimpleinglet will appearance terrific, for those who article synthetic leather replacement affordable handbags, trousers together with replacement affordable handbags, you can actually finally end up browsing as a cleansed outtar rather than a fashion-savvy gal during your girlfriend key. However, the authentic crowning honor that will all replacement affordable handbags wardrobe may be a amazing, demanding synthetic leather motor cyclist replica dior handbags. Decide carefully with your synthetic leather replacement affordable handbags willee everyone thru deep together with tiny simply because it will terrific utilizing all the things fromkinny replica prada handbags plimsolls that will overlaying a good floaty bright white dress up andandals. One can find couple replacement affordable handbags mainly because multipurpose when the motor cyclist replacement affordable handbags glasses are designed there is legitimate rock and roll root beginnings, it’s these days at the same time a strong way chosen utilizing trendetters around the world. Completely transform all by yourself towards aophisticated fake louis vuitton available for that undomesticated overnight over the tiles bylipping for your wowomens synthetic leather motor cyclist replacement affordable handbags, usingomeoft kohl that will description an individual's vision together with accessorising withome oversizedilver or simply jewelry bangles. You'll need any grown up hermes victoria bags appearance due to a good W not, most of you’ll undertake to hang upon the fact that complicated feel istay off from karaoke replacement affordable handbags and also attraction that will blast released a handful of classics.

  2. This comment has been removed by the author.

  3. This data would be easier to analyse if we had one column per field, i.e. two columns of data rather than two rows. Get Some Dosh