'%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 Excel Formulas Without Changing Destination Formatting
Question details
The user needs to copy calculations from one worksheet or workbook to another without modifying the existing visual formatting of the destination cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transferring formulas and calculations into a newly designed spreadsheet where custom colors, borders, and fonts are already applied.
- Observed behavior
- By default, standard copy and paste commands transfer both the formula and the source formatting, which overwrites the desired destination design.
Make sure both your source and destination workbooks are open, and verify that the destination cells are not accidentally formatted as 'Text', which would prevent formulas from calculating.
Use Paste Special to Transfer Only Formulas
This is the most reliable method to copy calculations while leaving your existing cell styles, borders, and background colors completely untouched.
The 'Paste Special' menu provides granular control over exactly which attributes of a copied cell are transferred to the new location.
Open Workbook A, highlight the range of cells containing the formulas you want to duplicate, and press Ctrl+C (or right-click and select Copy).
Switch to Workbook B, navigate to the specific worksheet, and click on the top-left cell of your intended destination range.
Right-click the selected destination cell to open the context menu, then select 'Paste Special...' to open the detailed pasting dialog box.
In the Paste Special dialog box, select the 'Formulas' radio button (or click the 'fx' icon under Paste Options) and click OK. The formulas will appear in the new cells without altering the existing design.
Seamlessly Manage Formulas and Formatting with WPS Spreadsheet
WPS Office provides highly intuitive data manipulation tools, including advanced Paste Special functions. You can easily transfer formulas, values, or formats across worksheets while enjoying full compatibility with Microsoft Excel files.
- 1. Copy Your Data: Open your file in WPS Spreadsheet, highlight the cells with the formulas, and press Ctrl+C.
- 2. Select the Target Cell: Switch to your destination worksheet and click the starting cell where you want the formulas to go.
- 3. Paste as Formulas: Right-click the target cell, hover over 'Paste Special', and select 'Formulas' to transfer calculations while retaining the original cell design.
Frequently Asked Questions
Why are my pasted formulas displaying as text instead of calculating?
This commonly happens if the destination cells were previously formatted as 'Text'. To fix this, change the cell format of the destination cells to 'General' or 'Number', then click into the formula bar and press Enter to recalculate.
Can I paste formulas as plain values to lock the data?
Yes. If you want to remove the underlying formula and just keep the calculated result, copy the cells, right-click the destination, and choose 'Values' from the Paste Special menu.
Do relative cell references change when pasting formulas?
Yes, by default Excel uses relative referencing, meaning cell references will adjust based on the formula's new location. If you want to keep the exact same references, you must change them to absolute references (e.g., $A$1) in the source formula before copying.
How can I copy both formulas and number formatting without copying cell borders or colors?
In the Paste Special dialog box, select the option for 'Formulas and number formats'. This transfers the calculation and the way numbers are displayed (like currency or percentages) without overriding cell background colors or borders.
