How to convert multiple rows into columns in excel
Convert Rows to Columns in Excel
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?
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 or columns to rows in Excel – the basic solution
Can I convert multiple rows to columns in Excel from another workbook?
How to switch rows and columns in Excel in more efficient ways
How to change row to column in Excel with TRANSPOSE
TRANSPOSE formula to convert multiple rows to columns in Excel desktop
TRANSPOSE formula to rotate row to column in Excel Online
Excel TRANSPOSE multiple rows in a group to columns between different workbooks
Are there other formulas to convert columns to rows in Excel?
How to convert text in columns to rows in Excel using INDIRECT+ADDRESS
How to convert Excel data in columns to rows using INDIRECT+ADDRESS from other cells
How to automatically convert rows to columns in Excel VBA
VBA macro script to convert multiple rows to columns in Excel
Convert rows to column in Excel using Power Query
Error message when trying to convert rows to columns in Excel
Overlap error
Wrong data type error
How to convert rows into columns or columns to rows 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
 Rightclick 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.
It works the same if you need to convert columns to rows in Excel.
This is the best way for a onetime 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 ways
To switch rows and columns in Excel 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.
Try out Coupler.io for free
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:
=TRANSPOSE(array)
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.
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(A1:J2)
Fortunately, you don’t have to go through this procedure in Excel Online.
TRANSPOSE formula to rotate row to column in Excel Online
You 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 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.
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+ADDRESS
If 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.
NOTE: This Excel formula only to convert data in columns to rows starting with A1 cell. If your columns start from another cell, check out the next section.
How to convert Excel data in columns to rows using INDIRECT+ADDRESS from other cells
To 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)))
 first_cell – enter the first cell of your columns
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 VBA
VBA is a superb option if you want to customize some function or functionality. However, this option is only codebased. Below we provide a script that allows you to change rows to columns in Excel automatically.
 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. You’ll find the script in the next section.
 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!
VBA macro script to convert multiple rows to columns in Excel
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
Read more in our Excel Macros Tutorial.
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 Excel data in columns to rows, let’s review the typical error messages you can face during the flow.
Overlap error
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 from rows to columns in Excel. Always doublecheck the syntax before pressing Enter or Ctrl+Shift+Enter 🙂 . Good luck with your data!
Back to Blog
How to convert multiple rows and columns to columns and rows in Excel
Excel 2021 Excel 2019 Excel 2016 Excel 2013 Office for business Excel 2010 More...Less
Summary
When you use the Microsoft Excel products listed at the bottom of this article, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).
More Information
The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout). This is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format.
Example

In a new worksheet, type the following data:
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 2381845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 5814914 
Type the following formula in cell C1:
=OFFSET($A$1,(ROW()1)*4+INT((COLUMN()3)),MOD(COLUMN()3,1))

Fill this formula across to column F, and then down to row 3.

Adjust the column sizes as necessary. Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John
111 Pine St.
San Diego, CA
(555) 128549
Jones, Sue
222 Oak Ln.
New York, NY
(555) 2381845
Anderson, Tom
333 Cherry Ave.
Chicago, IL
(555) 5814914
The formula can be interpreted as
OFFSET($A$1,(ROW()f_row)*rows_in_set+INT((COLUMN()f_col)/col_in_set), MOD(COLUMN()f_col,col_in_set))
where:

f_row = row number of this offset formula

f_col = column number of this offset formula

rows_in_set = number of rows that make one record of data

col_in_set = number of columns of data
Convert multiple rows and columns to columns and rows in Excel
Excel 2021 Excel 2019 Excel 2016 Excel 2013 Office for Business Excel 2010 More...Less
Summary
If you are using the Microsoft Excel products listed at the bottom of this article, you can use a formula on a worksheet to wrap data that spans multiple rows and columns in a database (column) format.
Additional information
The following example converts every four rows of data in a column to four columns of data in one row (similar to a database field and record layout). This scenario is similar to when you open an email file or a text file that contains data in a mail label format.
Example

In the new example, enter the following data:
A1: Kuznetsov, John
A2: st. Sosnovaya,
111 A3: San Ina, CA
A4: (555) 128549
A5: "John", "Yura"
A6: 222 st. l.
A7: New York, NY
A8: (555) 2381845
A9: Anderson, Tom
A10: 333Cherry Ave.
A11: Chicago, IL
A12: (555) 5814914 
Enter the following formula in cell C1
:=OFFSET($A$1,(ROW()1)*4+INT((COLUMN()3)),MOD(COLUMN()3,1))

Complete this formula up to column F and then down to row 3.

Adjusted the size of the columns if necessary. Notice that the data is now displayed in cells C1F3 as follows:
Kuznetsov, John
st. Sosnovaya, 111
Sun Key, CA
(555) 128549
John, Yura
222 St Ln.
New York, NY
(555) 2381845
Anderson, Tom
333 Cherry Ave.
Chicago, IL
(555) 5814914
Formula can be interpreted as
OFFSET($A$1,(ROW()f_row)*rows_in_set+INT((COLUMN()f_col)/col_in_set), MOD(COLUMN()f_col,col_in_set))
where

f_row = row number of this offset formula

f_col = column number of this offset formula

rows_in_set = number of rows that make up one data record

col_in_set = number of data columns
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 data in columns that needs to be rotated to reorder them by row, use function Transpose. 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:

Select the range of data that you want to reorder, including row or column headers, and then press CTRL+C.
Note: Make sure to copy the data to do this, as you won't be able to use the Cut command or CTRL+X.

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.
Rightclick the top left cell where you want to enter transpose the table and select transpose .

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 it 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:
 Learn more
 How to convert cgpa into german grading system
 How to convert column into rows in excel
 How to convert km into miles
 How to convert video tapes into dvds
 How to convert uppercase in excel
 How to play vob files on ipad without converting
 How to convert column into rows in excel
 How to convert km into miles
 How to convert video tapes into dvds
 How to convert uppercase in excel
 How to play vob files on ipad without converting