Excel - How to Match Destination Format always

[Socalgal]

Excel Gurus - I need you.

Pasting new data into a spreadsheet brings up the Paste Options icon to either Keep Source Formatting, or Match Destination Formatting.

How do I universally apply to the spreadsheet so that pasting new data automatically or always matches destination formatting?

Only need on a per worksheet basis (not for a default template).

Thanks!

[Socalgal]

No way to accomplish this? :/

[vass0922]

Hmm is this Excel 2003, or 2007?

I've seen that option, and found it quite annoying
give me a bit (possibly Monday) and I'll see what I can figure out

If anybody else has ideas, send 'em

[Socalgal]

vass thanks - I appreciate your effort.

Excel 2003

I did find a "workaround" that involved creating a macro, but that's still a PITA to hit a button every time.. not much of an improvement.

[vass0922]

does this do it?

Topics: Turning Off Paste Options

Choose Options from the Tools menu. Excel displays the Options dialog box.

Make sure the Edit tab is displayed. (Click here to see a related figure.)

Clear the Show Paste Options Buttons check box.

Click on OK.

[Socalgal]

Nope :/

That only disables the Paste Options button from appearing while keeping the source formatting intact; it does not cause what I want to happen, which is to automatically *always match destination format* (i.e., without the paste option button).

Thanks, though

[vass0922]

doesn't seem to be possible

here is one option
OpenOffice.org Forum :: Paste as unformatted text by default?

Not sure if its what you're looking for though

seems to be a new feature in Excel 2007
Set your own default Paste options with Word 2007 | Microsoft Office | TechRepublic.com

[vass0922]

does Paste Special --> Values do what you want?

Thats not exactly removing a step though :-/

[Socalgal]

thanks for the help, vass

I'll try recording a macro using Ctrl + V as the shortcut.

Yeah, how great that they have that option in 2007 Maybe I can find a TS seminar to get it gratis

[spelunkr1971]

Oh this is SOOOOOOOO frustrating....
What happened? What friggin genius (ha) thought this would be a good idea?

[Toadman]

It's called planned obsolescence and it bites big time. The next iteration of Word will probably only open in WordPad for pre-XP OS machines.

[CapK]

doesn't seem to be possible
seems to be a new feature in Excel 2007
Set your own default Paste options with Word 2007 | Microsoft Office | TechRepublic.com

Infuriatingly, that only works in Word. In Excel the drop-down list isn't present.

[guesttttttbaby]

If you double click the cell you want to paste into then the destination formatting will be applied when you paste. Tested and verified.

[Jackulas]

The double-click option doesn't do exactly what the OP (or I) was hoping, but it does cut down on the tedium. Thanks so much.

[hutchinsfairy]

Not a complete solution but I recently used conditional formatting to force some format options to remain even when pasting.

Format --> Conditional Formatting...

Then Formula is: =$ZZ$10000=""

Set all the options you can (there aren't many) to what you want.

This will override any local formatting that you subsequently try to apply though...

[balfor]

Copy the data to the clipboard (CTRL-C) then, in Excel, double-click the cell (make sure you select all of the data if you want to replace it, then paste (CTRL-V). That worked for me.

[BluArtistEyes]

Excel Gurus - I need you.

Pasting new data into a spreadsheet brings up the Paste Options icon to either Keep Source Formatting, or Match Destination Formatting.

How do I universally apply to the spreadsheet so that pasting new data automatically or always matches destination formatting?

Only need on a per worksheet basis (not for a default template).

Thanks!

This may help, if you are pasting multiple single lines into excel use the bar at top and it will only paste text adjusting it to your excel formatting. it makes it somewhat faster for single line pastes.

oh nm, double clicking cells works better and faster for this effect.

----SeeJ
As far as I know there's no options for changing default yet in any newer version of excel sad.

[Fluxrider]

Old thread, but the problem still exists. Here is what I've found:

1) You can set the default paste options in Word 2007 by

1. Click the Office button.
2. Click the Word Options button.
3. Click Advanced on the left side of the window.
4. Scroll down to the Cut, Copy, And Paste section, click the drop-down arrow of the Pasting From Other Programs box and select Match Destination Formatting.
5. Click OK.

Bizarrely however, this capability only exists in Word2007, not Excel2007.

2) An available method is the one mentioned above:

1. With data copied to the clipboard, double-click the cell to paste into.
2. Paste (Ctrl+v)

3) The method I like the best though, and use regularly, is to create a Macro, and set it to a convenient keystroke.

Do this once, and then you will be able to paste with destination formatting just as quickly as normal pasting.

1. First, the preparation: copy data onto your clipboard from another program, and select a cell on the worksheet.
2. To create the macro, on the menu bar select View > Macros > Record Macro
3. In the dialog box, there are three important tasks:
   1. Name your new macro. (I usually name it pasteWithDestFormatting)
   2. Select the keystroke (I almost always put a capital V in the box for the shortcut key, and the resulting keyboard shortcut is Ctrl+Shift+v)
   3. Choose where you want to save this macro:
      1. If you only want it available in this workbook, choose this workbook.
      2. If you want the macro available whenever you use Excel on this computer, choose Personal Macro Workbook.
4. Click OK to start recording the macro.
   1. Enter Ctrl+v (lower case, normal paste method) to paste your data into the selected cell.
   2. Click on the little options menu box next to the cell and choose Match Destination Formatting.
   3. Stop recording the macro (View > Macros > Stop Recording.)

You're done! Anytime you want to paste with destination formatting, just enter Ctrl+Shift+v (or whatever keystroke option you picked.)

[alexcarlson]

Hi Vass thanks - I welcome your effort.

[CP-PDX]

I found this on another board: Before paste, press "backspace" or "F2" then paste.

Always Match Destination Formatting - AppScout