The TRANSPOSE function is used to copy data located in a row into a column or copy data located in a column into a row.

Here is a completely under appreciated and therefore, underutilized function in Excel.

The TRANSPOSE function is used to copy data located in a row into a column or copy data located in a column into a row.

This is an ARRAY function, so you will need to hit Ctrl + Shift + Enter after typing it in to add those curly braces “ { } “.

For example, enter the following data into cells:

B5 – apple , B6 – pear, B7 – banana.

Now highlight cells D5:F5 – the location where the results will be displayed.

 

Excel Transpose function. Copy data from columns to rows. Copy data from rows to columns. FIG Consulting Partners. Microsoft Excel Tips.

 

In the formula bar, type:  “TRANSPOSE(“ and select cells “B5:B7” to complete the formula.

Press Ctrl + Shift + Enter to enter the function as an array formula.

The data in cells B5:B7 should now appear in cells D5:F5.

When you click on any of the cells from D5:F5, the complete function:

{ = TRANSPOSE ( B5 : B7 )} appears in the formula bar above the worksheet.

 

Excel Transpose function. Copy data from columns to rows. Copy data from rows to columns. FIG Consulting Partners. Microsoft Excel Tips.