How to Use the Transpose Function in Excel

There may be times when you’ll need to switch the layout of your data set around.

In situations like these you can use the Excel transpose function!

You can learn more about basic and advanced functions in our Microsoft 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:

where array is required and is the range or array whose orientation you’d like to rotate.

How To Use The Transpose Function – A Simple Example

Let’s look at an easy example to see the TRANSPOSE Function in action.

• We will look at how to transpose data with the TRANSPOSE Function, using Microsoft 365 first. In this case, one is using the TRANSPOSE Function as a dynamic array formula.

We have the following data set, showing the stages in a mobile app development process and the team leader responsible for each stage.

So, 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:

This is the array/range that we would like to transpose.

3) Press Enter. The results will spill over to give you the following.

For more guides on Excel read our conditional formatting guide here.

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.

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’re selected the correct destination range, in terms of dimensions.

When Transposing Data you can be left with redundant data, and will want to highlight them all at once. Read our Ultimate Guide To Highlighting Duplicate Values here.

Conclusion

Rearranging Excel spreadsheet data manually, can be very time-consuming and error prone.

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.

Want another guide for Excel? Go here for a Simple Guide to Macros!