Do you have some values arranged as rows in your workbook, and do you want to convert text in rows to columns in Excel like this? Show
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. Table of Contents How to convert rows into columns or columns to rows in Excel – the basic solutionThe easiest way to convert rows to columns in Excel is via the Paste Transpose option. Here is how it looks:
As an alternative, you can use the Paste Special option and mark Transpose using its menu. It works the same if you need to convert columns to rows in Excel. This is the best way for a one-time conversion of small to medium numbers of rows both in the 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 switch rows and columns in Excel in more efficient waysTo switch rows and columns in Excel automatically or dynamically, you can use one of these options:
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.
Check out all the available Excel integrations. Try out Coupler.io for free How to change row to column in Excel with TRANSPOSETRANSPOSE is the Excel function that allows you to switch rows and columns in Excel. Here is its syntax:
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. TRANSPOSE formula to convert multiple rows to columns in Excel desktopTRANSPOSE 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(A1:J2) Fortunately, you don’t have to go through this procedure in Excel Online. TRANSPOSE formula to rotate row to column in Excel OnlineYou can simply insert your TRANSPOSE formula in a cell and hit enter. The rows will be rotated to columns right away without any additional button combinations. Excel TRANSPOSE multiple rows in a group to columns between different workbooksWe 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:
=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. Are there other formulas to convert columns to rows in Excel?TRANSPOSE is not the only function you can use to change rows to columns in Excel. 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. Let’s check it out in the following example. How to convert text in columns to rows in Excel using INDIRECT+ADDRESSIf you have a set of columns with the first cell A1, the following formula will allow you to convert columns to rows in Excel. =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))) But where are the rows, you may ask! Well, first you need to drag this formula down if you are converting multiple columns to rows. Then drag it to the right.
How to convert Excel data in columns to rows using INDIRECT+ADDRESS from other cellsTo convert columns to rows in Excel with INDIRECT+ADDRESS formulas from not A1 cell only, use the following formula: =INDIRECT(ADDRESS(COLUMN(first_cell) - COLUMN($first_cell) + ROW($first_cell), ROW(first_cell) - ROW($first_cell) + COLUMN($first_cell)))
Here is an example of how to convert Excel data in columns to rows: =INDIRECT(ADDRESS(COLUMN(C5) - COLUMN($C$5) + ROW($C$5), ROW(C5) - ROW($C$5) + COLUMN($C$5))) Again, you’ll need to drag the formula down and to the right to populate the rest of the cells. How to automatically convert rows to columns in Excel VBAVBA is a superb option if you want to customize some function or functionality. However, this option is only code-based. Below we provide a script that allows you to change rows to columns in Excel automatically.
VBA macro script to convert multiple rows to columns in ExcelSub 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
Convert rows to column in Excel using Power QueryPower 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.
Error message when trying to convert rows to columns in ExcelTo wrap up with converting Excel data in columns to rows, let’s review the typical error messages you can face during the flow. Overlap errorThe 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 errorYou 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 from rows to columns in Excel. Always double-check the syntax before pressing Enter or Ctrl+Shift+Enter 🙂 . Good luck with your data! How do you mass transpose in Excel?TRANSPOSE function. Step 1: Select blank cells. First select some blank cells. ... . Step 2: Type =TRANSPOSE( With those blank cells still selected, type: =TRANSPOSE( ... . Step 3: Type the range of the original cells. Now type the range of the cells you want to transpose. ... . Step 4: Finally, press CTRL+SHIFT+ENTER.. How do you transpose a group of rows?Basically, you select all the data you want to transpose, (including any column or row headers) Ctrl + C to copy it, right click on the top left cell where you want to paste it and paste special Check the transpose box and hit ok, and you're done.
How to transpose every 5 or n columns from one row to multiple rows?Transform every 5 or n rows from one column to multiple columns: To transform every 5 or n rows from one column to multiple columns, Kutools for Excel's Transform Range utility can help you to solve this job as quickly as you can. It also can help you to transpose a range of data to a single row or column.
|