Keep External Data Aligned When Sorting a Table in WPS Office Spreadsheets
Question details
The user needs to sort or filter a dynamic Excel table without causing adjacent external data to become misaligned.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an Excel table with adjacent external data and wants to keep related rows together during sorting or filtering.
- Observed behavior
- Sorting or filtering the table moves only the table rows; the adjacent external data does not move, causing row misalignment.
Check whether the related values can be added directly into the Excel table. If the external data must remain separate, make sure both datasets contain a shared unique key column before sorting or filtering.
Move related external data into the Excel table before sorting or filtering
Best when the adjacent values belong to the same records and can be stored inside the table.
When all related values are table columns, any sort or filter action moves every column together, so rows cannot become misaligned.
Click any cell in the WPS Spreadsheets table that you want to sort or filter.
On the Table Tools tab, click Insert Right, or right-click a column header and choose Insert > Table Columns to the Right.
Paste or type the adjacent data into the added column, matching each row to its original record.
Use the filter arrow in the column header or the Data tab sort options to sort; every column in the table now moves together.
Use a unique key and XLOOKUP or INDEX/MATCH when data must stay separate
Use this when the external data cannot be moved into the table and must remain in a separate sheet or range.
Align sorted Excel table data without losing external values in WPS Office
In WPS Office Spreadsheets, you can keep Excel table rows and external data aligned by moving related values into the table or linking separate data with XLOOKUP or INDEX/MATCH. WPS handles .xlsx workbooks and familiar Excel table tools in a free, lightweight interface.
- 1. Open the Excel workbook: Launch WPS Office Spreadsheets and open the .xlsx file containing the table and external data.
- 2. Select the table area: Click any cell inside the Excel table to activate the Table Tools tab.
- 3. Keep data in one table or link by key: Either insert a table column for the external values, or use =XLOOKUP(A2,Table1[ID],Table1[Value],"") in the separate range.
- 4. Sort or filter safely: Use the header filter arrows or Data tab sorting commands; all linked values stay with the correct row.
Frequently Asked Questions
Why does my adjacent external data not move when I sort an Excel table?
Sorting and filtering only act on the table range. Adjacent data outside the table is treated as a separate range, so WPS Spreadsheets does not move it with the table rows unless you include it in the table or link it by a key.
How do I add external data to an existing Excel table in WPS?
Click any cell in the table, go to Table Tools, and insert a new table column to the right. Then copy the external values into that column so they become part of the table.
Can I still keep data in a separate range and have it align after sorting?
Yes. Add a unique key to both the table and the external range, then use =XLOOKUP(key,table_key_range,table_value_range,"") or =INDEX(return_range,MATCH(key,lookup_range,0)) to return the value. Sorting the table will not break the link.
Does WPS Office support XLOOKUP and Power Query for this alignment issue?
WPS Office Spreadsheets supports XLOOKUP, INDEX/MATCH, and Power Query in compatible workbook workflows, allowing you to merge or look up external data by a common key instead of row position.
