On the second sheet in the example workbook, you’ll see a table that contains a list of names and scores.
If you’re not sure why you’d want to format a series of data as a table in Excel, keep an eye out—we’ll be talking about it in an upcoming post!
If you select part of the table, you can use the Paste > Transpose option. If you want to transpose the entire table, however, that option won’t be available:
Instead, you’ll need the TRANSPOSE function.
The first thing to do when using the TRANSPOSE function is a bit different from other functions; you need to select a range of cells that matches the exact size of the table you’re transposing.
For our example, we’re transposing a table of 2 columns and 11 rows, so we’ll select an area of 11 columns and 2 rows:
After you’ve selected the cells, and without clicking into a specific cell, start typing the formula with “=TRANSPOSE(“:
Then, fill the array parameter by clicking and dragging to select the table:
Close off the parentheses, but don’t hit Enter. Remember, because this is an array function, we need to hit CTRL + Shift + Enter instead.
If you hit Enter, you’ll get a #VALUE! error. The right key combination, however, results in a successful transposition:
As a side note, you’ll know that a formula has successfully been run as an array formula if there are curly brackets around it in the formula bar.
The table has now been transposed, and it will still behave as a table.
Try changing a value in the original data—you’ll see that it’s automatically updated in the transposed range.
This is useful when you’re creating reports or transposing a range of data onto a different sheet in your workbook. If you use Paste > Transpose, this won’t happen.
One thing to be aware, of though.
Transposing with absolute references
When you transpose, either using Paste > Transpose or the TRANSPOSE function, cell references and formulas will be updated.
In most cases, you’ll have relative references in your cells and formulas.
But that’s not always going to work out how you want it to. Let’s take a look at an example.
On the third sheet of the example workbook, we have a very simple table of money owed, an interest amount, and an extra fee.
The Total column contains the amount owed, plus interest, plus the extra fee from cell G1.
Let’s try transposing that by copying and pasting.
As you can see, the totals don’t match up. If you click on one of the totals, you’ll see that instead of adding the extra fee from cell G2, it’s trying to add it from cell A13.
This is because of the relative cell reference.
Instead of interpreting the SUM command as adding cell G1 for the extra fee, Excel instead interprets it as summing the cell that’s three to the right and one up, or three to the right and two up, and so on.
When we transposed the data, we also transposed the reference.
So instead of looking at G1, our new totals are looking at A13.
Fortunately, there’s an easy way to fix this: with absolute references.
Let’s go back to the original Total column and make some adjustments. The first total is derived using this formula:
By changing G1 to $G$1, we can tell Excel that we mean that specific cell, and not the cell three to the right and one above the cell we’re working with.
After updating each of the formulas in the Total column to contain absolute references, we can try transposing again:
As you can see, it worked this time. Excel knows that our extra fee is in cell G1, and that it’s always going to be in G1.
You don’t need to worry about relative and absolute references using the TRANSPOSE function, because it automatically pulls the values from the previous location.
To see how this works in this particular case, we can use the TRANSPOSE function to transpose these columns into rows.
Use the method above to transpose this data with the TRANSPOSE function (I placed it just below the copied and pasted version).
Now, try changing one of the interest values in the original data. I’ll change the 0.07 to 0.09 and hit Enter.
The totals have now changed in the original data as well as the new data created by the TRANSPOSE function.
Notice that the values didn’t change in the data that was copied and pasted. This is because transposing with this method only copies values.
Try changing the extra fee to see what happens to the values in both tables!
Now that you’ve seen a few ways to transpose your data in Excel and you know what to watch out for when you do it, you’re ready to turn rows into columns and vice versa at will!
Just remember that if you’re getting strange results, there’s a good chance you’re using a relative reference where you should be using an absolute one. That should be the first thing you check.