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. Continue reading Nifty trick: switching rows and columns without breaking formulas