Home » Misc » How to convert column into rows in excel

How to convert column into rows in excel


Transpose (rotate) data from rows to columns or vice versa

Excel

Formulas and functions

Reference

Reference

Transpose (rotate) data from rows to columns or vice versa

Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

If you have a worksheet with data in columns that you need to rotate to rearrange it in rows, use the Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa.

For example, if your data looks like this, with Sales Regions in the column headings and and Quarters along the left side:

The Transpose feature will rearrange the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left, like this:

Note: If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first, or you can use the TRANSPOSE function to rotate the rows and columns.

Here’s how to do it:

  1. Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C.

    Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.

  2. Choose a new location in the worksheet where you want to paste the transposed table, ensuring that there is plenty of room to paste your data. The new table that you paste there will entirely overwrite any data / formatting that’s already there.

    Right-click over the top-left cell of where you want to paste the transposed table, then choose Transpose .

  3. After rotating the data successfully, you can delete the original table and the data in the new table will remain intact.

Tips for transposing your data

  • If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative, absolute, and mixed references before you rotate the data.

  • If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so that you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.

You can paste data as transposed data within your workbook. Transpose reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.

Here's how you can transpose cell content:

  1. Copy the cell range.

  2. Select the empty cells where you want to paste the transposed data.

  3. On the Home tab, click the Paste icon, and select Paste Transpose.

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Transpose data from rows to columns (or vice versa) in Excel for Mac

Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac More...Less

If you have a worksheet with data in columns that you want to rotate so it’s rearranged in rows, you can use the Transpose feature. It lets you rotate the data from columns to rows, or vice versa.

For example, if your data looks like this, with sales regions listed along the top and quarters along the left side:

You can rotate the columns and rows to show quarters along the top and regions along the side, like this:

Here’s how:

  1. Select the range of data you want to rearrange, including any row or column labels, and either select Copy on the Home tab, or press CONTROL+C.

    Note:  Make sure you copy the data to do this. Using the Cut command or CONTROL+X won’t work.

  2. Select the first cell where you want to paste the data, and on the Home tab, click the arrow next to Paste, and then click Transpose.

    Pick a spot in the worksheet that has enough room to paste your data. The data you copied will overwrite any data that’s already there.

  3. After rotating the data successfully, you can delete the original data.

  • If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative and absolute references before you rotate the data.

  • If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first by using the Convert to Range button on the Table tab, or you can use the TRANSPOSE function to rotate the rows and columns.

  • If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.

Transpose (rotate) data from rows to columns and vice versa

excel

Formulas and Functions

To work with links

To work with links

Transpose (rotate) data from rows to columns and vice versa

Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More. ..Less

If you have a table with column data that needs to be rotated in order to reorder it by row, use the Transpose function. Use it to quickly switch data from columns to rows and vice versa.

For example, if the data looks like this: "Sales regions" in the column headings and "Quarters" on the left side:

The Transpose function will relabel a table in which the "Quarters" columns are displayed in the column headings, and sales regions are shown on the left, for example:

Note: If the data is stored in an Excel spreadsheet, the Transpose function will not be available. You can first convert the table to a range, or you can use the TRANSPOSE function to rotate the rows and columns.

Here's how to do it:

  1. Select the range of data that you want to reorder, including row or column headers, and then press CTRL+C.

    Note: Be sure to copy the data to do this, as you won't be able to use the Cut command or CTRL+X.

  2. Select a new location at the location on the computer where you want to enter the transposition table to fit the data in sufficient space. The new table will completely reformat all the data and formatting that is already there.

    Right-click the top left cell where you want to enter the transpose table and select transpose .

  3. After a successful data rotation, you can delete the original table and the data in the new table will remain unchanged.

Tips for transposing data

  • If the data contains formulas, Excel automatically updates them to reflect the new location. Make sure these formulas use absolute references. If not used, you can switch between relative, absolute, and mixed references before rotating data.

  • If you want to frequently rotate data to view it from different angles, create with it by dragging fields from the row area to the column area (or vice versa) in the field list.

You can enter data into the book as a data transpon. Transpose: Reposition the contents of the copied cells when copying. Row data will be inserted into columns and vice versa.

Here's how to transpose the contents of a cell:

  1. Copy a range of cells.

  2. You select the empty cells in which you want to enter the transpose data.

  3. On the Home tab, click the icon Enter and select Enter transpose .

You can always ask the Excel Tech Community a question or ask for help in the Answers community.

Transpose data from rows to columns (or vice versa) in Excel for Mac

Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac More...Less

If you have a worksheet with data in columns that you want to rotate to arrange the rows, you can use the function Transpose . It allows you to rotate data from columns to rows and vice versa.

For example, if the data looks like the following, with sales by region at the top of the sheet and sales by quarter at the left of the sheet.

You can rotate columns and rows to show quarters at the top of the sheet and regions on the side.

The following describes how to do this.

  1. Select the range of data you want to change, including row or column labels, and click the Copy button on the Home tab, or press control+C.

    Note: Be sure to copy the data. This cannot be done using the Cut command or the CONTROL+X keys.

  2. Select the first cell in the range where you want to paste the data, and then on the Home tab , click the arrow next to Paste and choose Transpose .

    Select a location on the sheet where there is enough space to insert data. The copied data will replace any existing data.

  3. By rotating the data, you can delete the original data.