How to Break Excel External Links While Preserving Formulas in WPS Spreadsheets
Question details
The user needs to remove external workbook references from formulas that were copied to another workbook, while keeping the formulas intact and pointing to the local Data worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user copied worksheets into a different workbook, but the pasted formulas still contain references to the original workbook instead of referencing the new local Data worksheet.
- Observed behavior
- Formulas retain links to the original workbook after the worksheet is copied to a new workbook.
Confirm that the destination workbook contains a worksheet named Data. Select a linked cell and check the formula bar to identify the exact external path you need to remove, such as [OriginalWorkbook.xlsx].
Use Find and Replace to Remove the External Workbook Path
Use this when copied formulas contain a consistent external file reference that can be searched and deleted, so formulas keep working with the local Data worksheet.
This method edits the formula text directly. It removes only the external workbook portion, leaving the sheet and cell references intact.
In WPS Spreadsheets, open the target workbook and click Home > Find & Replace > Replace, or press Ctrl+F and switch to the Replace tab.
Click Options in the Replace dialog and set Look in to Formulas so WPS searches formula contents instead of only cell values.
In Find what, type the external workbook path shown in the formula bar, for example [OriginalWorkbook.xlsx]. Leave Replace with blank.
Click Replace All, then select a previously linked cell and confirm that the formula now references Data locally without the original file path.
Copy the Original Workbook and Replace Data Worksheet Values
Use this when you want to avoid editing formulas one by one and already have the updated values in a second Data worksheet.
Break External Workbook Links and Keep Formulas in WPS Spreadsheets
Use WPS Spreadsheets' Find and Replace to delete the original workbook path from formulas, or save a duplicate of the source workbook and replace its Data sheet with the new values. This removes external links without converting formulas to static values.
- 1. Open the workbook: Open the workbook that contains the external links in WPS Spreadsheets.
- 2. Open Replace options: Press Ctrl+F, switch to the Replace tab, and click Options to set Look in to Formulas.
- 3. Remove the external path: Type the external workbook path in Find what and leave Replace with blank.
- 4. Apply and verify: Click Replace All and verify that the formulas now reference the local Data worksheet.
- 5. Save the workbook: Press Ctrl+S to save the cleaned workbook.
Frequently Asked Questions
Why do my copied formulas still point to the original workbook?
When a worksheet is copied to another workbook, formulas that referenced another workbook can keep the original file path. You can see the external path in the formula bar after selecting the linked cell.
Will removing the external path with Find and Replace turn my formulas into values?
No. Replacing the external workbook path with nothing edits the formula text and keeps the formula intact, so it continues calculating with the local worksheet.
Can I remove only one specific external file reference and keep other formulas unchanged?
Yes. In Find and Replace, enter the exact file path you want to remove and replace it with nothing. Formula references to other files remain unchanged.
Do I need a Data worksheet in the destination workbook?
Yes, if the formulas are designed to reference Data. Create or rename a worksheet to Data before removing the external path so the cleaned formulas have a valid local target.
What if my WPS version does not support macro-based cleanup?
You can use the Find and Replace method or the duplicate workbook method described above. Neither requires a macro.
