"Paste Special ⇒ Values and Source Formatting" pastes formulas instead of values when merged cells are present.

Question

I'm using Excel for Mac 16.77.1 in MacOS Monterey 12.7. These are the most recent stable versions of each at the time of this posting.

I have a table in one Excel spreadsheet that I'm trying to paste into another Excel spreadsheet, in a different position [In the original spreadsheet it's in rows 1–18, and in the new spreadsheets it goes in rows 16–33.]

Since the table contains formulas with fixed references (e.g., =G6*G$18), I can't do a regular paste, and instead need Paste Values. In the past this has always worked fine, but I'm now finding that it pastes the formulas instead of the values, giving me "#VALUE!" results in those cells.
The problem only occurs when I try to copy and paste the entire table, and use Paste Special ⇒ Values and Source Formatting (see screenshot below). If I paste the entire table using Paste Special ⇒ Values and Number Formatting, it works fine (I only get values). If I paste a single cell using Paste Special ⇒ Values and Source Formatting, it also works fine (I only get a value).

The problem is neither of the latter two are acceptable for me. I need to copy over the whole table, and I need to retain the original formatting.

I see the same behavior if I do the copy and paste within the same sheet.

I don't know if this is new behavior resulting from the recent updates to Excel, or if it's always worked like this but I never encountered it before.

Answer 1

I determined the issue (and updated the thread title to reflect this): The table I'm copying contains some merged cells. I do get two warning messages about the merged cells (see screenshot at bottom), which I've been ignoring because the merged cells copy over flawlessly (in spite of the messages). Nevertheless, even though the formulas are not in the merged cells, and the merged cells copy over without issue, the mere presence of the merged cells interferes with the operation of Paste Special ⇒ Values and Source Formatting, preventing it from converting formulas to values.

The fix is thus to do two copy-paste operations: One for the rows containing the formulas, and one for the row containing the merged cells.

Note: The reason I don't see the warning messages when I instead do Paste Special ⇒ Values and Number Formatting is because this operation doesn't preserve the merged cells. And since the merged formatting is destroyed, the formulas are properly converted to values.

Essentially, when merged cells are present in the source, Excel treats Paste Special ⇒ Values and Source Formatting as a normal paste operation.

Answer 2

Hi Byte99,

Thanks for posting back and sharing the workaround.

Sincerely,

George | Microsoft Community Moderator