The TRANSPOSE function, one of Excel's Lookup & Reference functions, is used to copy data located in a row into a column or copy data located in a column into a row.
The syntax for the TRANSPOSE function is:
{ = TRANSPOSE ( Array )}
Array - the range of cells to be copied from the horizontal to the vertical or vertical to the horizontal.
The curly braces " { } " surrounding the function indicate that it is an array function.
Example: Using Excel 's TRANSPOSE Function
For help with this example, see the image above.
- Enter the following data into cells:
B1 - cabbages, B2 - carrots, B3 - radishes
- Drag select cells D1 to F1 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose Lookup & Reference functions > TRANSPOSE from the ribbon to bring up the
function's dialog box.
- Drag select cells B1 to B3 on the spreadsheet.
- Press the CTRL , SHIFT , and ENTER keys on the keyboard at the same time to enter the function as an array function.
- The data in cells B1 to B3 should appear in cells D1, E1, and F1.
- When you click on any of the cells D1 to F1 the complete function
{ = TRANSPOSE ( B1 : B3 )} appears in the formula bar above the worksheet.
Other Excel Lookup Functions
