How to Move Every Other Excel Row Up and One Column Right in WPS Office Spreadsheets
Question details
The user needs to restructure a single-column list where each record spans two alternating rows into a two-column table and remove the empty rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The dataset currently has paired values in alternating rows in one column, such as a label in row 2 and its value in row 3.
- Observed behavior
- Values appear on alternating rows in a single column, leaving blank rows and making it difficult to analyze or summarize the data.
Verify that the source data is in one column and that every pair follows the same pattern, such as name in the first row and amount in the next. Insert two empty columns to the right of the source range so the restructured output has room without overwriting existing data.
Use WRAPROWS to reshape alternating rows into two columns
Use this if your WPS Office version supports the WRAPROWS dynamic array function and you want a single formula that spills the result.
WRAPROWS takes a one-column list or vector and wraps it into rows. For paired alternating data, a wrap count of 2 puts the first value of each pair in the left output column and the second value in the right output column.
WPS Office versions that do not support dynamic arrays may return #NAME? instead of a spilling range. In that case, use the INDEX formula method below.
Click the cell where the two-column result should begin, for example C2.
Type =WRAPROWS(A2:A17,2) and press Enter, adjusting A2:A17 to the actual source range.
WPS fills the adjacent columns automatically for each pair from the source range.
Combine alternating rows with INDEX formulas in any version
Use this when you need a version-independent formula that works in WPS Spreadsheets and Microsoft Excel, and you want full control over the output range.
Move Every Other Row Up and One Column Right in WPS Office Spreadsheets
Use WPS Office Spreadsheets to combine every pair of alternating rows into one clean two-column record. You can work directly in .xlsx files with familiar formulas, no paid Excel 365 add-in required.
- 1. Open the source workbook: Launch WPS Spreadsheets, click Menu, then Open, and select the Excel file that contains the alternating row data.
- 2. Insert output columns: Select two blank columns to the right of the source column, right-click, and choose Insert.
- 3. Enter the pairing formulas: In the first output cell enter =INDEX($A$2:$A$17,(ROW()-2)*2+1), in the next enter =INDEX($A$2:$A$17,(ROW()-2)*2+2), and press Enter.
- 4. Fill down and clean up: Select the two formula cells and drag the fill handle down through all pairs. Copy the result and paste as values, then delete the original alternating rows you no longer need.
- 5. Save the restructured workbook: Press Ctrl+S and keep the .xlsx format so the file remains compatible with Microsoft Excel.
Frequently Asked Questions
How do I move every other Excel row up and one column to the right without Excel 365?
In WPS Spreadsheets, use the INDEX formula method: enter =INDEX($A$2:$A$17,(ROW()-2)*2+1) in the first output cell and =INDEX($A$2:$A$17,(ROW()-2)*2+2) in the adjacent cell, then fill down.
Why does =WRAPROWS(vector,2) return #NAME? in WPS Spreadsheets?
Your WPS Office version may not support the WRAPROWS dynamic array function. Use the INDEX formula method described above; it works in WPS Spreadsheets without dynamic arrays.
Can I apply this to multiple columns or more than two rows per record?
Yes. The same logic can be extended by adjusting the range and the multiplication factor in the INDEX formulas, or by using WRAPROWS with a different row count if supported.
Will the restructured file still open in Microsoft Excel?
Yes. WPS Spreadsheets preserves .xlsx compatibility, and the INDEX formulas also work when the workbook is later opened in Microsoft Excel.
How do I remove the original blank rows after restructuring?
After copying the formula results and pasting them as values, select the original column, apply Filter, filter for blanks, then delete those rows to keep only the combined records.
