How To Transpose Data in Excel
Excel TRANSPOSE function, is used to transfer data from rows to columns or vice versa from columns to rows. Copying or transmission of data can be performed using Excel TRANSPOSE function/formula, or by using the dialog window for the Copy/Paste - Special/Transpose
The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows.
Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.
The TRANSPOSE function syntax has the following argument:
array argument => Required. An array or range of cells on a worksheet that you want to transpose. The Transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.
I will here, show some examples of how we can use the TRANSPOSE function.
Copy and Transpose data from Column to Row
Take for example that in column 'A' we have 30 or 50 names or other data. The data in column 'A', we want to copy the first row horizontally, I will show you an example of three data/names. (see image below).
First you need to do is the following: You need to know the exact number of data (names) that you want to transfer from a vertical column in a horizontal row and select the number of cells in a row based on the number of data. In the picture below, I selected the three cells in the first row because I have three data in column 'A'.
So, after you have selected a number of cells identical to the number of data in column 'A', press the F2 key on your keyboard and type the following formula:
and then press CTRL+SHIFT+ENTER (the so-caled "CSE" formula). Given that this is a range of cells this formula we must end up being ARRAY formula.
Now you have this situation as shown below. So, all the names from the vertical position of the column is copied in a horizontal position in the row. After the information is copied, the source column 'A' we can hide.
ATTENTION! In this case, when we use the formula and Excel TRANSPOSE function, we must not try to delete the column 'A', as the respective column data source.
Move and Transpose data from Column to Row
In this second Excel example, I will show you how we can permanently move data from the vertical position (from column) in the horizontal position (in the row). For this example I will use the dialog box Copy/Paste => Special/Transpose. The procedure is as follows: Select the all the data in column 'A'. Then right-click the cell is selected, then click on the pop-up menu on the Copy command.
After copying, you need select the first cell in the row in which you want to move and transfer data from the column and click on the respective cell with the right mouse button. From the pop-up menu, choose Paste Special command and then click the Transpose.
After such transfer of data from a column in the row, we can delete the original data in column 'A'
Move and Transpose multiple columns data from range to another place
In the following Excel example, I'll show you how we can copy data from multiple cell ranges, so that we all data were in a vertical position on the destination line to be in a horizontal position. You notice a range of cells and the data in the image below. I painted the numbers to make you easier to understand the problem.
So, select all the cells in the range, and right-click on a destination first cell where you want to transfer and transpose data. From the pop-up menu, choose Paste Special command.
Now you have an open dialog window Paste Special, in this dialog box you check the box Transpose, then click on the OK button. (see image below).
The result of the above-mentioned actions is as shown below. You notice the numbers 1,2,3,4 in column 'A' and the end result TRANSPOSE functions, in the first row 'G1:J1'. After this transfer of data from multiple columns in the multiple rows, we can safely delete the data in columns 'A:E'.