Excel always pasting both value and source formatting

Question

I'm trying to copy ONLY values and keep the DESTINATION formatting when pasting across workbooks (even within the same workbook) - and no matter how I select to do the paste, the data is ALWAYS showing with the SOURCE formatting. This is annoying as then I have to go column by column to select the entire range of data then use then click on the little alert icon and change the formatting (normally from text to number, though I often need to go in after and set a custom format for thing that show with preceding zeros).

I've looked online for solutions, and the mention going into Options -> Advanced and changing the default paste options, but the menu I see in the version of Excel I'm using doesn't have those options. Using Microsoft 364 - version 2303 at work... (Apps for Enterprise). Other solutions also provided don't seem to work (one suggested Ctrl-Shft-V but that does nothing.)

It's getting really annoying and I have to copy/paste large quantities of data between spreadsheets on a weekly or monthly basis, and having to adjust formatting all the time is a waste of time...

Answer 1

Select the source cell.

Copy it.

Select the Target cell.

Click the lower half of the Paste button on the Home tab of the ribbon.

Select Values or press V.

Answer 2

If it were that simple, I wouldn't have posted the question...

I tried all the available options under paste... still copying in value AND source formatting.

Colin

Answer 3

I'm sorry, I cannot explain that. Paste Values only pastes the value for me, ignoring the source formatting.

Answer 4

I'm trying to copy ONLY values and keep the DESTINATION formatting when pasting across workbooks (even within the same workbook)

I've never seen that problem when doing paste-value in the same workbook. Or even across workbooks in the same instance of Excel.

However, when pasting across workbooks in separate instances of Excel -- we can only paste values, in that case -- you are correct that Paste copies the source format as well as the value.

We should be able to work around that by selecting Match Destination Formatting.

But that does not work as expected in my version of Excel (2010). YMMV.

(It might work as expected when the source is not Excel.)

You might use the Feedback feature to report the misbehavior(?). But honestly, I doubt that MSFT would change it, considering how long it has been behaving that way.

Answer 5

Well, I am going between workbooks, so maybe its an issue with two instances of Excel running... as I have my "master spreadsheet" and then add data to it from a report download that I open seperatately.

I'll try opening it from within the running application and see if that doesn't fix the problem..

Thanks.