TRANSPOSE function in excel is a lookup/reference function. This function is used to rotate or flip or switch cells and range of cells without duplicating the data. When copy and pasting of data is done, it leads to duplication of data, by using TRANSPOSE function this problem can be avoided.
- Array: It is an array or a range of cells that will be transposed. A vertical range is converted into horizontal range and a horizontal range into vertical range. First row of the array becomes first column in the result and first column becomes first row in the result.
- It helps in minimizing duplication of data.
- After entering the formula, always remember to enter CTRL+SHIFT+ENTER, otherwise errors will occur.
- This function must be entered in an array form.
- This function do not copy the formatting of the cells.
- A vertical range is converted into horizontal range and a horizontal range into vertical range.
In the following example, a table in columns A to G is given which is flipped to a row table using TRANSPOSE function using the following procedure:
- First select blank cells which are equal in number to the given data and with their rows and columns exchanged.
- Then enter TRANSPOSE formula and enter cell range in brackets.
- Then press CTRL+SHIFT+ENTER.