'%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)
Fix Excel Paste Special Pasting Formulas with Merged Cells
Question details
The user is trying to paste values using Paste Special, but the application incorrectly pastes formulas instead when the source table contains merged cells.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Copying and pasting data that contains merged cells using the 'Values and Source Formatting' option in Paste Special.
- Observed behavior
- Excel retains and pastes the formulas instead of converting them to static values.
Before trying the workarounds, review your source data and identify exactly which rows or columns contain the merged cells causing the conflict.
Copy and Paste Formula Rows Separately
Bypass the merged cell conflict by copying the formula rows individually, avoiding the rows that contain merged cells.
Since the issue is triggered directly by selecting merged cells alongside formula cells, isolating your selection prevents the glitch from occurring.
Highlight only the specific rows or cells containing the formulas you want to convert to values, intentionally leaving the merged cells out of your selection.
Press Cmd + C on your Mac keyboard to copy the selected formula cells.
Right-click the destination cell where you want the data to appear, and select 'Paste Special' from the context menu.
Select the 'Values' option and click OK. The formulas will now correctly convert to static numbers.
Use Values and Number Formatting
Use an alternative Paste Special option that ignores source formatting (and thus ignores merged cells) while still pasting the values correctly.
Experience Seamless Data Handling with WPS Office
Struggling with Excel's copy and paste quirks on Mac? WPS Office offers a free, lightweight, and highly compatible alternative that handles formatting, merged cells, and values effortlessly without unexpected formula errors.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your Mac.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file.
- 3. Paste without errors: Use the Paste Special feature securely knowing that formatting and values will be handled correctly.
Frequently Asked Questions
Why does Paste Special paste formulas instead of values when using Source Formatting?
This is a known behavioral issue in Excel for Mac. When a copied range includes merged cells, selecting 'Values and Source Formatting' causes the application to incorrectly prioritize the source layout, unintentionally carrying over the underlying formulas instead of converting them to static values.
How do I remove merged cells before copying my data?
Select the cells you want to unmerge, go to the Home tab on the ribbon, click the drop-down arrow next to 'Merge & Center', and select 'Unmerge Cells'. You can then copy and paste the data normally.
Does 'Values and Number Formatting' keep my cell colors and borders?
No. The 'Values and Number Formatting' option only retains the number formatting (such as currency or date formats) and the actual text or numerical values. It strips away background colors, cell borders, and font styles, which avoids the merged cell issue entirely.
