How to Fix Excel VLOOKUP Not Updating Automatically
Question details
The user needs to resolve an issue where a VLOOKUP formula fails to refresh automatically when retrieving data from an external, password-protected workbook.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Working with VLOOKUP formulas that pull data from external, password-protected workbooks.
- Observed behavior
- The VLOOKUP formula does not update its values automatically unless the source workbook is opened or the formula is manually re-entered using F2 and Enter.
Ensure you have the password for the external source workbook and verify that the file path to the linked document has not been changed.
Enable Automatic Calculation and Update External Links
Ensure the spreadsheet is set to calculate formulas automatically and manually force an update of external workbook links.
Go to the 'Formulas' tab on the ribbon, click on 'Calculation Options', and ensure that 'Automatic' is selected.
Navigate to the 'Data' tab and click on 'Edit Links' in the Queries & Connections group.
Select the linked source workbook from the list in the dialog box and click 'Update Values' to force the VLOOKUP data to refresh.

Adjust Protection Settings on the Source Workbook
Modify the protection level of the source file so that external formulas can read the data without requiring the file to be open.
Keep the Source Workbook Open During Updates
A reliable workaround for highly secure files is to keep the source workbook open while working on the destination file.
Use WPS Spreadsheet to Manage External Links and Formulas
WPS Office provides a highly compatible and efficient Spreadsheet tool that seamlessly handles VLOOKUP formulas and external workbook links, ensuring your data stays up to date without hassle.
- 1. Open Files in WPS Spreadsheet: Launch WPS Office and open your destination workbook.
- 2. Check Formula Calculation: Go to the 'Formulas' tab and verify that the calculation mode is set to 'Automatic'.
- 3. Manage External Links: Navigate to the 'Data' tab, click 'Edit Links', and select 'Update Values' to refresh your VLOOKUP data instantly.

Frequently Asked Questions
Why does Excel ask me to save the file every time I open it, even if I haven't made changes?
This usually happens when your workbook contains volatile functions or external links (like VLOOKUPs to other files) that recalculate upon opening. Excel detects this automatic recalculation as a change, prompting you to save.
Can VLOOKUP pull data from a closed, password-protected workbook?
Generally, Excel cannot pull data from a closed workbook if the entire workbook structure is password-protected. You must either open the source workbook or change its protection settings to only protect individual sheets.
Why do I have to press F2 and Enter to make my VLOOKUP work?
This happens when the cell format is set to 'Text' instead of 'General', or when manual calculation is enabled. Pressing F2 and Enter forces Excel to re-evaluate the cell contents as a formula.
How can I check if my workbook has broken external links?
Go to the 'Data' tab and click on 'Edit Links'. This dialog box will list all external sources. Check the 'Status' column to see if any links are broken, unknown, or require an update.




