+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 20 of 35
  1. #1
    Per aspera ad astra Socalgal's Avatar
    Join Date
    May 1999
    Location
    USA
    Posts
    11,333

    Excel - How to Match Destination Format always

     
    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!

  2. #2
    Per aspera ad astra Socalgal's Avatar
    Join Date
    May 1999
    Location
    USA
    Posts
    11,333
    No way to accomplish this? :/

  3. #3
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879
    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

  4. #4
    Per aspera ad astra Socalgal's Avatar
    Join Date
    May 1999
    Location
    USA
    Posts
    11,333
    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.

  5. #5
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879
    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.

  6. #6
    Per aspera ad astra Socalgal's Avatar
    Join Date
    May 1999
    Location
    USA
    Posts
    11,333
    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

  7. #7
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879
    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

  8. #8
    Not Really a Member
    Join Date
    Oct 2001
    Posts
    27,879
    does Paste Special --> Values do what you want?

    Thats not exactly removing a step though :-/
    Last edited by vass0922; July 5th, 2008 at 09:49 PM.

  9. #9
    Per aspera ad astra Socalgal's Avatar
    Join Date
    May 1999
    Location
    USA
    Posts
    11,333
    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

  10. #10
    Junior Member
    Join Date
    Sep 2008
    Posts
    1

    Angry Cut/n/Paste nightmare

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

  11. #11
    Ultimate Member Toadman's Avatar
    Join Date
    Oct 2001
    Location
    30-41,000ft
    Posts
    5,879
    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.
    "The inherent vice of Capitalism is the unequal unfair sharing of blessings. The inherent virtue of Socialism is the equal sharing of mediocrity and motivation. Socialism is a philosophy of failure, the creed of ignorance, gospel of envy, and its only inherent virtue is in the equal sharing of misery.

  12. #12
    Junior Member
    Join Date
    Feb 2009
    Posts
    1
    Quote Originally Posted by vass0922 View Post
    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.

  13. #13
    Junior Member
    Join Date
    Feb 2009
    Posts
    1

    Thumbs up I found a way that works for me.

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

  14. #14
    Junior Member
    Join Date
    Mar 2009
    Posts
    1

    THANK YOU!

    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.

  15. #15
    Junior Member
    Join Date
    Jun 2009
    Posts
    1

    Conditional Formatting?

    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...

  16. #16
    Junior Member
    Join Date
    Nov 2009
    Posts
    1

    keeping destination formatting

    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.

  17. #17
    Junior Member
    Join Date
    Mar 2010
    Posts
    1

    This may help depending on what your trying to do.

    Quote Originally Posted by Socalgal View Post
    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.
    Last edited by BluArtistEyes; March 18th, 2010 at 04:59 AM. Reason: already a better solution on page sorry

  18. #18
    Junior Member
    Join Date
    Apr 2010
    Posts
    1
    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.)

  19. #19
    Junior Member
    Join Date
    Apr 2010
    Posts
    29
    Hi Vass thanks - I welcome your effort.

  20. #20
    Junior Member
    Join Date
    Jan 2011
    Posts
    1

    Found this "solution"

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

    Always Match Destination Formatting - AppScout

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Able to Export the Access Report to Excel, but not in the desired format.
    By ShameerG in forum Applications and Operating Systems
    Replies: 1
    Last Post: January 9th, 2008, 11:34 AM
  2. Excel Format Question
    By ZeR0 in forum Applications and Operating Systems
    Replies: 1
    Last Post: December 18th, 2006, 09:08 AM
  3. Excel help!! I fight Excel & Excel wins!!!!!!!!!
    By Dooin' it in forum Applications and Operating Systems
    Replies: 4
    Last Post: March 23rd, 2005, 09:35 PM
  4. Honeymoon destination help
    By ILC in forum IMO Community
    Replies: 24
    Last Post: June 15th, 2004, 05:10 PM
  5. dsl destination not reachable
    By jdbic in forum Networking and Internet
    Replies: 3
    Last Post: July 29th, 2003, 11:37 PM

Tags for this Thread

Posting Permissions

  • You may post new threads
  • You may post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Recommended Sites: ResellerRatings Store Reviews