How to Stop Excel Formulas from Linking to the Original Workbook
Question details
The user needs to prevent Excel formulas from retaining external links to an original SharePoint workbook after the file has been copied or saved to a new folder.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying or using 'Save As' on an Excel workbook originally hosted on SharePoint to a local or different folder.
- Observed behavior
- Formulas continue to explicitly reference the original SharePoint file's path rather than updating to use internal worksheet references within the newly copied file.
Before modifying external links or replacing formula text in bulk, verify the exact name of your original SharePoint file and unhide any hidden sheets to ensure all formulas are updated.
Update External Links Using Find and Replace
The fastest method to convert external links back to internal references is by finding the original workbook's name in the formulas and replacing it with nothing.
When an internal reference accidentally converts to an external one, the formula changes from '=Sheet1!A1' to something like '=[OriginalWorkbook.xlsx]Sheet1!A1'. Removing the bracketed workbook name restores the internal link.
Press the Ctrl + H keyboard shortcut to open the Find and Replace dialog box.
In the 'Find what' field, type the name of the original SharePoint workbook exactly as it appears in your formula, including the brackets (e.g., [OriginalWorkbook.xlsx]).
Leave the 'Replace with' field completely empty so the external file name is deleted from the formula.
Click 'Options' to ensure you are searching within 'Workbook' and looking in 'Formulas', then click 'Replace All'.
Change Source via Edit Links
Use the built-in Edit Links tool to redirect the workbook's external references to the current file itself.
Manage and Break External Links Easily with WPS Spreadsheet
WPS Spreadsheet provides an intuitive Edit Links manager and powerful Find & Replace capabilities to help you seamlessly fix external references after copying files from shared environments like SharePoint.
- 1. Open the File in WPS Spreadsheet: Launch WPS Office and open the copied workbook containing the unwanted external links.
- 2. Navigate to the Data Tab: Click on the 'Data' tab in the top ribbon and locate the 'Edit Links' option.
- 3. Modify or Break the Link: Select the original SharePoint file link. Choose 'Change Source' to point it to your current file, or 'Break Link' to convert those specific formulas into static values.
Frequently Asked Questions
Why do my formulas point to the old workbook after saving to a new folder?
When moving files from SharePoint or using Save As, Excel occasionally treats copied sheets as distinct files. If the sheets were copied individually or referenced strictly, Excel retains the explicit file path (like =[OldFile.xlsx]Sheet1!A1) instead of converting it to a relative internal reference.
Will 'Breaking Links' delete my data?
No. Breaking a link will not delete the data currently visible in your cells. However, it will remove the formula entirely and replace it with the last calculated static value. You should only use Break Links if you no longer need the formula to update.
How can I find all external links hidden in my workbook?
You can find hidden links by pressing Ctrl + F to open the Find dialog. Type the left bracket symbol '[' into the search box, click 'Options', set 'Within' to 'Workbook' and 'Look in' to 'Formulas', then click 'Find All'.
Do Defined Names cause external link errors?
Yes. If you created a Defined Name in the original workbook that references an external path, copying the sheet might copy that Named Range too. You must go to Formulas > Name Manager to delete or update these references.
