'%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 Copy Formula Results Instead of Formulas in Excel
Question details
The user wants to copy the calculated results of formulas in Excel and paste them as plain values in another location, without carrying over the underlying formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying calculated data to another worksheet or workbook without transferring dynamic formulas or creating cell reference errors.
- Observed behavior
- Normally, copying and pasting cells with formulas transfers the formula itself, which can result in reference errors or unintended calculations in the new location.
Ensure your worksheet calculations are fully updated and correct before copying, as the pasted values will be static and will not update if the original data changes.
Using Paste Special to Copy Values
The most reliable method to convert formula outputs into static text or numbers is by using the Paste Special function to paste only the values.
By utilizing the Paste Special feature, you instruct Excel to strip away the underlying mathematical formulas and cell references, leaving only the final visible outcome in the destination cells.
Highlight the cells containing the formulas you want to copy. Press Ctrl+C on your keyboard or right-click and select 'Copy'.
Navigate to the destination worksheet or workbook and click on the starting cell where you want to paste the data.
Right-click the destination cell and select 'Paste Special' from the context menu, or press the keyboard shortcut Ctrl+Alt+V.
In the Paste Special dialog box, select the 'Values' radio button and click 'OK'. Your formula results will now appear as static data.
Easily Copy and Paste Formula Results in WPS Office
WPS Spreadsheet provides a seamless and identical Paste Special feature to manage your formulas and static values effortlessly. It offers a user-friendly interface that handles complex calculations with ease.
- 1. Copy Cells in WPS Spreadsheet: Select your calculated cells and press Ctrl+C to copy them to your clipboard.
- 2. Use Paste Special: Right-click the target cell, hover over 'Paste Special' in the context menu, and select 'Paste as Values'.
- 3. Save the Document: Your data is now static. Save your workbook in the standard .xlsx format to retain maximum compatibility.
Frequently Asked Questions
Why do I get a #REF! error when I copy and paste formulas?
A #REF! error occurs when you paste a formula that references cells that do not exist relative to the new location. Using Paste Special > Values prevents this by only copying the final calculated text or number.
Can I use a keyboard shortcut to paste values?
Yes, after copying your cells, press Ctrl+Alt+V to open the Paste Special dialog, press 'V' to select Values, and hit Enter.
Will pasting as values keep my cell formatting?
No, pasting strictly as 'Values' removes borders, colors, and number formats. To keep formatting, select 'Values and source formatting' or 'Values and number formats' in the Paste Special menu.
How do I replace formulas with values in the exact same cells?
Select the cells with formulas, copy them (Ctrl+C), and then immediately right-click and paste as values (the '123' clipboard icon) over the exact same selection.
