How to transpose data in Microsoft Excel

Updated: 04/30/2020 by Computer Hope

When working with data in a Microsoft Excel spreadsheet, you may need to convert, or transpose, data in columns to rows, or rows to columns. Transposing data is useful to present data in a different layout or copy data from a spreadsheet to another program requiring another layout. For example, you may have monthly sales data in column format, with the month names in one column and sales amounts in a second. You can transpose the monthly sales data into two rows instead of columns.

To transpose data in Excel, select the direction to transpose data below and follow the steps.

• Transpose data from column to row.
• Transpose data from row to column.

Transpose data from column to row

To transpose data in Excel from column to row, follow the steps below.

1. In the Excel spreadsheet, select the cells containing to data you want to transpose. The data should be contained in one or more columns.

• How to select one or more cells in a spreadsheet program.

2. Press Ctrl+C on your keyboard to copy the selected cells.
3. Using your mouse, select the cell where you want to convert the column data to row layout. You can select a cell in the current worksheet or a new worksheet contained in the same spreadsheet file. In our example, we are selecting the cell in row 10, column A.
4. Right-click the selected cell and select the Paste Special option.

5. In the Paste Special window, click the box for the Transpose option, then click OK.

6. The data is pasted in one or more rows, depending on the number of columns you selected. For example, if there are two columns of data, transpose converts the data into two rows of data.

Transpose data from row to column

To transpose data in Excel from row to column, follow the steps below.

1. In the Excel spreadsheet, select the cells containing to data you want to transpose. The data should be contained in one or more rows.

• How to select one or more cells in a spreadsheet program.

2. Press Ctrl+C on your keyboard to copy the selected cells.
3. Using your mouse, select the cell where you want to convert the row data to a column layout. You can select a cell in the current worksheet or a new worksheet contained in the same spreadsheet file. In our example, we are selecting the cell in row 7, column A.
4. Right-click the selected cell and select the Paste Special option.

5. In the Paste Special window, click the box for the Transpose option, then click OK.

6. The data is pasted in one or more columns, depending on the number of rows you selected. For example, if there are two rows of data, transpose converts the data into two columns of data.