Excel copy-paste: always match destination formatting

Question

I use Excel to crunch numbers, so I usually don't want to copy the formatting along with the text. But this is the default. Is there a way to default to always match destination formatting when pasting into Excel?

Solution 1

Can't set the default, but more convenient than fiddling with the paste options after each paste:

If you press the Backspace or F2 key before pasting text it will paste the text only, retaining the existing formatting.

source: http://appscout.pcmag.com/business-financial/272436-always-match-destination-formatting

I wonder why this works?

Solution 2

Simply double click on the cell first before pasting.

Solution 3

Try ALT+E+S+V+ENTER are the key strokes.

You could place a macro in your Personal personal excel workbook for pasting special, but you would not be able to undo the action after.

Solution 4

Paste with ctrl+V then hit ctrl to open a one key menu with formatting options. By default the letter is V to set the pasted information to the destination cell's former/proper formatting.

A little weird that the ctrl+V and ctrl, then V have such disparate effects, but nonetheless to past and keep the destination formatting:

Copy cell or range of cells, paste to new locations using ctrl+V like you would to move the source formatting, then hit ctrl then V to select a change from the current source formatting into the destination formatting.

ctrl+V, then ctrl, then V. a much quicker method than point and click.

Solution 5

In Excel 2013, use the ordinary Ctrl+V shortcut, then press Ctrl, then press M.

View more solutions

Comments

[Scott - Слава Україні]

This doesn’t work for me in Excel 2007.

[redcalx]

Note. To make this work you ahve to double click on the cell you are copying, select the cell text, ctrl-c. You can then paste anywhere without having to double-click on the destination cell. This will copy the text only and not the formatting.

[RobG]

It works because it puts the cell into edit mode rather than text entry mode. It's equivalent to double–clicking in the cell.

[Nate]

Thank you for this! It works to paste multiple cells (e.g. a whole table from a webpage or something) AND it has a keyboard combo to activate.

[Nate]

This only works for pasting one cell at a time. If you have many cells in a column, or many columns of a row, or any combination of that, you'd still have to copy and paste each single cell.

[CharlieRB]

Please elaborate how this answers the question "Is there a way to default to always match destination formatting when pasting into Excel".

[Jon]

If you are attempting to paste multiple rows, this will not work as it will put the text of all the rows into one cell.

[kojow7]

ALT+E+S+T+ENTER works for me. Your method didn't work, but I'm likely using a newer version of Excel.

[Ravindra Bawane]

In Excel 2013 you CAN set the default paste options, according to Microsoft. Options > Advanced > Cut, copy, paste. Adjust your defaults there.

[Aaron Hoffman]

I followed this method however I was receiving the error: pastespecial method of range class failed. I replaced the VAB line with ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False and that seemed to work. I got that line from recording a macro and performing a paste special text only.

[DimaSan]

Thanks, but still not perfect way since you want to use keyboard only, then you need to do another action - press F2 to enter the selected cell first.

[wotter]

This answer needs more votes. It's closer to the objectives of the OP (quickly paste while keeping formatting) than most answers above.

[wotter]

Yes microsoft loves to fuck up the shortcuts with each version they enforce on users. I wonder if Microsoft's devs use their own products.

[Cameron Tacklind]

This has nothing to do with hitting "CTRL+V"

[TheModularMind]

This seems to always keep the source formatting rather than match the target formatting, which is the opposite of what was asked.

[FelixJN]

@AdamV The "paste and match destination format" option can be found directly beneath.