Back when I was talking about how to transpose, you may have noticed that I was only talking about transposing values, not formulas. Transposing formulas can be somewhat more tricky, and I mention this now because when I want to transpose formulas, it’s usually incremental formulas.
If you want to turn a column into a row, and the formulas in the column only have references to other rows, you’re good. A reference to one cell below will become one cell to the right, and one cell above will become one cell to the left. Likewise, formulas in a row that only refer to other columns will be fine, but if they refer to other row they’ll break if you try to transpose them. They’ll also break if they refer to cells too far outside of the destination range.
For example, imagine you have a list of people, and you want a bunch of tables with a bunch of statistics about these people. For some of the tables, there’s more people than there are statistics so you want one person per row. For others, you have lots of statistics about each person, so you want the statistics in the rows, and the people in the columns.
Fortunately there’s a fairly simple trick to fool the spreadsheet into doing what you want IF you’re only referring to cells in a different column/row than the one you’re trying to transpose from.
In Microsoft Excel (all versions) and Open Office Calc:
- Select the area with the formulas you want to transpose.
- Find all instances of “=” in the selected area and replace them with “zzz”.
- Copy the selected area.
- Select the top left cell of the area you want to transpose to.
- Paste-special transposed.
- Select the area you have just pasted the formulas into.
- Find all instances of “zzz” in the selected area and replace them with “=”.
It sounds pretty simple, but I honestly didn’t come up with it until recently. (I’ll share the project that made me come up with it later.) Until then I had been manually, tediously typing the formulas in, which basically meant I avoided doing it whenever possible.
In Google Spreadsheet:
You’ll note that I said this technique only works in Excel and Open Office. That’s because Google Spreadsheet’s =transpose() function doesn’t care whether it’s pulling from cells that contain values and formulas. That’s because it’s not actually transposing the formulas themselves, it’s only reflecting the results of the formulas. If you ever erase the original cells, the transposed cells will be affected.
It’s also because, quite simply, the technique doesn’t work in Google. I tried. It kind of…broke it.
The first hurdle is Google Spreadsheet’s “no replacing in formulas” rule, but there’s easy ways around that. One option is to concatenate something that looks like the formula, instead of the formula itself. For example, if you wanted a2=a1+1, and a3=a2+1, then normally you’d just put =a1+1 in a2, and copy that down to a3. So what you’d do in this case is have a2=concatenate(“a”,row()-1,”+1″). Since the formula is in row 2, and row() just gives the number of the current row, the result of that would be: a1+1. If you copied it down to a3 then it would be in row 3, and so the result would be a2+1. Then you’d just copy, and paste as values, whereupon you could presumably start following the regular procedure at step 2.
Unfortunately, it… doesn’t work. And I can’t tell you why. There weren’t any error messages when I replaced “a” with “=a”, but there weren’t any results, either. The cells were blank. So I tried copying the newly created formulas and pasting them in another row, and they did have values, but the values were all one column to the right of where they ought to be. So in increasing bafflement I tried many other things, all of which came up with different results, none of which were correct. So I can only say that this method breaks the spreadsheet, and advise you to avoid it.