How to Sort Values While Preserving Blank Spacer Rows in WPS Office Spreadsheets
Question details
The user needs to sort numeric data from smallest to largest without losing the blank or gray spacer row between each data row.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has alternating data rows and blank or gray spacer rows, and the user wants the data sorted numerically while preserving that alternating row structure.
- Observed behavior
- A standard sort moves the blank cells together or removes the alternating blank spacer rows, so the visual structure is lost.
Check that your data values and spacer rows are in a single column. If the spacer rows are gray-filled but contain formulas returning an empty string, use a true blank value or adjust the formula condition so the helper method can identify them.
Use a helper column to sort values and keep spacer rows
Best for WPS Office Spreadsheets and Excel versions that do not have the latest dynamic array functions; it keeps the row pairing intact during the sort.
A helper column assigns each blank spacer row the same sort key as the value above it. When you sort by the helper column, WPS Office keeps the value row and its following spacer row together, provided the blank row is immediately below its value in the source range.
Select the column containing values and blank spacer rows, right-click the column letter, and choose Insert to add a blank column to the right.
In the first helper cell, for example B1, type =A1. In B2, type =IF(A2="",B1,A2) and fill the formula down to the last data row.
Select both columns, for example A:B, go to the Data tab, click Sort, set the helper column as the sort key, choose Sort On > Values and Order > Smallest to Largest, then click OK.
Delete or hide the helper column after the sort if you no longer need it.
Use a dynamic array formula in Excel 365
Use this when the file will be opened in Excel 365 or a build that supports LET, SCAN, LAMBDA, and SORTBY; it returns a sorted spill range without changing the original data.
Sort values and preserve blank spacer rows in WPS Office Spreadsheets
Use the helper-column workflow in WPS Office Spreadsheets to sort numbers from smallest to largest while keeping blank or gray spacer rows after each value. The same file can be opened in Microsoft Excel without losing the result.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the Excel workbook containing the values and blank spacer rows.
- 2. Add the helper column: Insert a column next to your data and enter =IF(A2="",B1,A2) to carry each value into its spacer row.
- 3. Sort the range: Select the data and helper columns, click Data > Sort, choose the helper column, and set Order to Smallest to Largest.
- 4. Save the result: Save the file as .xlsx so it remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does a normal sort put all blank rows at the bottom in WPS Office Spreadsheets?
A normal sort treats blank cells as empty values and groups them, so the alternating spacer rows are not preserved. Use a helper column to give each spacer row the same sort key as its value row before sorting.
Can I sort values with gray spacer rows instead of true blank cells?
Yes. The helper-column method uses =IF(A2="",B1,A2), so it works when the spacer cells are truly empty. If the cells are gray-filled but contain an empty string or zeros, replace the condition to match that content, for example =IF(A2=0,B1,A2).
What Excel 365 formula preserves blank rows after sorting?
In Excel 365 you can use =LET(order,SCAN("",A1:A6,LAMBDA(a,v,IF(v="",a,v))),SORTBY(A1:A6,order)). Replace A1:A6 with your range; the formula spills sorted values and blanks together.
Does WPS Office support the LET, SCAN, LAMBDA, and SORTBY functions?
Support depends on your WPS Office version. If those dynamic array functions are not available in your build, use the helper-column method, which works in WPS Office Spreadsheets and Excel.
Will the helper column change my original data?
No. The helper column only adds a sort key. After sorting you can delete or hide the helper column, and your value and spacer rows remain in the sorted order.
