Excel’s Transpose Function – Syntax, Uses and Troubleshooting
Contents
The TRANSPOSE function inverts rows and columns.
If you have ever had your data laid out and then realised it would be best the other way around:
This is the function for you!
It’s one of the best ways to control your data, which is a key part of our Excel courses.
Excel Transpose Function
To change the layout of your data set from horizontal to vertical or from vertical to horizontal without re-typing the data manually, you can use the TRANSPOSE Function.
The syntax of the TRANSPOSE Function is:
=TRANSPOSE(array)Where array is required and is the range or array whose orientation you’d like to rotate.
Using TRANSPOSE – Simple Example
Let’s look at an easy example to see the TRANSPOSE Function in action.
We have the following data set:
It shows the stages in a mobile app development process and the team leader responsible for each stage.
We have made the headers stand out using font formatting.
To change this data set from a horizontal to a vertical layout, we need to do the following.
1) Select the cell, you would like to be the upmost left cell in your range. In this case, it is cell A4.
2) Enter the following formula:
=TRANSPOSE(A1:I2)This is the array/range that we would like to transpose.
3) Press Enter. The results will spill over to give you the following.
This is a great tool to have when you have been importing data into Excel from other sources.
TRANSPOSE Function Use Cases
- When you need your data to be transposed and updated as the source data changes.
- You need the layout switched without the formatting transferred with it.
- Your data, that you need to transpose, is in an Excel table.
A great idea after transposing your data is to use simple borders to help highlight key sections.
Troubleshooting With The Transpose Function
Blanks In The Data Set
If your original data set contains blanks, then the TRANSPOSE function will add a zero instead of a blank when it rotates the data set.
To solve this issue, you will need to use the IF Function in combination with the TRANSPOSE Function.
Encountering A #VALUE! Error
If you’re using an older version of Microsoft Excel and haven’t pressed CTRL-SHIFT-ENTER once you’ve entered your formula, then you will get a #VALUE! error.
In this case, repeat the steps needed for older versions of Excel, only this time ensure that you press CTRL-SHIFT-ENTER.
Only One Cell In The Data Set Is Copied
If you’re using an older version of Microsoft Excel and you do not select the entire destination range first but only one cell and then press CTRL-SHIFT-ENTER. Only one cell will be copied.
The solution to this is to follow the steps correctly and select the entire destination range first.
Selecting The Incorrect Destination Range Dimensions
If you’re using an older version of Excel and you do not select the correct destination range dimensions.
For example, if your original range was 2 rows by 9 columns, and you select 5 rows by 2 columns, instead of 9 rows by 2 columns all your data will not be copied.
In this case, verify that you selected the correct destination range, in terms of dimensions.
When Transposing Data, you will often be left with redundant data. Highlighting duplicate values can show you where they are!
Conclusion
Rearranging Excel spreadsheet data, manually, can be very time-consuming and error-prone.
The TRANSPOSE Function is a great tool to learn, learn more time saving tools on our Excel courses!
In this post, you have learned how to use the Excel TRANSPOSE Function to switch the orientation of data in order to save time and reduce errors.
