Using the Excel TRANSPOSE Function
SummaryThe TRANSPOSE function is used to rotate data from rows to columns or vice versa. For example, if you have names and scores organized horizontally, TRANSPOSE can be used to rotate them to a vertical layout. TRANSPOSE must be entered as an array formula.
Required. The range of cells or array within a worksheet that you wish to transpose.
Usage NotesThe TRANSPOSE function will convert data arranged horizontally to a vertical range of cells, or vice versa. Think of TRANSPOSE as "flipping" the orientation of a range of cells.
The first row of the array becomes the first column of the new array when using TRANSPOSE. The second row becomes the second column, the third row becomes the third column, and so on.
When using TRANSPOSE you must hit Ctrl + Shift + Enter when finishing the formula. Hitting only Enter, as you do with other formulas, will result in an error.
It is critical that the new array must have the same number of rows as the original array has columns, and the same number of columns as the original array has rows.
A Detailed ExampleThe TRANSPOSE function can be tricky for first time users. The following step-by-step guide will help you grasp how to use this function.
Step 1: Select blank cells (prior to entering =TRANSPOSE). Be sure that the range you select follows the rows and columns rule mentioned above.
In the above example, we start with 10 vertically arranged cells. For the function to work, we need to select 10 horizontally arranged cells.
Step 2: With the blank cells still selected, enter =TRANSPOSE(
You should see the following:
Step 3: Enter the range of cells you would like to transpose. Here we would like to transpose the range A3:B7. You can enter the range manually or use your mouse to select the range.
DON'T PRESS ENTER JUST YET
Step 4: Press Control + Shift + Enter all at once. When finishing an array formula, such as TRANSPOSE, you must hit Control + Shift + Enter at the same time. This is because an array formula is applied to more than one cell at a time.
You should see the following as your final output: