Method 1 – Convert Multiple Columns into a Single Row with Formula
You will find the dataset in the sheet named “Dataset”.
Convert these columns to a single row in another sheet “SingleRow”
Steps
- Go to the “SingleRow” sheet and click on Cell A1.
- Type the following formula:
=OFFSET(Dataset!$B$4,((ROW()-1)*3)+(FLOOR(COLUMN()-1,3)/3),(COLUMN()-1)-(FLOOR(COLUMN()-1,3)))
- Press Enter.
- The first data is pasted here.
- Drag the Fill Handle icon to the right until you get the 0. Remember, 0 means the end of the dataset. You don’t have to include it.
We onverted multiple columns to a single row in Excel.
Breakdown of the Formula
Dataset!$B$4: It specifies the sheet name and the range reference you want to use.
(ROW()-1)*3: 3 stands for the row number.
(COLUMN()-1,3)/3: 3 indicates the column number here.
Change these values according to your needs. Based on your rows and columns.
Method 2 – VBA Codes to Convert Multiple Columns into a Single Row
Steps
- Press Alt+F11 on your keyboard to open the Visual Basic editor.
- Click on the Insert > Module option.
- Type the following code:
Sub convert_to_a_single_row()
Dim input_data As Range, output_range As Range
xTitleId = "Convert Multiple Rows to a Single Column"
Set input_data = Application.Selection
Set input_data = Application.InputBox("Select Your Range :", xTitleId, input_data.Address, Type:=8)
Set output_range = Application.InputBox("Enter the Cell where you want to paste:", xTitleId, Type:=8)
Application.ScreenUpdating = False
num_of_rows = input_data.Rows.Count
num_of_columns = input_data.Columns.Count
For i = 1 To num_of_rows
input_data.Rows(i).Copy output_range
Set output_range = output_range.Offset(0, num_of_columns + 0)
Next
Application.ScreenUpdating = True
End Sub
- Save the file.
- Press Alt+F8 on your keyboard to open the Macro dialog box.
- Select convert_to_a_single_row.
- Click Run.
- Select the range of cells from the dataset (your multiple columns).
- Click OK.
- Go to the “VBA” sheet and select any cell to start with.
- Click OK.
Breakdown of the VBA Code
Set input_data = Application.Selection
Set input_data = Application.InputBox("Select Your Range :", xTitleId, input_data.Address, Type:=8)This chunk of code takes your input from multiple columns in your dataset.
Set output_range = Application.InputBox("Enter the Cell where you want to paste:", xTitleId, Type:=8)It takes the input of the cell where you want to convert your multiple columns to a single row.
num_of_rows = input_data.Rows.Count
num_of_columns = input_data.Columns.CountThese codes count the number of rows and columns of your input dataset.
For i = 1 To num_of_rows
input_data.Rows(i).Copy output_range
Set output_range = output_range.Offset(0, num_of_columns + 0)
NextWe run a loop to take each value from your dataset and paste them into a cell. After that, it takes the next value and pastes that in the same row but in the next column.
Similar Readings
- How to Reverse Transpose in Excel (3 Simple Methods)
- Transpose Duplicate Rows to Columns in Excel (4 Ways)
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- How to Convert Columns to Rows in Excel (2 Methods)
How to Convert Multiple Columns into Single Column in Excel
Method 1 – Convert Multiple Columns into Single Column Using Formula
Steps
- Create a new column where you want to paste your multiple columns.
- Select Cell F5 and type the following formula:
=INDEX($B$4:$D$6,1+INT((ROW(A1)-1)/COLUMNS($B$4:$D$6)),MOD(ROW(A1)-1+COLUMNS($B$4:$D$6),COLUMNS($B$4:$D$6))+1)
- Press Enter.
- Drag the Fill Handle down until you get #REF!.
- You don’t have to include the #REF!. It means the end of the dataset.
Breakdown of the Formula
- COLUMNS($B$4:$D$6): The COLUMNS function inside the MOD function here returns the total number of columns available in the range ($B$4:$D$6).
- ROW(A1)-1+COLUMNS($B$4:$D$6): The mixture of ROW and COLUMNS functions here represents the dividend of the MOD
- MOD(ROW(A1)-1+COLUMNS($B$4:$D$6), COLUMNS($B$4:$D$6))+1: This part represents the column number of the INDEX For the result, the function returns ‘1’.
- 1+INT((ROW(A1)-1)/COLUMNS($B$4:$D$6)): This is the row number of the INDEX We specify this in the part where the INT function rounds up the resulting value to the integer form.
Method 2 – CONCATENATE Function to Convert Multiple Columns into Single Column
Steps
- Select Cell E5 and type the following formula:
=CONCATENATE(B5,C5,D5)
- Press Enter.
- Drag the Fill handle icon over the range of cells E6:E9.
How to Transpose Multiple Rows in Excel
Steps
- Select and copy the whole dataset.
- Cick any cell.
- From the Home tab, you will find the Paste Click on it and select the Transpose (T) command.
After that, it will transpose the multiple rows of your dataset. To know more about this, read: Transpose Multiple Rows in Group to Columns in Excel.
Things to Remember
✎ The Visual Basic code will convert multiple columns with their formatting also.
✎ Make sure to check those functions we used in the formula. Otherwise, it will be difficult to understand if you are unknown to them.
Download Practice Workbook
Download this practice workbook.
Related Articles
- How to Transpose Rows to Columns Based on Criteria in Excel (2 Ways)
- Excel Paste Transpose Shortcut: 4 Easy Ways to Use
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- VBA to Transpose Array in Excel (3 Methods)
- How to Convert Column to Comma Separated List With Single Quotes
- Convert Single Columns to Rows in Excel with Formulas
- How to Transpose in Excel VBA (3 Methods)
