Prevent Excel from Moving a UNIQUE Formula When Inserting Rows
Question details
The user wants to prevent a dynamic UNIQUE formula in an adjacent column from shifting down when new records are inserted at the top of a dataset via macro or manual entry.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a macro or manually inserting new payment records into columns A:E, while a dynamic UNIQUE formula is located in column F.
- Observed behavior
- The UNIQUE formula in column F is pushed down or shifted from its original position whenever new rows are inserted at the top of the adjacent columns.
Before altering your data entry process or modifying macros, ensure your raw dataset is properly formatted and backed up. Review how your current macro handles inserting rows to understand what structural changes need to be adjusted.
Use Excel Tables and Append Data to the Bottom
The most robust way to prevent formula shifting and structural errors is to add new records to the bottom of an Excel Table rather than inserting physical rows at the top.
Inserting rows at the top of a worksheet forces all adjacent cells, including formulas, to shift downwards. By converting your data into an official Excel Table and appending data to the bottom, the table will automatically expand its range without physically shifting adjacent columns or disrupting your UNIQUE formula.
Select your existing data range in columns A:E. Go to the 'Insert' tab and click 'Table', or press Ctrl+T to convert the range into a Table.
Change your macro or manual entry method so that it appends new payment records to the first empty row at the bottom of the table, rather than inserting a new row at row 2.
Enter your =UNIQUE() formula in column F (or another stable area) and reference the Table columns. As new data is added to the bottom, the Table expands and the UNIQUE formula updates automatically without moving.
Use a Separate Report Sheet to Reverse the Order
If you require the newest transactions to appear at the top, separate your raw data entry from your reporting view to keep formulas secure.
Efficiently Handle Dynamic Arrays with WPS Spreadsheet
WPS Office fully supports dynamic array functions like UNIQUE, SORT, and FILTER. By taking advantage of built-in table formatting and advanced calculation capabilities, you can easily manage transaction records without the frustration of formulas shifting or breaking.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the payment records.
- 2. Convert to a Table: Highlight your data range, navigate to the 'Insert' tab, and click 'Table' to enable automatic downward expansion.
- 3. Apply Dynamic Functions: Type =UNIQUE(range) in an adjacent column to extract distinct values securely, and use =SORT() on a separate sheet if you need to reverse the display order.

Frequently Asked Questions
Why does my UNIQUE formula return a #SPILL! error when I insert rows?
A #SPILL! error occurs when a dynamic array formula does not have enough empty cells below it to display all the results. Inserting rows might push other data into the formula's expansion path, blocking it and triggering the error.
Can I lock a UNIQUE formula to a specific cell so it never moves?
Using absolute references (like $F$2) stops cell references inside the formula from changing when dragged. However, if a physical row is inserted directly above the formula's location, the cell itself will still be pushed down to F3. The best prevention is avoiding row insertions at the top of the sheet.
How do I automatically sort my UNIQUE list in ascending order?
You can wrap the UNIQUE function inside a SORT function. For example, using =SORT(UNIQUE(A2:A100)) will extract the distinct values and then automatically sort them in alphabetical or ascending numerical order.
Does WPS Office support the UNIQUE function?
Yes, WPS Office fully supports the UNIQUE function along with other modern dynamic array formulas, ensuring high compatibility when opening and editing modern Excel workbooks.




