'%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 Paste an Excel Formula as Text into an Adjacent Cell
Question details
The user needs to paste an existing Excel formula as raw text into an adjacent cell to display it without calculating, but their attempt using an apostrophe to force text formatting is failing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying a formula to display it purely as text for documentation, sharing, or auditing purposes.
- Observed behavior
- Pasting the copied formula fails when an apostrophe is entered first into the destination cell to force text formatting.
Ensure you are copying the exact text of the formula directly from the formula bar, rather than copying the cell itself, to prevent Excel from automatically adjusting relative cell references.
Format the Destination Cell as Text Before Pasting
Pre-formatting the destination cell as Text prevents Excel from trying to calculate the formula, allowing you to paste the string exactly as copied without needing an apostrophe.
This is the most reliable method for pasting formulas as text because it changes the inherent property of the destination cell before any data is entered, eliminating the need for manual prefixing.
Click on the adjacent cell where you want the formula to appear as text.
Navigate to the 'Home' tab on the ribbon. In the 'Number' group, click the dropdown menu and select 'Text'.
Select the original cell containing the formula, highlight the entire formula text in the formula bar, and press Ctrl+C to copy.
Double-click the formatted destination cell (or click into the formula bar) and press Ctrl+V to paste the formula text. Press Enter.
Use the Apostrophe Prefix Correctly
If you prefer using an apostrophe to force text formatting, you must paste the formula in edit mode rather than pasting over the cell.
Use the FORMULATEXT Function
For a dynamic and automated approach, use the FORMULATEXT function to extract and display the formula from an adjacent cell as text.
Effortlessly Manage and Display Formulas with WPS Office
WPS Spreadsheet provides robust formatting tools and built-in functions like FORMULATEXT, making it incredibly easy to extract, audit, and display your formulas as text without running into calculation errors.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open your workbook.
- 2. Format cell as text: Select your destination cell, go to the Home tab, and choose 'Text' from the number format dropdown.
- 3. Copy and paste safely: Copy the text directly from the formula bar of the source cell and paste it into the pre-formatted destination cell.
Frequently Asked Questions
Why does my pasted formula still calculate even after adding an apostrophe?
This happens if you paste the entire copied cell rather than the raw text, which overwrites the apostrophe. To fix this, copy the formula text directly from the formula bar, double-click the new cell, type the apostrophe, and paste the text immediately while still in edit mode.
What is the fastest way to show all formulas in a worksheet instead of their values?
You can toggle the display of all formulas in your worksheet by pressing the shortcut key combination Ctrl + ` (the grave accent key, usually located below the Escape key). Press it again to return to showing the calculated values.
Can I prevent Excel from changing my cell references when I copy a formula?
Yes. If you copy the entire cell and paste it elsewhere, relative references will shift. To prevent this, copy the exact text from the formula bar and paste it, or add a dollar sign ($) before the column letters and row numbers in your original formula to make them absolute references.
