Excel TRANSPOSE Formula (Table of Contents)
TRANSPOSE Formula in Excel
Sometimes the data is not available in the manner we want. This may be due to the system generating a report, or some people like to see the information in a different way you prefer. One example is when the information is available in a Horizontal manner, but you want to see the information in a vertical manner or vice-versa.
Microsoft Excel has an inbuilt function to tackle this issue which is known as “Transpose”. Transpose basically helps us to re-arrange the information in the manner we want. It helps us to swap or switch columns to row without re-typing the information.
There are different ways to use the Transpose Formula in Excel. Either you can use this with the Copy-Paste function, or you can use the Transpose Formula.
How to Use the TRANSPOSE Formula in Excel?
TRANSPOSE Formula in Excel is very simple and easy to use. Let’s understand how to use the TRANSPOSE Formula in Excel with the help of some examples.
Let’s look at the below example where I want to switch the information from Horizontal to Vertical.
Example #1
In this spreadsheet, I have given the cost and installation charges of some Electric appliances. The information is available in Horizontal Manner. The challenge in this report is I cannot filter the information. So I have to change the orientation of the report from Horizontal to vertical. So let’s see how we can do that.
Now to switch or swap the information from Horizontal to Vertical, we will need to follow the below steps.
- Select the range of information that you need to swap or switch. In this example, A1:F4.
- Copy the Table (CTRL+C) and go to another sheet (or anywhere in the excel sheet where you want the information) and Paste special values (CTRL+ALT+V).
- After the paste special values, you will see the below options.
- As you can see, there are multiple options for Paste & Operation. Also, there is an additional option for Skip Blanks and Transpose. In the paste area, there are two important options (Values and Formulas). Select Values if you just want to copy the values of data and select Formulas if you want to copy the data with Formulas.
- In this case, I am selecting Formulas because I need the data with formulas. So click on the Formula button.
- In addition to the formula button, click on the Transpose button as well. So that it can paste the data with formulas, and it can swap the information from Horizontal to vertical and then click on OK.
- Now you will see the information is pasted in a vertical manner.
Introduction to Transpose Function
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. So to avoid that, we can use the TRANSPOSE function in excel. You will need to be a little careful while using the function.
The transpose function helps to convert vertical orientation into horizontal orientation and vice versa. 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.
Whenever the source information changes, the values will also change in the Transpose Formula. This is the main difference between using the Transpose function and using the Transpose with copy and paste feature. In the copy-paste feature, the values will not change if the source information changes. So Transpose function is more useful than the copy-paste feature.
The syntax for the transpose Formula is
Example #2
We will see the same example as above and try to use the TRANSPOSE formula in Excel.
Please follow the below steps to use this function.
- 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. So in our example, we need to select 24 cells in the opposite direction. As you can see in the below screenshot, I have selected 24 blank cells in the Vertical direction.
- Now be careful with this step because you need to insert the TRANSPOSE Formula in Cell A6 while all the blank cells still selected. Please refer to the below screenshot on how to do this.
- The next step is to select the range of the original cells for which you need to change the orientation. In our example, the range is A1:F4. After selecting the range, don’t press ENTER because ENTER will not work with this function.
- So the last step in this function is to press CTRL + SHIFT + ENTER. 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. 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.
- So after hitting the CTRL+SHIFT+ENTER, you will be able to see the result as below.
TRANSPOSE function with IF Condition
The transpose function can be used with other functions to get the desired results. So let’s try to use this with the IF Condition formula.
Example #3
Suppose you have a report card of few students available in the Horizontal orientation. You want to transpose the information with the condition that those who score below 50 or absent should be marked as Fail.
So we can use transpose Formula with IF condition like below.
=TRANSPOSE(IF(A1:F3<50,”FAIL”,A1:F3))
But make sure not to press ENTER and press CTRL+SHIFT+ENTER.
So after hitting the CTRL+SHIFT+ENTER, you will be able to see the result as below.
As you can see, Raj and Vikas scored less than 50, so they are marked as FAIL.
Things to Remember
- Make sure you select the same number of blank cells as the number of cells in source information while using the Transpose Formula.
- Make sure you do not press ENTER after inserting the Transpose Formula because the Transpose function is an Array function. Instead, you should press CTRL+SHIFT+ENTER.
- You can use the copy and paste feature for Transpose, but it creates duplicate data. So when the values in source information change, the values do not get updated.
Recommended Articles
This has been a guide to the TRANSPOSE Formula in Excel. Here we discuss how to use TRANSPOSE Formula in Excel along with practical examples and a downloadable excel template. You can also go through our suggested articles –
