'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Prevent Excel Links from Changing After Save As
Question details
The user wants to stop formulas in a destination Excel workbook from automatically updating to reference a newly copied workbook when the source workbook is saved under a new name.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using the 'Save As' function on a source workbook while the linked destination workbook is also open.
- Observed behavior
- Formulas in the linked workbook automatically change their reference to the newly saved copy instead of maintaining the link to the original source workbook.
Verify that you know the exact file path and name of your original source workbook, as you will need to manually point Excel back to this file.
Restore the Original Link using the Change Source Feature
Excel automatically updates open linked workbooks during a 'Save As' operation by design. The most effective resolution is to let the change happen and manually revert the link source back to the original file.
Because Microsoft Excel assumes you want to keep open workbooks connected, it dynamically updates external links when you rename or copy a source file using 'Save As'. Since there is no setting to disable this specific behavior while both files are open, manually changing the source afterward is required.
In your open destination workbook (the one containing the formulas), click on the 'Data' tab located on the top Excel ribbon.
Click on 'Workbook Links' (labeled as 'Edit Links' in older versions of Excel) located within the Queries & Connections group.
Select the link that was updated to the new copied workbook. Click the 'Change Source' button, browse your computer for the original source workbook, and click 'OK' to restore the correct references.
Easily Fix Changed External Links using WPS Office
WPS Spreadsheet provides an intuitive Edit Links tool that allows you to quickly track down, update, or restore your formulas to the original source workbook after making copies of your files.
- 1. Open your linked workbook: Launch WPS Spreadsheet and open the destination file containing the formulas that have been updated to the wrong source.
- 2. Access the Edit Links manager: Navigate to the 'Data' tab on the top menu ribbon and click on 'Edit Links'.
- 3. Restore the original source: Select the affected external link from the dialog box, click 'Change Source', and select your original workbook to immediately fix the formulas.
Frequently Asked Questions
Why do external links change when I use Save As?
When both the source and destination workbooks are open, Excel dynamically manages their relationship. Saving the source file under a new name signals Excel to automatically update the open destination workbook so it continues to point to the active, newly named file.
How can I prevent the links from changing in the first place?
The easiest and most reliable method is to close the destination workbook (the file with the external formulas) before you use the Save As function on your source workbook. Since it is closed, Excel cannot update its formulas.
Can I lock an external link formula so it never updates?
Excel does not have a native feature to permanently lock an external link path while the destination file is open during a Save As. The standard fix is either closing the file beforehand or using the Change Source tool afterward.
Where do I find the Edit Links option if my Excel version doesn't show Workbook Links?
In older versions of Microsoft Excel (like Excel 2016 or 2019), the feature is simply called 'Edit Links'. You can find it under the Data tab in the Connections group.
