Formula Errors

How to Keep Excel Values with Rows When Data Is Reordered

WPS Content Manager Oct 1, 2026 868 views

Question details

The user needs a way to keep formula values attached to specific rows of source data so that when the source data is sorted or rearranged, the dependent values do not change or break.

How to Keep Excel Values with Rows When Data Is Reordered

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: The user is manually entering related information beside formula-driven cells on a separate worksheet, but rearranging the original source cells causes the related values to shift out of alignment.
Observed behavior: Absolute references (like $A$1) lock onto the physical cell address rather than the underlying data. As a result, when source rows move, the formulas return the values of the new data occupying those fixed cell addresses.

Before you start

Before attempting to link your data, ensure your source worksheet has a column containing a unique, permanent identifier (such as an ID number, sku, or distinct name) for every row.

Solution 1Recommended

Use Lookup Formulas (XLOOKUP or VLOOKUP) with a Unique Identifier

Retrieve data dynamically by matching a permanent, unique identifier instead of relying on a fixed cell address.

Absolute references lock a formula to a specific grid coordinate. To ensure that data follows a row when it moves, you must assign a unique ID to each row and use a lookup function to search for that ID dynamically.

Create a unique identifier column

Insert a new column in your source worksheet and assign a distinct, permanent ID (e.g., 1, 2, 3) to each row of data.

Set up the destination sheet

On your fourth worksheet (or destination sheet), create a matching ID column that corresponds to the data you wish to retrieve.

Apply the XLOOKUP function

In the target cell, enter the formula =XLOOKUP(A2, SourceSheet!A:A, SourceSheet!B:B), where A2 is the unique ID, SourceSheet!A:A is the column containing IDs, and SourceSheet!B:B is the column containing the data you want to return.

Copy the formula

Drag the fill handle down to apply this formula to the remaining rows. The values will now stay accurate even if the source data is rearranged.

Use Lookup Formulas (XLOOKUP or VLOOKUP) with a Unique Identifier

Pro Tip: If you are using an older version of Excel that does not support XLOOKUP, you can use VLOOKUP or a combination of INDEX and MATCH to achieve the exact same result.

Solution 2

Use the SORTBY Function for Dynamic Data Spilling

Automatically retrieve and sort your data based on a corresponding reference range without breaking row alignments.

Solution 3

Format Source Data as an Excel Table

Convert your data into a Table to use structured references that expand and adapt automatically.

Advanced Data Management

Easily Link and Manage Dynamic Data with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for modern lookup formulas like XLOOKUP, VLOOKUP, and dynamic arrays like SORTBY. You can effortlessly tie your data to unique identifiers and use structured tables to prevent values from breaking when rows are rearranged.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your misaligned data.
2. Add a unique ID column: Insert a helper column in your source sheet to assign a distinct identifier to every row.
3. Apply a lookup formula: In your destination sheet, use =XLOOKUP or =VLOOKUP to fetch the data based on the unique ID rather than a static cell.
4. Format as a Table: Highlight your source data and press Ctrl+T to format it as a Table, ensuring your ranges update automatically.

Fully compatible with Microsoft Excel formulas, functions, and structured tablesFull support for advanced dynamic array functions like SORT and SORTBYBuilt-in XLOOKUP and VLOOKUP wizards for easy data retrievalLightweight, fast, and completely free to use

Frequently Asked Questions

Why doesn't an absolute reference ($A$1) follow my data when it is sorted?

An absolute reference is designed to lock a formula to a specific grid coordinate (like cell A1) regardless of what data is placed there. If you sort or move the data out of A1, the formula continues looking at cell A1, which now contains different data.

What is the difference between VLOOKUP and XLOOKUP?

XLOOKUP is a modern, more flexible function that can search both vertically and horizontally, look to the left of the search array, and defaults to an exact match. VLOOKUP is an older function that only searches the leftmost column of a specified range and requires a column index number.

How do Excel tables prevent formula errors when reordering rows?

Excel Tables utilize structured references (e.g., TableName[ColumnName]) instead of static cell blocks. Because the formula references the semantic column rather than physical cells, rearranging rows or adding new data automatically updates the underlying logic without breaking your linked formulas.

Excel & Data Problems