'%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 and Paste Formula Results as Values in Excel Without Blank Cells
Question details
The user needs to paste the calculated results of formulas as static text or numbers without copying the underlying formulas themselves.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Transferring or duplicating data that was generated by formulas into another location within the spreadsheet.
- Observed behavior
- Standard copy-pasting transfers the formula with relative references, leading to blank cells, incorrect calculations, or green error indicators.
Ensure that your spreadsheet is set to automatic calculation so the formula outputs are completely up-to-date before you attempt to copy them.
Use Paste Special to Paste Values Only
Using the Paste Special feature allows you to strip away the formula and keep only the final displayed result in the destination cell.
This is the standard and most reliable method to convert dynamic formula outputs into static values. It prevents reference errors when moving data to a new sheet or workbook.
Highlight the cells containing your formula-generated results and press Ctrl + C, or right-click and select Copy.
Click on the top-left cell of the destination area where you want the static values to appear.
Right-click the destination cell. Instead of clicking the standard Paste icon, look for 'Paste Special' in the context menu.
In the Paste Special options, select 'Values' (often represented by an icon with a clipboard and '123') and click OK.
Use WPS Spreadsheet for Flawless Copy and Paste
WPS Office provides an intuitive and robust Spreadsheet tool that effortlessly handles complex formulas and data formatting. It perfectly mirrors the Paste Special functionality you are used to, making data management seamless.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the calculated formulas.
- 2. Copy the data: Select the cells containing the results you want to extract and press Ctrl+C.
- 3. Paste as static values: Right-click your destination cell, hover over 'Paste Special', and choose the 'Paste as Values' (123) icon.
Frequently Asked Questions
Why do I get blank cells or REF! errors when I copy and paste formulas?
When you use a standard paste, the spreadsheet software copies the underlying formula using relative references. If the formula looks at cells that are empty in the new location, it returns a blank or an error. Pasting as values prevents this by only copying the final text or number.
Can I convert an entire column of formulas into values in place?
Yes. Select the entire column containing the formulas, copy it, then right-click on the same column header and select 'Paste Special' > 'Values'. This overwrites the formulas with their static results.
How do I keep the formatting when pasting as values?
To retain the cell colors, borders, and number formats while stripping the formulas, use the Paste Special menu and select 'Values & Source Formatting'. This ensures your data looks exactly as it did originally but remains static.
