Copy Excel Formulas Between Workbooks Without External References in WPS Spreadsheets
Question details
The user needs to copy formulas between similarly structured Excel workbooks while preventing the destination workbook from keeping references to the original source file.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Copying formulas between two similarly structured workbooks, such as budget or report templates, without creating external source links.
- Observed behavior
- Formulas retain references to the original source workbook after being copied, which can create external link prompts or broken references.
Open both the source and destination workbooks in WPS Spreadsheets and confirm the sheet structures match. If you are copying individual formulas, note the source workbook name so you can update it in the Edit Links dialog.
Move or Copy the Whole Worksheet and Update the Source Link
Use this method when the entire sheet structure is the same and you want to keep formulas, formatting, and layout together.
Moving or copying the worksheet first brings all formulas into the destination workbook. WPS Spreadsheets may still show an external link, so the final step is to change the source to the destination workbook.
Open the source workbook and the destination workbook in WPS Spreadsheets.
Right-click the sheet tab you want to copy, choose Move or Copy, select the destination workbook, check Create a copy if needed, and click OK.
In the destination workbook, go to Data > Edit Links.
Select the old source workbook in the list, click Change Source, and choose the current destination workbook.
Click Close, then save the destination workbook and check that formulas calculate correctly.
Update Individual Copied Formulas with Change Source
Use this method when you only need to copy selected formulas rather than an entire worksheet.
Use WPS Spreadsheets to Copy Excel Formulas Without External References
WPS Spreadsheets provides a familiar spreadsheet workspace where you can move or copy worksheets and use Data > Edit Links > Change Source to redirect formulas to the current workbook, removing unwanted external references.
- 1. Open both workbooks: Launch WPS Spreadsheets and open the source and destination workbooks.
- 2. Copy the worksheet: Right-click the source sheet tab, choose Move or Copy, select the destination workbook, and click OK.
- 3. Edit workbook links: In the destination workbook, go to Data > Edit Links.
- 4. Change source: Select the old source file in the list, click Change Source, and choose the destination workbook.
- 5. Save the workbook: Save the destination workbook and verify that formulas no longer reference the original file.
Frequently Asked Questions
Why does Excel keep a reference to the original workbook when I copy formulas?
Copied formulas can retain the source file path when relative references are interpreted as external links. In WPS Spreadsheets, open Data > Edit Links to see and change the linked workbook.
How do I remove external links from a copied worksheet in WPS Spreadsheets?
After copying the worksheet to the destination workbook using Move or Copy, go to Data > Edit Links, select the source workbook, click Change Source, and choose the destination workbook.
Can I update only selected copied formulas instead of the whole sheet?
Yes. Paste the selected formulas into the destination workbook, then open Data > Edit Links, select the old source, click Change Source, and choose the destination workbook.
Will Move or Copy keep my sheet formatting and formulas?
Yes. Move or Copy transfers the entire sheet, including formulas, formatting, and layout. If you check Create a copy, the source sheet also remains in the original workbook.
What should I check after changing the source link?
Click a copied formula cell and look at the formula bar to confirm the original file path is gone, then save the workbook.
