Switching rows and columns

There’s a third kind of “paste special” that I haven’t discussed yet — transposing. When you paste transposed, you’re switching the rows and columns with each other. The cell in C1 (3rd column, 1st row) swaps places with cell A3 (1st column, 3rd row), and cells that are along the diagonal (A1, B2, C3, etc.) stay where they are.

There’s a number of reasons you might want to do this, but the most obvious one is when you have only a few rows, but lots of columns. Unless your cells are only one character wide, they’re probably going to be wider than they are tall. Even with the shape of the average monitor this means you’re probably going to be able to see more rows than columns at any given time. For example, using the default cell sizes, my monitor shows 33 rows, and only 15 columns. So if I had a little information about a lot of things (say, for example, the average individual and household incomes for all 50 counties in NC) I could see all the data for only 15 items at once if they were the columns, but I could see it for 33 of them at once if they were the rows.

So, if you’ve decided you want to transpose something, how do you do it?

First off, many programs won’t let you paste in an area that overlaps the area you’re copying from. I recommend inserting a blank worksheet and pasting the transposed information in that.

In Excel 97:

  1. Click on the “Edit” menu, or type “alt-e”.
  2. Click on “Paste Special” or type “s”.
  3. In the menu that pops up, click on the checkbox next to “Transpose” or type ‘e’. The Transpose option is near the bottom-right, just above the buttons.
  4. Click on the “OK” button, or hit enter.

Note: In Excel 97, it’s possible to combine transpose with other special pasting options like “Value”.

In Excel 2007:

  1. In the Home tab, look at the “Clipboard” group. For me it’s the first one on the left.
  2. Below the graphic of the clipboard and the word Paste, there’s a downward-pointing arrow. Click on it.
  3. In the menu that pops up, click on “Transpose”.

Note: If you follow the keyboard shortcut for Excel 97, you’ll get Excel 97’s menu options, which will allow you to select whether you want to paste as just values, etc.

In Google Spreadsheet:

  1. In the upper left cell of the area you want the transposed cells in, enter the formula =transpose(range) where ‘range’ is the set of cells you’re transposing from. It will automatically enter a set of formulas that pull the data from the original range.
  2. Copy the set of formulas
  3. In the same location, paste special as values.

Note: There seems to be no way in Google Docs to transpose cells and retain the formulas in them. You can only retain their values. If you do not paste as values after doing the transpose function then anything you do to the original data — including deleting it — will be reflected in the transposed data.

In OpenOffice Calc:

  1. Click “Edit”
  2. Click “Paste Special…”
  3. In the window that pops up, click on the checkbox next to “Transpose”.
  4. Click on OK.

Note: In OpenOffice, it’s possible to combine transpose with other special pasting options like “Value”.

In Microsoft Works Spreadsheet: Not possible to do automatically.