How to Automatically Combine Every Two Rows into One Row in Excel
Question details
The user needs a method to automatically merge paired consecutive rows into a single row spanning separate columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing worksheet data where related information is split across alternating pairs of rows instead of being side-by-side in adjacent columns.
- Observed behavior
- Data is arranged vertically in alternating rows and needs to be pivoted, grouped, or split so that each related pair occupies exactly one horizontal row.
Ensure your dataset does not contain unexpectedly merged cells or completely blank rows between the data pairs, as this can disrupt the indexing formula and offset your merged rows.
Use Power Query and a Helper Column to Combine Rows
Using a helper column with an indexing formula alongside Power Query provides a dynamic, repeatable way to merge row pairs into single rows across multiple columns.
Power Query is highly recommended for this scenario because it creates a repeatable process. By adding a helper column to group your row pairs, Power Query can combine and pivot the data cleanly.
In an empty column right next to your data (e.g., Column B), enter the formula =ROUNDUP(ROW(A1)/2,0) in the first data row and drag the fill handle down. This creates an index that assigns the same number to every two consecutive rows (1, 1, 2, 2, etc.).
Highlight your entire data range including the new helper column. Navigate to the Data tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, click to select the Helper Column. Go to the Transform tab and select 'Group By'. Keep the operation set to 'All Rows' to group the paired data together under single index numbers.
Add a Custom Column to extract the paired text values and join them using a temporary delimiter (like a pipe '|' or tilde '~'). Delete the original grouped columns, then select your new custom column and click 'Split Column' > 'By Delimiter' to separate the pairs into adjacent columns.
Once the rows are cleanly separated into distinct columns, click 'Close & Load' on the Home tab to output your newly formatted, combined rows into a fresh Excel worksheet.
Combine Rows Quickly with WPS Spreadsheet Formulas
You can achieve the exact same row-combining result in WPS Office using simple indexing formulas, entirely avoiding the steep learning curve of Power Query.
- 1. Identify Your Source Data: Open your spreadsheet in WPS Office Spreadsheets and locate the single-column data you want to pair up (for example, Column A).
- 2. Extract the First Row of the Pair: Click into cell C1 (or your desired output area) and type the formula =INDEX($A:$A, ROW(A1)*2-1). Press Enter. This pulls the first row's data.
- 3. Extract the Second Row of the Pair: Click into cell D1, right next to the previous formula, and type =INDEX($A:$A, ROW(A1)*2). Press Enter to pull the second row's data.
- 4. Apply Formulas to the Whole Range: Highlight both cells C1 and D1. Double-click or drag the fill handle at the bottom-right corner downwards until you see empty or zero results. Your data is now perfectly combined into single rows.
Frequently Asked Questions
Can I combine every three or four rows into one instead of two?
Yes. If you are using the helper column method, simply change the divisor in the formula from 2 to the number of rows you wish to group. For example, use =ROUNDUP(ROW(A1)/3,0) to group every three rows. In the direct INDEX formula method, you would multiply by 3 instead of 2.
Why do I get extra columns or errors when splitting columns in Power Query?
This usually happens if the temporary delimiter you chose (such as a comma or space) already exists naturally within your source text. To fix this, always use a unique, rare character like a pipe (|) or caret (^) when combining the data before you split it.
Is it possible to combine rows manually without using formulas or Power Query?
For very small datasets, you can copy the second row, right-click the adjacent cell of the first row, and use 'Paste Special' > 'Transpose' to move the data side-by-side. However, for large sets of data, utilizing formulas or Power Query is highly recommended to eliminate repetitive manual work.
