55

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?

4
  • Which version of Excel are you using?
    – Firee
    Dec 6, 2013 at 12:29
  • @Firee: Excel 2013
    – Leftium
    Dec 6, 2013 at 12:42
  • I maintain a free and open-source tool to enable pasting plain, unformatted text by default in Windows, systemwide. With Ctrl+V or whatever is the app's default for pasting. This mode can be turned off, and Shift+Win+Ins can be used as an alternative.
    – noseratio
    Sep 29, 2020 at 1:43
  • Type control-U (Mac) or F2 (Windows) to enter editing mode, then use the normal paste keyboard shortcut.
    – JW.
    Nov 30, 2020 at 18:49

11 Answers 11

36

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?

4
  • 1
    This doesn’t work for me in Excel 2007. Dec 3, 2013 at 22:47
  • 10
    It works because it puts the cell into edit mode rather than text entry mode. It's equivalent to double–clicking in the cell.
    – RobG
    Aug 17, 2015 at 4:40
  • 7
    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.
    – Jon
    Aug 1, 2016 at 12:21
  • In Excel 2013 you CAN set the default paste options, according to Microsoft. Options > Advanced > Cut, copy, paste. Adjust your defaults there. Aug 6, 2018 at 22:12
17

Simply double click on the cell first before pasting.

3
  • 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.
    – redcalx
    Jul 27, 2015 at 14:15
  • 3
    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.
    – Nate
    Mar 2, 2016 at 22:03
  • 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.
    – DimaSan
    Feb 21, 2019 at 0:16
11

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.

1
  • 1
    ALT+E+S+T+ENTER works for me. Your method didn't work, but I'm likely using a newer version of Excel.
    – kojow7
    Jun 3, 2018 at 5:31
8

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.

1
  • This answer needs more votes. It's closer to the objectives of the OP (quickly paste while keeping formatting) than most answers above.
    – wotter
    May 5, 2020 at 12:49
6

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

1
  • 2
    Please elaborate how this answers the question "Is there a way to default to always match destination formatting when pasting into Excel".
    – CharlieRB
    Jun 1, 2016 at 17:20
5

I don't believe there is a way to change the default. However, what I've found very convenient is to add the "Paste Formulas" button (which will automatically keep the destination formatting) to the Quick Access Toolbar. To do this:

  • right click on the Quick Access Toolbar (the area in the top left with the Save, Undo and Redo buttons)
  • click "Customize Quick Access Toolbar..."
  • in the "Choose commands from:" dropdown menu, select "All Commands"
  • scroll down to "Paste Formulas" and double click on it
  • click OK

Now when you copy a cell or range, you can just use Alt+4 (assuming the new button is the fourth in the Quick Access Toolbar) to paste wherever you want and keep the destination formatting.

1
  • 2
    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
    Mar 2, 2016 at 21:52
3

In addition to my answer above, there is one more way to achieve this.

Go to File > Options > Quick Access Toolbar.

Select "All Commands": enter image description here

Select "Paste and keep source formatting", then "Add" the same. You can also use the positioning arrows to position this option to the top. Advantage of this is, to paste the item you have copied, you can use the shortcut ALT+1 shortcut: (You don't have to use CTRL+V)

enter image description here

3
  • This has nothing to do with hitting "CTRL+V" Apr 15, 2021 at 0:40
  • This seems to always keep the source formatting rather than match the target formatting, which is the opposite of what was asked.
    – AdamV
    Jun 23, 2021 at 10:57
  • @AdamV The "paste and match destination format" option can be found directly beneath.
    – FelixJN
    Jul 15, 2021 at 9:58
1

According to the MSDN Excel PasteType reference, there is no "paste-matching-destination-theme", although you could paste just the values or formulas.

Similar to the instructions found here, you could add a macro to override the CTRL+V behaviour.

The steps are as follows (embellished slightly from the source page):

  1. press ALT+F11 to open the Microsoft Visual Basic for Applications window
  2. select Insert > Module from the menu, and paste the following code in the Module Window:

    Sub PasteAsValue()
        Selection.PasteSpecial Paste:=xlPasteValues
    End Sub
    

    NOTE: replace xlPasteValues with xlPasteFormulas if required

  3. (optional) rename the module (in the lower left Properties pane)
  4. close the window (click the "X" button at top right)
  5. press ALT+F8 to open the Macro dialog box
  6. select the "PasteAsValue" macro (or whatever name the macro created in step #2 has) and click the "Options..." button
  7. in the Macro Options dialog box, type "v" in the box, and (optionally) any description
  8. click the "OK" button to commit the changes and exit the Macro Options dialog box
  9. click the "Cancel" button to exit the Macro dialog box (and avoid running and any macros)
  10. save the Excel spreadsheet (Excel will throw a warning if the spreadsheet is not already an .xlsm "Macro-enabled spreadsheet" - click "No" to have Excel prompt for a different file name and file type to save as)
1
  • 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. Oct 25, 2018 at 19:16
1

This is another work-around I have found, if you can switch from Excel to Google Sheets:

  • Ctrl-Shift-V: Paste values only

Source: support.google.com/docs/answer/181110

I just double-checked, and Excel 2016 doesn't have this shortcut yet (by default).

1
  • 3
    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.
    – wotter
    May 5, 2020 at 12:50
0

The paste forumula method works when one is copying and pasting into the same workbook. When copy to a different workbook, choose paste special and then choose CSV format.

1
  • Gone in Excel 2019.
    – EML
    Apr 29 at 9:39
0

Not so much an answer, as an extended comment.

First, Microsoft have gone to extraordinary lengths to remove any trace of 'Paste and match destination formatting' in 2019, for reasons best known to themselves. I have to go back to Excel 2010 to do this.

Second, it seems that this can't be done for some data. If you have a value which is meant to be an elapsed time ('27.16', for example, which you interpret as seconds), then you can't paste it into a cell which is custom-formatted as, say, [m]:ss.00 (ie. an elapsed time, with minutes, seconds, and 2 decimal places), because you haven't got a number of minutes and a colon in the source data. This also seems to be a general problem with the destination formatted as an elapsed time, whether or not you have a missing field in the source.

Not the answer you're looking for? Browse other questions tagged or ask your own question.