Quick and simple ways to convert row to column in Excel. read on to know how.
It might happen that you are working on a sheet where you have some values arranged as rows and you want to convert the text in rows to columns in Excel as shown in the picture below.
Excel allows you to do this manually or automatically in several ways depending on your purpose.
Let us learn how to convert rows to columns in Excel.
How to Convert Row To Column in Excel – The Basic Solution
The easiest way to do it is via the Paste Transpose option. Here is how:
- Select and copy the needed range
- Right-click on a cell where you want to convert rows to columns
- Select the Paste Transpose option to rotate rows to columns
You can also use the Paste Special option and mark Transpose using its menu.
How to change row to column in Excel with TRANSPOSE
TRANSPOSE is the Excel function that allows you to switch rows and columns in Excel. Here is its syntax:
- array – an array of columns or rows to transpose
The benefit of TRANSPOSE is that it links the converted columns/rows with the source rows/columns. So, if the values in the rows change, the same changes will be made in the converted columns.
TRANSPOSE formula to convert multiple rows to columns in Excel desktop
TRANSPOSE is an array formula, which means that you’ll need to select a number of cells in the columns that correspond to the number of cells in the rows to be converted and press Ctrl+Shift+Enter.
TRANSPOSE formula to rotate row to column in Excel Online
Insert your TRANSPOSE formula in a cell and hit enter. The rows will be rotated to columns.
Excel TRANSPOSE multiple rows in a group to columns between different workbooks
To use TRANSPOSE to rotate rows to columns between different workbooks, do the following:
- Copy a range of columns to rotate from one spreadsheet and paste it as a link to another spreadsheet.
- Copy the URL path.
- The URL path above is for a cell, not for an array. So, you’ll need to slightly update it to an array to use in your TRANSPOSE formula. Here is what it should look like:
=TRANSPOSE(‘https://d.docs.live.net/ec25d9990d879c55/Docs/Convert rows to columns/[dataset.xlsx]dataset’!A1:D12)
Follow the same logic for rotating rows to columns between workbooks on an Excel desktop.
Maximize Your Online Business Potential for just ₹79/month on Lio. Annual plans start at just ₹799.