Method 1 – Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows
Use the OFFSET function and the ROWS and COLUMNS functions.
Steps:
- Enter the formula in D5.
=OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*4,0)
- Press Enter.
- Drag the Fill Handle (+)Â to the right.
- Drag down the Fill Handle to see the result in the rest of the cells.
Data is organized in rows.
Formula Breakdown
➤ COLUMNS($B:B)
returns {1}. 1 was subtracted from this part and the result becomes zero.
➤ ROWS($5:5)
returns {1}. 1 was subtracted from this part and the result becomes zero. TROWS($5:5) is multiplied by 4 to return 4 cells in each row.
➤ OFFSET($B$5,COLUMNS($B:B)-1+(ROWS($5:5)-1)*4,0)
returns {John}.
Read More: VBA to Transpose Multiple Columns into Rows in Excel
Method 2 – Applying the TRANSPOSE Function to Convert Columns to Rows
Transform data from columns to rows using the TRANSPOSE function.
The dataset contains biographic data. Organize it row-wise:
Steps:
- Enter the formula in C14.
=TRANSPOSE(B5:E10)
- Press Enter.
You will get the result in an array.
Note:
In older versions of Excel, press Ctrl + SHIFT + Enter to enter the formula as an array.
Read More: How To Transpose Data in Excel
Method 3 – Convert a Single Column to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)
Use a combination of the OFFSET, MOD, ROW, INT, and COLUMN functions.
The dataset contains a column with random biographical data. Organize the data in multiple rows according to each person’s Names, Professions, and Addresses.
The generic formula is:
OFFSET($B$5,(ROW()-f_row)*rows_of_set+INT((COLUMN()-f_col)/col_of_set),MOD(COLUMN()-f_col,col_of_set))f_row is the row number.
f_col is the column number.
rows_of_set is the number of rows with data.
col_of_set is the number of columns with data.
Steps:
- Enter the formula in D5.
- Press enter.
=OFFSET($B$5,(ROW()-5)*3+INT((COLUMN()-4)),MOD(COLUMN()-4,1))
- Drag the Autofill to the right.
- This is the output. Drag down the Fill Handle to see the result in the rest of the cells.
- This is the final output.
Formula Breakdown
➤ ROW()
returns {5}. 5 is subtracted from the formula: 0 is the result.
➤ COLUMN()
returns {4}. 4 is subtracted from the formula: 0 is the result. 0 is passed into the INT function to get 0.
➤ COLUMN()
returns 4. 4 is subtracted from the formula. The result is passed within the MOD function and MOD(0.1) returns 0.
➤ OFFSET($B$5,(ROW()-5)*3+INT((COLUMN()-4)),MOD(COLUMN()-4,1))
returns {John Smith}
Read More: Convert Columns to Rows in Excel Using Power Query
Method 4 – Using a Combination of Excel Functions (INDIRECT, ADDRESS, ROW & COLUMN) to Convert One Column to Rows
Combine the INDIRECT, ADDRESS, ROW, and COLUMN functions.
Steps:
- Enter the formula in D5.
=INDIRECT(ADDRESS((ROW($B5)-4)*3+COLUMN(B5),2))
- Press Enter.
-  ‘John Smith’ is the result. Drag the formula to the right.
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula Breakdown
➤ ROW($B5)-4)
returns {5}.
➤ COLUMN(B5)
returns {2}.
➤ INDIRECT(ADDRESS((ROW($B5)-4)*3+COLUMN(B5),2))
returns {John Smith}.
Read More: How to Convert Columns to Rows in Excel Based On Cell Value
Things to Remember
- While using the TRANSPOSE function,you can’t have blank cells in the dataset. Otherwise, it will return ‘0’ in the place of the blank.
- If you change data in the resulting array of the TRANSPOSE formula, an error (#SPILL!) will occur.
- You can also use the Paste Special option to convert data from single columns to rows.
Download Practice Workbook
Download the practice workbook.
Related Articles
- How to Transpose Every n Rows to Columns in Excel
- How to Transpose Rows to Columns Based on Criteria in Excel
- How to Transpose Rows to Columns Using Excel VBA
- How to Transpose Rows to Columns in Excel
- VBA to Transpose Array in Excel
- Transpose Multiple Columns into One Column in Excel
