Paste data and transpose

Transposition in a spreadsheet editor is an operation that swaps rows and columns. Thus, the columns of the original table become the rows of the resulting table.

You can paste the copied data with transposition in one of the following ways:

–On the Home tab, in the Edit group, click the arrow to the right of the  Paste button. In the drop-down list, select Paste and transpose.

–To open the context menu, right-click the cell or cell range you want to paste into, and then select Paste special >  Paste and transpose.

When pasting with transposition, the formatting settings and data format of the copied cells are retained.

Pasting with transposition is a type of pasting with parameters, so its behavior follows the general principles of pasting, which apply when the pasted range intersects or completely matches an object on the sheet. Please note the following specifics of pasting with certain types of objects:

–Table: If the result of pasting falls within the range of a table, the data in it is replaced. In this case, the style of the table is not reset. When you paste a table, only the values are pasted.

–Pivot table: You can't paste data into pivot table cells. When you paste a pivot table, only the values are pasted.

–Shape: If the result of the pasting falls within the area taken up by the shape, the data is pasted under the shape.

–Chart: If the result of the pasting falls within the area taken up by the chart, the data is pasted under the chart.

–Notes: Notes are retained after transposition.

–Merged cells: Transposed in the same way as regular cells.

–Cells with data validation: If the result of the pasting falls within cells with data validation, the data in those cells is overwritten. If cells with data validation are pasted, the validation is retained after transposition.

Standard formulas and array formulas located in the selected area are also transposed. At the same time, absolute references ($A$1) remain unchanged, while relative references (A1, B2) are changed according to the “rows ⇄ columns” rules.