Fix Excel UNIQUE Function Not Refreshing Spilled Results in WPS Office
Question details
The user needs to resolve an issue where an Excel UNIQUE formula referencing a table column does not automatically include newly added table items in an older workbook; pressing Enter manually refreshes the spilled results.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains an older workbook with a UNIQUE(Table[Column]) formula and adds a new row to the source table, expecting the spilled results to update automatically.
- Observed behavior
- The spilled results do not refresh immediately after adding the table item. Pressing Enter updates the formula output, and the issue does not appear in a newly created workbook.
Before rebuilding the workbook, open the affected file in WPS Office Spreadsheets and verify that the UNIQUE formula references a structured table column. Note which data ranges, formulas, and defined names you need to preserve before copying them to a new workbook.
Move the data and formulas into a new blank workbook
Use this when the UNIQUE spill issue appears in only one older workbook and pressing Enter updates the results, because the original file may be corrupted.
Workbook-specific corruption can prevent dynamic array formulas from reacting to table changes. Rebuilding the workbook removes hidden structural damage while keeping your data and formulas intact.
In WPS Office Spreadsheets, click File > New > Blank workbook to create a clean workbook.
Return to the older workbook, select the source table or data range, right-click and choose Copy, or press Ctrl+C.
In the new workbook, right-click the destination cell and choose Paste Special > Values to paste data without formatting and rule out formatting-related issues.
Recreate or copy the UNIQUE formula and any required formulas. For defined names, use Formulas > Name Manager to review and recreate them in the new workbook.
Add a new item to the table in the new workbook and confirm that the UNIQUE spilled results refresh automatically without pressing Enter.
Check calculation mode and table references before rebuilding
Use this first if you want to rule out simple calculation or reference issues before copying the workbook.
Rebuild an Older Excel Workbook in WPS Office to Restore UNIQUE Auto-Refresh
Download WPS Office, open the affected workbook, create a new blank workbook, copy the data and formulas, and test UNIQUE to remove workbook-specific corruption. WPS Office keeps your Excel data and dynamic array formulas compatible.
- 1. Download and install WPS Office: Go to wps.com, download WPS Office Free, and install it on your Windows or Mac computer.
- 2. Open the affected Excel workbook: Launch WPS Office Spreadsheets and open the older Excel workbook that contains the UNIQUE formula.
- 3. Create a new blank workbook: Click File > New > Blank workbook to create a clean destination workbook.
- 4. Copy data and formulas: Copy the source table data into the new workbook, then recreate or copy the UNIQUE formula and any defined names with Formulas > Name Manager.
- 5. Test the UNIQUE spill update: Add a new table item in the new workbook and verify that the spilled results update immediately.
Frequently Asked Questions
Why does my UNIQUE formula only refresh after I press Enter?
In an older workbook, this is often caused by workbook structure corruption. Create a new blank workbook in WPS Office, copy the data and formula, and test UNIQUE there. If it updates automatically, the old file structure was the problem.
Can I keep my defined names when moving data to a new workbook?
Yes. Open WPS Office Spreadsheets, go to Formulas > Name Manager to review the defined names in the original workbook, then recreate them in the new workbook. You can also copy formula cells after pasting values.
How do I copy data without formatting to test workbook corruption?
Select the source range in WPS Office Spreadsheets, press Ctrl+C, right-click the destination cell, and choose Paste Special > Values or Values & Number Formats. This removes formatting as a variable.
Does WPS Office support the Excel UNIQUE dynamic array function?
Yes, WPS Office Spreadsheets can open Excel workbooks and work with dynamic array functions such as UNIQUE. Test the formula in a new blank workbook to confirm that your version handles spilled array updates as expected.
