Bug in Excel: Paste Values & Source formatting pastes formulas

Question

... a whole sheet into a new .xlsx workbook, newest office 365.

Answer 1

We’ve received an update from our Internal Support team. The prompt when Pasting Values & Formatting is normal behavior in Excel. It's just letting the user know to be cautious when pasting especially if the field to paste the table has been formatted. We suggest that you submit this feedback on our Excel UserVoice platform. This way our Developers will be aware of your concern and considering making changes to the application in the future updates based on what features are the most important to you.

In line with your second issues that you’ve raised with us, we’re still waiting for the sample file for us to further isolate this concern accordingly. We’ll wait for that information, and we’ll get back to you as soon as possible with the answer.

If you need more advice from us, you can get back to us by replying to this thread or create a new thread if you have a different concern.

Answer 2

Thanks for all the information, Peter. The scenario that you posted to create the bug was replicated on our end as well. We got the same results. This concern is now under investigation and we are coordinating with our Internal Support team to get this addressed as soon as possible. We’ll let you know once we reach a resolution from them. Please standby.

As far as the receiving of a budget summary when you send it via email, would you be able to send us a sample file. This way we can replicate the issue properly and for us to provide an accurate solution. You may send it through the private message for security purposes. To get into your inbox, click your profile name on this page and select View private messages.

To notify us and have us get back to you, simply reply to this post or to the message that we’ve sent to you.

Answer 3

Subscription Product: Microsoft Office 365

About Excel: Version 1812 (Build 11126.20266 Click-to-Run) Monthly Channel

I guess this bug exist since ever;

More detailed description:

First something that works as intended:

1. File:New:Blank workbook
2. Enter Formula =1+2 into cell A1
3. Set cell A1 fill color to yellow
4. Mark cell and right click:copy
5. Paste into cell A2 using Home:Paste:Values & Source Formatting

=> this works es expected! Cell A2 contains the number "3" (no formula) and is yellow.

How to create the bug:

1. File:New:Blank workbook "Book1"
2. Enter "1" in cell A1
3. create new sheet "Sheet2"
4. Mark cells A1 and B1 and merge them
5. enter formula "=Sheet1!A1" into Sheet2!A2
6. mark whole Sheet2 and "copy"
7. File:New:Blank workbook "Book2"
8. Home:Paste:Values & Source Formatting

=> two Dialog boxes appear:

"To do this, all the merged cells need to be the same size"

"You can't paste this here because the copy and the paste area aren't the same size. Select just one cell in the paste area or an area that's the same size and try pasting again."

9. I acknowledge both with "Ok"

=> Book2 Sheet1 Cell A1 and B1 are correctly merged

=> Book2 Sheet1 Call A2 contains the formula "=[Book12]Sheet1!A1". It should, however, contain the value "1".

As a workaround, it works to past values and source formatting separately, after step 7):

8a) Home:Paste:Values

8b) Home:Paste:Formatting

=> no dialogs appear

=> Cells are merged and formula is correctly replaced by value.

It may not look like a bug which is encountered often, but I wanted to send a copy of a budget summary sheet via e-mail, without the calculation details on the other sheets. While the new workbook created looked correct to me, the receiver saw only "#REF, #REF, ...". Fortunately, we did not miss any deadline.

Answer 4

Hi PeterSteier,

Could you elaborate your concern for us to further assist you? Also, may we know which build and version of Excel is installed on your computer? To get this information, you can check this link. Could you also tell us when did the issue start?

To notify me and have me get back to you, simply reply to this post.