Keep Excel Rows Matched to Unique IDs When Sorting in WPS Spreadsheets
Question details
The user needs to keep each worksheet record matched to its unique ID when sorting, inserting, or adding rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user manages a worksheet with formula-based IDs in column A and needs each record to stay aligned with its ID during sorting and data entry.
- Observed behavior
- Sorting, inserting, or adding records can make a row-number-based ID change or become separated from the corresponding row data.
Before changing the worksheet, identify whether the ID in column A is based on a row-number formula and review which columns contain record data. If you plan to protect the sheet, decide which cells users may still edit.
Convert the range to a WPS Spreadsheets table and use stable IDs
Use this solution for most worksheets because a table keeps sorting, filtering, and new record entry aligned across every column in the same row.
An Excel-compatible table treats each row as one record, so sorting and inserting rows apply to the full row. Replacing row-number formulas with stable IDs prevents the identifier from changing when a record moves.
Open the workbook in WPS Spreadsheets, click any cell inside the dataset you want to keep together, and make sure the range includes the ID column and all record columns.
Go to the Insert tab, click Table or press Ctrl+T, check My table has headers if your first row contains column titles, then click OK.
In column A, replace formulas such as =ROW()-1 or other row-number formulas with fixed unique IDs, or use a stable identifier that does not change when the row position changes.
Use the table header filter dropdown or go to Data > Sort to sort by any column. Add new records in the blank row below the table so the table expands and keeps each record together.
Protect the ID column and allow sorting in a protected worksheet
Use this solution after you have stable IDs if other people edit the workbook and you need to prevent accidental changes to the ID column.
Keep ID and Row Data Together with WPS Spreadsheets Tables
Open your workbook in WPS Spreadsheets, convert the range to a table, and replace row-based IDs with stable values. WPS Office gives you the Excel-compatible table, sorting, and sheet-protection tools you need without changing your existing .xlsx workflow.
- 1. Open the WPS Spreadsheets workbook: Start WPS Office, click Spreadsheets, and open the .xlsx workbook that contains the ID column and records.
- 2. Convert the data range to a table: Click any cell in the data range, go to Insert > Table, confirm My table has headers if needed, and click OK.
- 3. Use stable unique IDs in column A: In the ID column, replace row-number formulas with static unique IDs or a stable identifier that will not change when the row changes position.
- 4. Sort and add records inside the table: Use a column filter dropdown or Data > Sort to sort by any column, and add new records in the blank row directly below the table.
- 5. Save as an Excel-compatible file: Click Menu > Save As and choose .xlsx to keep the workbook fully compatible with Microsoft Excel.
Frequently Asked Questions
Why does my formula-based ID change when I sort rows in WPS Spreadsheets?
If the ID uses ROW() or another row-position formula, sorting recalculates the formula and may assign a new ID to the record. Replace it with a fixed unique ID or a stable identifier that does not depend on the current row number.
How do I convert a range to a table in WPS Spreadsheets?
Select any cell in the range, go to Insert > Table or press Ctrl+T, check My table has headers if the first row contains column titles, and click OK.
Can I protect the ID column but still allow sorting?
Yes. Lock the ID column via Format Cells > Protection, then go to Review > Protect Sheet and enable Sort and AutoFilter in the protect sheet dialog.
Will a table saved as .xlsx in WPS Spreadsheets work in Microsoft Excel?
Yes. Save the file as .xlsx; table behavior, sorting, and row alignment remain compatible with Microsoft Excel.
What should I do when I add new rows so IDs stay matched?
Add a row inside the table by typing in the blank row directly below the table or right-clicking a row and choosing Insert. The table expands and keeps all columns together, and you can enter a stable unique ID for the new record.
