How to Automatically Sort Adjacent Excel Data in Descending Order in WPS Office
Question details
The user needs to sort adjacent columns of names and hours worked in descending order so related values remain together, using a dynamic array formula that returns the sorted result without changing the original data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Names and hours worked are stored in adjacent columns, and sorting only one column would separate the related values.
- Observed behavior
- The user wants the full adjacent range returned in descending order by the second column while the original D2:E500 data remains unchanged.
Check that your WPS Office version supports the SORT dynamic array function and open the workbook with the adjacent columns ready. Ensure the destination area is blank so the returned range can spill without overwriting other data.
Use the SORT Dynamic Array Formula to Return Adjacent Columns in Descending Order
Use this method to keep the original data unchanged and automatically return both columns sorted by the hours worked column in descending order.
The SORT function treats the adjacent columns as one range, so names and hours remain aligned. It is supported in WPS Office Spreadsheets versions that include modern dynamic array functions.
Open your WPS Office spreadsheet and click an empty cell outside the source data where the sorted range can spill without overlapping existing data.
Type =SORT(D2:E500,2,-1) and press Enter. Adjust D2:E500 to match the actual range of names and hours in your sheet.
Verify that the formula returns both columns together, with the highest hours at the top and each name still aligned with its original hours value.
Sort Adjacent Columns in Place Using the WPS Data Sort Tool
Use this method if you prefer to sort the original range directly instead of returning a sorted copy with a formula.
Sort Adjacent Data Automatically with WPS Office Spreadsheets
WPS Office Spreadsheets supports the SORT dynamic array function, so you can keep names and hours aligned and return a descending-order view without changing the original data. It works with familiar Excel files and modern formulas.
- 1. Open your Excel file: Launch WPS Office and open the .xlsx workbook that contains the adjacent names and hours columns.
- 2. Select a blank area: Click an empty cell where the sorted result can spill without covering other data.
- 3. Enter the SORT formula: Type =SORT(D2:E500,2,-1) and press Enter to return both columns sorted by the second column descending.
- 4. Review and save: Check that names and hours remain aligned, then save or share the workbook in Excel-compatible .xlsx format.
Frequently Asked Questions
What does =SORT(D2:E500,2,-1) mean in WPS Office?
It tells WPS to take the adjacent range D2:E500, sort by the second column in descending order using -1, and return the full sorted range in a new location while leaving the original data unchanged.
Can I sort adjacent data automatically when I add new rows?
The SORT dynamic array formula updates the returned range automatically when values in the source range change. If you add rows beyond D2:E500, extend the range in the formula to include the new data.
Why does sorting only one column separate names and hours?
When you select only the hours column and sort, the adjacent names stay in place, so the rows no longer match. Always select both columns or use a formula that sorts the full range as a unit.
Does WPS Office support the SORT function in existing .xlsx files?
Yes, WPS Office Spreadsheets supports SORT in compatible Excel .xlsx files. Make sure your WPS Office version is current to use dynamic array functions.
How can I sort in ascending order instead?
Change the third argument from -1 to 1, for example =SORT(D2:E500,2,1), to return the range sorted by the second column in ascending order.
