Excel 2010 Paste Special Commands

December 17th, 2009 by datapig Leave a reply »

 A lull in the action this week gives me time to talk about Excel 2010.

Every day this week, I'll take time to blog about some of the new features in Excel 2010 that strike me as real improvements.

Today, I'd like to talk about the improvements in the Paste Special functionality in Excel 2010.

 

Anyone who has used Excel for more than a week will know just how much the 'Paste Special' functionality is ingrained in our daily activities.  So when Microsoft makes improvements to this functionality, it's time for a love fest.

 

From my perspective, there are two major improvements that will come in handy: WYSIWYG Paste Special, and One Touch Paste Special.

I made these terms up because…frankly…I'm too lazy to look up Microsoft's official terminology for these two new features.

 

WYSIWYG Paste Special

In Excel 2010, when you copy a range and right click anywhere in the spreadsheet, you get a right-click menu that shows you the Paste Special icons.

Hovering over these, you can see a live preview of what the each Past Special command will do to your data. In this screenshot, I'm hovering over the Transpose icon. As you can see, Excel 2010 shows me what my dataset would look like if I executed this Paste Special command. Nice!

 

One Touch Paste Special

I found that it's sometimes easier to execute a normal paste then use the smart tag menu that activates in Excel 2010.

That is, copy the source data, then do a standard paste (Ctrl+V) where you want. At this point Excel 2010 will activate a smart tag that allows you to expand a small menu of Paste Special commands.

Simply click the command you want, and you're done. You can also use Hot Keys to execute these Paste Special commands.

 

 

There you have it! 

I'm sure many of you will sooner Barbecue your family pet than stop using your tried and true hot key combination for Paste Special Values.  But you have to admit that Paste Special sure has come a long way - from being practically hidden in Excel 2000 to in-your-face functionality in Excel 2010.

 

As a side note: Mrs. Pig told me that she wishes she can take Hugh Jackman's body and do a Paste Special Formats on top of mine.  Did she just call me fat?

Advertisement

14 comments

  1. Nice feature, but those icons are absurd. Every time I use it, I end up hovering my mouse over every icon so I can read the text on the Tooltip.

    I won’t have a chance to memorize those icons because I’ll be sticking with my tried and true hot key combinations. Those have served me well for the past 40 years, and I ain’t a gonna be learnin’ no new icons. No siree.

  2. datapig says:

    John: You’ve got a point about the icons. I do find myself waiting for the tool-tips to tell me what each icon means. It may have been a better idea to identify each paste command with some simple text.

  3. sam says:

    What they should have done is to redesign the paste special dialog…with Check boxes rather than radio buttons.
    If you want to paste values, format and column widths…its many many clicks away…

    Look at the paste special dialog that comes with ASAP utilities….
    http://tinypic.com/view.php?pic=eqs6l4&s=6

  4. Tony Rose says:

    I’m with John too. I look at them and have to figure out what each does. Maybe with time it will come easier. I would like to customize the options if possible. The paste image has been a nice feature to use.

  5. There are some interesting deadlines in this article but I don’t know if I see all of them middle to heart. There’s some validity however I will take maintain opinion till I look into it further. Good article , thanks and we wish extra! Added to FeedBurner as well

  6. Laura says:

    All I get are the icons–no text to tell me what they are for no matter how long I hover. Like so much new in 2010, these just put one more step between the user and the result.

  7. Jackie Benster says:

    In Excel 2010, I have a column with categories and a column with numeric data. I have sorted on the numeric data, high to low. Now I want to do a chart. Excel puts the data back to the original organzation by the category column. I want the data to display the way it shows after the sort. How can I do this? I have tried paste special keep values – did not work.

    Your supposed improvements have cost me HOURS of time. I loathe icons and wish you would go back to drop down menus – less space taken up; more logical; fewer keystrokes. As far as I’m concerned, you took a GIANT step backwards here.

  8. Norm says:

    I concur with previous comments, EXCEL 2010 toolbar wastes space, takes forever to find what use to be simple text pull downs. A nice single line custom tool bar on top, and on the BOTTOM a drawing tool bar with most used functions made life easy – I wish my 2003 EXCEL was back.

  9. Celeste says:

    It appears that Exel 2010 running in Windows 7 Enterprise, will not paste formulas or anything other than text when you use any form of copy and paste as long as Chrome, Internet Explorer, or Firefox are also open. As soon as you close the browsers, the additional paste options return. Anyone else having these problems? Any explanation or work-around other than closing all internet browsers before working in an Excel 2010 spreadsheet?

  10. Randy says:

    I am having a different problem. When I go to paste, I only get one option and it is to past text. I need to copy formulas and instead I copy and it inserts text. I have been to File, Options, Advanced and clicked the Cut and Paste boxes. All 3 boxes have checks in them.

    Any suggestions?

  11. Gordon says:

    I am having the same issue Randy, posted 3/26/12 – brand new laptop. Brand new excel 2010 Pro install … no Paste Special option. Paste Text only ….. Very frustrating, and no real solutions posted on the web. I have even tried a repair installation. Nothing

  12. yasir SUDAN says:

    same :/ .. paste test only .. nothing appears in gray, nothing more at all.

    i found this .. it may help
    http://www.excelbanter.com/showthread.php?t=445723

  13. Cliff says:

    What they should have done is allowed for the user to choose which special type of pasting they want as default.

    I do a lot of copying and pasting ranges of values from one workbook to another. Having to continually select the ‘paste values’ icon, or using the extended shortcut is ridiculuous. It’s my computer, I should be able to tell it what to do when I press ctrl+v. Excel is broken.

  14. Frank says:

    I HATE HATE HATE THIS NEW “FEATURE” I CAN’T DO ANYTHING WITH PASTE THESE ICONS ARE RIDICULOUS

Leave a Reply