How to Copy Excel Formulas Without Keeping References to the Original Sheet in WPS Office
Question details
The user needs to copy formulas between workbooks in WPS Office so the pasted formulas use the destination workbook's own cell and sheet references, rather than continuing to point to the original sheet or external file.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is moving formula-based workbooks or sheets and wants the destination workbook to recalculate formulas locally instead of carrying over source workbook links.
- Observed behavior
- Formulas containing worksheet names, workbook names, or external references may continue to point to the original file after being copied; simple relative formulas such as =A1+B1 usually copy correctly.
Open the source and destination workbooks in WPS Office and locate the formulas that contain sheet names, workbook names, absolute references, or external links before you copy them.
Copy Formulas as Text by Adding an Apostrophe Before the Equals Sign
Use this method when formulas contain sheet names or workbook references and you want the destination workbook to evaluate them with its own cells.
Adding an apostrophe tells WPS Spreadsheets to treat the formula as text, so the cell reference string is copied exactly without being re-anchored to the original workbook.
In WPS Office Spreadsheets, open the source workbook and select the cells that contain the formulas you want to copy.
Click the formula bar and insert an apostrophe before the equals sign, for example '=A1+Sheet2!B1. Press Enter so the cell displays the formula as text.
Press Ctrl+C to copy the selected text cells, switch to the destination workbook, select the target cell, and press Ctrl+V.
Click each pasted cell, delete the apostrophe before the equals sign, and press Enter so WPS Spreadsheets recalculates the formula using the new workbook's references.
Check formulas that contain absolute references such as $A$1, sheet names like Sheet2!B1, or external links like [Book1.xlsx]Sheet1!A1 to confirm they point to the intended cells.
Copy Normally and Remove External Workbook References with Find and Replace
Use this if you already pasted formulas and only need to clean up remaining original workbook paths or sheet references.
Copy Excel Formulas in WPS Office Without Old Workbook References
Use WPS Spreadsheets to copy formulas as text, paste them into the destination workbook, and remove the apostrophe. The formulas then recalculate with the new file's references instead of pointing back to the original sheet.
- 1. Open both workbooks in WPS Spreadsheets: Launch WPS Office, open the source workbook and the destination workbook, then click the sheet tab that contains the formulas.
- 2. Convert formulas to text: Insert an apostrophe before the equals sign in each formula, for example '=A1+Sheet2!B1, and press Enter.
- 3. Copy and paste to the destination: Select the text-formula cells, press Ctrl+C, switch to the destination workbook, select the target cell, and press Ctrl+V.
- 4. Remove the apostrophe and recalculate: Delete the apostrophe in the pasted cells and press Enter so WPS Spreadsheets evaluates each formula with the new workbook's references.
- 5. Review sheet, absolute, and external references: Check formulas containing $A$1, Sheet2!B1, or [Book1.xlsx]Sheet1!A1 to make sure they point only to intended cells.
Frequently Asked Questions
Why does copying a formula keep the original sheet reference?
It usually happens with formulas that include sheet names, workbook names, or external links, such as =Sheet2!B1 or =[Book1.xlsx]Sheet1!A1. Convert the formula to text with an apostrophe before copying, then remove the apostrophe in the destination workbook.
Can I copy simple formulas like =A1+B1 without changing anything?
Yes. Simple relative formulas usually copy without keeping the original sheet reference. If you are not sure, use the apostrophe text method for a predictable result.
How do I remove the apostrophe from many formulas after pasting?
Select the pasted cells, press Ctrl+H to open Find and Replace, enter '= in Find what and = in Replace with, then click Replace All. This makes WPS Spreadsheets evaluate the formulas again.
Does this method work when moving formulas between WPS Spreadsheets and Microsoft Excel files?
Yes, because the formula text is copied without the file path and WPS Spreadsheets supports Excel-compatible .xlsx formulas. Check that sheet names and absolute references exist in the destination file.
