Do you have some values arranged as rows in your workbook, and you want to convert text in rows to columns in Excel like this?
In Excel, you can do this manually or automatically in multiple ways depending on your purpose. Read on to get all the details and choose the best solution for yourself.
How to convert rows into columns in Excel – the basic solution
The easiest way to convert rows to columns in Excel is via the Paste Transpose option. Here is how it looks:
- 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
As an alternative, you can use the Paste Special option and mark Transpose using its menu.
This is the best way for a one-time conversion of small to medium numbers of rows both in Excel desktop and online.
Can I convert multiple rows to columns in Excel from another workbook?
The method above will work well if you want to convert rows to columns in Excel between different workbooks using Excel desktop. You need to have both spreadsheets open and do the copying and pasting as described.
However, in Excel Online, this won’t work for different workbooks. The Paste Transpose option is not available.
Therefore, in this case, it’s better to use the TRANSPOSE function.
How to automatically convert rows to columns in Excel
To rotate rows to columns automatically or dynamically, you can use one of these options:
- Excel TRANSPOSE function
- VBA macro
- Power Query
Let’s check out each of them in the example of a data range that we imported to Excel from a Google Sheets file using Coupler.io.
Coupler.io is an integration solution that synchronizes data between source and destination apps on a regular schedule. For example, you can set up an automatic data export from Google Sheets every day to your Excel workbook.
Check out all the available Excel integrations.
Convert multiple rows to columns in Excel formula
TRANSPOSE is the Excel function that allows you to rotate rows to columns and columns to rows. Here is its syntax:
array– an array of columns or rows to transpose
One of the main benefits 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.
Note: TRANSPOSE is not the only function you can use to rotate rows to columns. A combination of INDIRECT and ADDRESS functions can be considered as an alternative, but the flow to convert rows to columns in Excel using this formula is much trickier.
TRANSPOSE formula example for 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.
Fortunately, you don’t have to go through this procedure in Excel Online.
TRANSPOSE formula example for Excel Online
You can simply insert your TRANSPOSE formula in a cell and hit enter. The rows will be rotated to columns right away.
Excel TRANSPOSE multiple rows in a group to columns between different workbooks
We promised to demonstrate how you can use TRANSPOSE to rotate rows to columns between different workbooks. In Excel Online, you need to do the following:
- Copy a range of columns to rotate from one spreadsheet and paste it as a link to another spreadsheet.
- We need this URL path to use in the TRANSPOSE formula, so copy it.
- 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)
You can follow the same logic for rotating rows to columns between workbooks in Excel desktop.
Convert multiple rows to columns in Excel using VBA macro
VBA is a superb option if you want to customize some function or functionality. However, this option is only code-based. As an example, we’ll use the following script that allows you to automate converting rows to columns:
Sub TransposeColumnsRows() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8) Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8) SourceRange.Copy DestRange.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False End Sub Sub RowsToColumns() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Application.InputBox(Prompt:="Select the array to rotate", Title:="Convert Rows to Columns", Type:=8) Set DestRange = Application.InputBox(Prompt:="Select the cell to insert the rotated columns", Title:="Convert Rows to Columns", Type:=8) SourceRange.Copy DestRange.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False End Sub
- Go to the Visual Basic Editor (VBE). Click Alt+F11 or you can go to the Developer tab => Visual Basic.
- In VBE, go to Insert => Module.
- Copy and paste the script into the window.
- Now you can close the VBE. Go to Developer => Macros and you’ll see your RowsToColumns macro. Click Run.
- You’ll be asked to select the array to rotate and the cell to insert the rotated columns.
- There you go!
Convert rows to column in Excel using Power Query
Power Query is another powerful tool available for Excel users. We wrote a separate Power Query Tutorial, so feel free to check it out.
Meanwhile, you can use Power Query to transpose rows to columns. To do this, go to the Data tab, and create a query from table.
- Then select a range of cells to convert rows to columns in Excel. Click OK to open the Power Query Editor.
- In the Power Query Editor, go to the Transform tab and click Transpose. The rows will be rotated to columns.
- If you want to keep the headers for your columns, click the Use First Row as Headers button.
- That’s it. You can Close & Load your dataset – the rows will be converted to columns.
Error message when trying to convert rows to columns in Excel
To wrap up with converting rows to columns, let’s review the typical error messages you can face during the flow.
The overlap error occurs when you’re trying to paste the transposed range into the area of the copied range. Please avoid doing this.
Wrong data type error
You may see this #VALUE! error when you implement the TRANSPOSE formula in Excel desktop without pressing Ctrl+Shift+Enter.
Other errors may be caused by typos or other misprints in the formulas you use to convert groups of data in rows to columns in Excel. Always double check the syntax before pressing Enter or Ctrl+Shift+Enter 🙂 . Good luck with your data!Back to Blog