Fix Shared OneDrive Excel Formulas Not Updating for Collaborators
Question details
Users need to resolve an issue where Excel formulas referencing an external workbook do not update for colleagues when the main file is shared.
- Product
- Microsoft Excel and OneDrive
- Device & OS
- not provided
- Scenario
- Collaborating on a shared Excel workbook that contains formulas referencing external data stored in another file.
- Observed behavior
- Formulas fail to update for other users, and pressing F9 does not work, because the external source file is stored in a private or local directory that collaborators cannot access.
Ensure you know the exact file path of the source workbook being referenced in your formulas and check the current sharing permissions of that original source file.
Move the Source Workbook to a Shared OneDrive or SharePoint Location
The most effective way to ensure external links update for everyone is to place the source file in a cloud directory that all collaborators can access.
Excel external links rely on absolute or relative file paths. If the referenced file is stored on your local hard drive or in a private OneDrive folder, Excel cannot fetch the data for other users. Relocating the file to a common shared space restores the link.
Find the original Excel workbook that contains the referenced data, which is currently stored in your personal Documents folder.
Move or upload the source file to the same shared OneDrive folder or SharePoint site where the collaborative workbook is currently stored.
Open the shared workbook, navigate to the 'Data' tab on the ribbon, click 'Edit Links' (or 'Workbook Links'), and update the source path to point to the newly shared location.
Copy the Source Data Directly into the Shared Workbook
If you cannot or prefer not to share the external file, you can bring the required data directly into the shared workbook to eliminate external references entirely.
Experience Seamless Cloud Collaboration with WPS Office
If managing Microsoft Excel external links and OneDrive permissions becomes too complex, try WPS Office. It provides a lightweight, highly compatible alternative for creating, sharing, and collaborating on spreadsheets without the heavy subscription costs.
Frequently Asked Questions
Why does pressing F9 not update the formulas for my colleagues?
Pressing F9 forces Excel to recalculate formulas based on available data, but it cannot bypass access restrictions. If the referenced source file is stored locally on your PC or in a private folder, Excel cannot fetch the data for other users, so recalculation fails.
Can I schedule automatic formula updates in shared Excel files?
No, Excel does not feature a built-in way to schedule automatic formula updates for external references. The source file must remain accessible for the links to update successfully when the file is opened or manually refreshed.
How do I find which external links are broken in my Excel file?
Go to the 'Data' tab on the top ribbon and click on 'Edit Links' or 'Workbook Links'. This menu will display a list of all external sources, their file paths, and their current update status, helping you quickly identify any inaccessible files.
