'%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 UPPER Formula Results Without #REF! Error in Excel
Question details
The user wants to copy capitalized text generated by the UPPER formula to another workbook without triggering a #REF! error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transferring or copying text that has been converted to uppercase using formulas into a different spreadsheet or workbook.
- Observed behavior
- A standard copy and paste transfers the formulas instead of the text, referencing missing cells in the destination workbook and resulting in a #REF! error.
Ensure both your source workbook containing the UPPER formulas and your destination workbook are open before attempting to copy the data.
Use the Paste Special Values Keyboard Shortcut
The fastest way to copy formula results as plain text is by using the Paste Special Values shortcut dialog.
This method works across all versions of Excel and ensures only the final capitalized text is transferred, leaving the problematic formula behind.
Select the cells containing your =UPPER formulas and press Ctrl+C to copy them.
Navigate to the destination workbook or sheet, select the first target cell, and press Ctrl+Alt+V to open the Paste Special dialog.
Select the 'Values' option from the list and click OK to paste the plain text.
Use the Home Ribbon to Paste Values
If you prefer using the mouse, the Home tab offers a quick drop-down option to paste formula results as static values.
Manage Formulas Easily with WPS Office
WPS Spreadsheet provides a highly compatible and intuitive interface for managing formulas and pasting values without errors. Easily convert UPPER formula results to plain text in seconds with built-in quick actions.
- 1. Copy the formula results: Select the cells containing the UPPER formula in your WPS Spreadsheet, right-click, and choose 'Copy'.
- 2. Select the target location: Navigate to the destination sheet or workbook and right-click the starting cell.
- 3. Paste as plain values: From the right-click menu, select 'Paste as Values' (the clipboard icon with 123) to safely drop the uppercase text without any #REF! errors.
Frequently Asked Questions
Why do I get a #REF! error when copying formulas to a new workbook?
The #REF! error occurs because a standard copy-paste transfers the underlying formula, not the visible text. The formula then tries to reference cells that do not exist or are out of bounds in the new workbook. Pasting as values prevents this by removing the formula entirely.
Does pasting as values remove the uppercase formatting?
No, pasting as values keeps the capitalized text exactly as it appeared in the source cells. It only removes the underlying =UPPER() formula, leaving the static text behind.
Can I convert the UPPER formula to values in the same column?
Yes. You can select the column containing your UPPER formulas, press Ctrl+C to copy, and then immediately paste it as values directly over the exact same cells to permanently replace the formulas with static uppercase text.
