How to reverse transpose in excel how to#
Here we discuss how to use TRANSPOSE Formula in Excel along with practical examples and a downloadable excel template.The Excel TRANSPOSE function converts a vertical range of cells to a horizontal range of cells. This has been a guide to TRANSPOSE Formula in Excel. So when the values in source information change, the values do not get updated. You can use the copy and paste feature for Transpose, but it creates duplicate data.Instead, you should press CTRL+SHIFT+ENTER. Make sure you do not press ENTER after inserting the Transpose Formula because the Transpose function is an Array function.Make sure you select the same number of blank cells as the number of cells in source information while using the Transpose Formula.So after hitting the CTRL+SHIFT+ENTER, you will be able to see the result as below.Īs you can see, Raj and Vikas scored less than 50, so they are marked as FAIL. So we can use transpose Formula with IF condition like below.īut make sure not to press ENTER and press CTRL+SHIFT+ENTER. You want to transpose the information with the condition that those who score below 50 or absent should be marked as Fail. Suppose you have a report card of few students available in the Horizontal orientation. So let’s try to use this with the IF Condition formula. The transpose function can be used with other functions to get the desired results. So after hitting the CTRL+SHIFT+ENTER, you will be able to see the result as below.Basically, an array is a function that gets applied to more than one cell because of a selection of a range of more than one cell. The reason for doing this is because TRANSPOSE is an array function, and this is how an array function ends and not by pressing ENTER. So the last step in this function is to press CTRL + SHIFT + ENTER.After selecting the range, don’t press ENTER because ENTER will not work with this function. The next step is to select the range of the original cells for which you need to change the orientation.Please refer to the below screenshot on how to do this. Now be careful with this step because you need to insert the TRANSPOSE Formula in Cell A6 while all the blank cells still selected.As you can see in the below screenshot, I have selected 24 blank cells in the Vertical direction. So in our example, we need to select 24 cells in the opposite direction. You need to first select some blank cells in the other direction as the original set of cells and make sure to select the same number of cells.Please follow the below steps to use this function. We will see the same example as above and try to use the TRANSPOSE formula in Excel. So Transpose function is more useful than the copy-paste feature. In the copy-paste feature, the values will not change if the source information changes. This is the main difference between using the Transpose function and using the Transpose with copy and paste feature.
Whenever the source information changes, the values will also change in the Transpose Formula. It must be entered as an array formula in a range that has the same number of rows and the same number of columns as in source data. The transpose function helps to convert vertical orientation into horizontal orientation and vice versa. You will need to be a little careful while using the function. So to avoid that, we can use the TRANSPOSE function in excel. So we have seen how to swap orientation with the help of the copy and paste feature, but there is a risk of creating duplicated data.
Also, there is an additional option for Skip Blanks and Transpose.