Fix Excel VLOOKUP Data Connections Not Refreshing in WPS Office Spreadsheets
Question details
The user needs to identify and correct why VLOOKUP formulas tied to external links, queries, or data connections do not update until Enable Content is selected or a manual refresh is performed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Opening an Excel workbook in WPS Office Spreadsheets where VLOOKUP formulas rely on external data sources.
- Observed behavior
- Values remain stale after opening or only recalculate after clicking Enable Content.
Open the workbook in WPS Office Spreadsheets and use Data > Edit Links or Connections to list the external sources feeding the lookup cells. Set Formulas > Calculation Options to Automatic so refreshed data is recalculated immediately.
Review and Refresh External Data Connections
Use this first when a VLOOKUP result comes from an external link or query and the value does not update after opening the file.
VLOOKUP formulas often return stale values because the underlying connection or external link has not refreshed. WPS Spreadsheets lets you review, update, and schedule these sources from the Data tab.
In WPS Office Spreadsheets, open the affected workbook and click the Data tab. Select Edit Links or Connections to list external sources used by the workbook.
Select the connection or link that feeds the VLOOKUP range, then click Refresh or Update Values to pull the latest source data.
Open the connection properties and enable Refresh data when opening the file if the source should be updated every time the workbook is opened. Click OK to save.
Set Calculation to Automatic and Verify Lookup Ranges
Use this when the data connection refreshes but the VLOOKUP cells still show old results.
Check Trust Center External Content Settings
Use this if the workbook updates only after clicking Enable Content and the connection is already valid.
Fix Excel VLOOKUP Data Connections in WPS Office Spreadsheets
Use WPS Office Spreadsheets to review external links, refresh queries, set automatic calculation, and update VLOOKUP results in Excel-compatible workbooks without changing your existing files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the VLOOKUP formulas.
- 2. Inspect external sources: Click the Data tab and select Edit Links or Connections to see which workbooks, queries, or external ranges feed the formula cells.
- 3. Refresh and configure: Select the source, click Refresh, and enable Refresh data when opening the file in the connection properties if needed.
- 4. Set automatic calculation: Open the Formulas tab, choose Calculation Options > Automatic, and recalculate the sheet to update the VLOOKUP cells.
- 5. Save and verify: Save the file in its original Excel-compatible format and reopen it to confirm the VLOOKUP values refresh correctly.
Frequently Asked Questions
Why does my VLOOKUP formula only update after I click Enable Content?
This usually means the workbook contains external links, queries, or data connections that are blocked until you allow them. In WPS Spreadsheets, review Data > Edit Links or Connections and, for trusted files, enable automatic refresh or trusted content in File > Options > Trust Center.
Where do I refresh Excel data connections in WPS Office?
Open the workbook, go to the Data tab, and select Edit Links or Connections. Select the external source and click Refresh or Update Values to pull the latest data.
How do I make a data connection refresh automatically when opening the workbook?
Open the connection properties from Data > Connections or Edit Links, enable Refresh data when opening the file, save the workbook, and reopen it from a trusted location.
Why does VLOOKUP still show the old value after the connection refreshes?
Check that calculation is set to Automatic under Formulas > Calculation Options, and verify that the VLOOKUP range and data types match the refreshed source data.
Can WPS Office refresh workbooks created in Microsoft Excel?
Yes. WPS Spreadsheets supports Excel-compatible workbooks, external links, queries, and data connections, so you can refresh and edit these files without losing the original format.
