When you work with Excel, sometimes you may encounter data in rows, which you want to present in columns and vice versa. Copying or writing it cell by cell would be very tedious and error-prone. There is a better way you can use achieve this. TRANSPOSE is a quite unknown feature that sometimes can be very useful.
What is Transposing?
Transposing is a fast and easy way to switch rows with columns and columns with rows.
Copy and Transpose
In order to transpose a table. First, you need to select the data you want to use. Look at the following example.
Select cells from B2 to G6 and then copy these cells (Ctrl + C). Now, right-click a cell where you want to insert transposed data. From the contextual menu select the Transpose (T) icon.
As you can see, after transposing, the days have been switched with names.
This time, after copying the cells we will also use the contextual menu, but this time let’s choose Paste Special. Alternatively, you can use Ctrl + Alt + V.
This will open a new window.
Check the Transpose checkbox and click OK.
It will create the same result as before.
You can only transpose when you use the copy function. You won’t be able to do this when you cut data.
The above methods are fast and easy, but they have one problem. When you change a value of a cell inside source data, the data that is transposed won’t update.
So, here, we can use something called the TRANSPOSE function. This function will switch rows and columns in the same ways as methods that we used earlier, but this time it will change the transposed data each time the source data is modified.
Using this function is a bit tricky. I will show you how you can use it, but first, take a look at the following example. This data will be transposed using the TRANSPOSE function.
Here, you have 3 columns and 2 rows. When you transpose this data you will get 2 columns and 3 rows.
Select the data and enter =TRANSPOSE(B2:D3
Use the Ctrl + Shift + Enter shortcut to transpose table. It will give the following result.
You can’t use just Enter because you will get the #VALUE error.
When you use the TRANSPOSE function, the formatting won’t be copied.
If one of your cells is empty, or you delete the value, in the transposed data you will get 0.
Adding Transpose to Quick Access Toolbar
If you use transpose feature quite often, you can add it to Quick Access Toolbar. Go to FILE >> Options >> Quick Access Toolbar.
Now, copy cells from B2 to D3, move your cursor to cell B6 and press the button.
This will transpose the data the same way as using Copy >> Transpose from the contextual menu.