Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

How to Paste Numbers Only [quick tip]

Posted on September 8th, 2010 in Excel Howtos , Learn Excel - 26 comments

Sometimes when you are working with data, you may need to just copy and paste numbers from one range to another. Here is a handy little trick to achieve that.

Use Paste Special > Add operator to paste numbers only.

  1. Select the source range, press CTRL+C
  2. Now, go to target range, press ALT+E and then S to activate paste special dialog
  3. Select “Add” operator (you can press d)
  4. Click ok.

See this short screen-cast to understand how to do this:

Paste Numbers only using Excel

Paste Special - Tips & TricksBonus tips:

Convert numbers from positive to negative:

  1. Select and copy a bunch of numbers
  2. Go to an empty cell, press ALT+E and then S and then S (or choose subtract operator)
  3. That is all. Your numbers are negated now.

Convert from one currency to another instantly:

  1. Enter the exchange rate in a cell. Copy it.
  2. Now select all the cells which needs to be converted.
  3. Press ALT+E and then S and then select Multiply operator (press m)
  4. That is all. Your currencies are converted. That will be $2.00 plus 1$ per 100.

16 more tricks on how to use paste special

Do you use Paste Special? Share your tips:

I love paste special as much as my wife loves pastries. And that is a high praise. What about you? Do you use paste special? Tell me and our readers how you use it. We are all ears.

Here is a chance to be awesome:

Learn a quick tip today or share a link to win a gift card [expires on 12th Sept, 2010].

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 Responses to “How to Paste Numbers Only [quick tip]”

  1. MrCichy says:

    Maybe I’m stupid – but where on regular keybord is ES key?

  2. Mike says:

    Dear Chandoo,

    did you predefined ALT – ES for “Paste special”? This does not exist
    in a german version (03)

    Kind regards, Mike

  3. Hui... says:

    @ MrCichy
    Alt ES means Press the Alt and E together (release) and then press the S key

  4. Hui... says:

    @ Mike
    Have a read of the 2nd Comment on this page (about half way down the page)
    http://chandoo.org/wp/2010/02/22/complete-list-of-excel-shortcuts/
    There Should be a Paste Special option there

  5. Chandoo says:

    By ALT+ES I meant press ALT+E and then S.

  6. Chandoo says:

    And now I edited the post to make it clear. Sorry for the confusion.

  7. Mike says:

    @ Hui … yes, no its clearer with the space between E S

    @ Chandoo … thank you for solving the confusion and it is perfectly discribed in the way of “press ALT+E and then S to activate paste special dialog” .. well done!

  8. Tom says:

    Hi All,
    I personally don’t use the ‘keyboard combinations’ but rather stick to the buttons excel can add to the ‘ribbon’ > right click on the excel bar > Customize > Command > Edit and just drag the icons ‘paste special’, etc, to the ribbon.

  9. garyk says:

    i use pastespecial to convert numbers stored as text to numbers. enter a 1 in a blank cell. copy it. select the cells to be converted. do a pastespecial and choose multiply.

  10. David says:

    I must be missing something, but why not just copy the price column on the left side and then paste it on the right side? Why copy the product names also?

    And why would one want to paste prices for the 5 products on the left next to different items on the right side.

    Perhaps someone can explain what I am missing.

  11. Gregory says:

    Text stored as a number will be converter to a number using this shortcut.

  12. David says:

    I understand that text stored as a number will be converted to a number, but I did not understand why the product names were copied. It seems unnecessary to do so. Why not only copy the prices, and paste special them to convert them to a number?

  13. Tommy says:

    Is this the same in Excel 2007? A message “This is the shortcut for 2003″ comes up and does not work. What can I do to use this shortcut?

  14. Hui... says:

    @Tommy
    Copy a Range
    Goto where you want to paste it, which maybe the same location
    Press ALT+E together (release the keys) and then press S

    You may get a message poping up when you press Alt + E that says “Office 2003 Access Keys Alt E, continue typing the key sequence or ERsc to Exit.”

    If that pops up press the S key

  15. Tommy says:

    @Hui
    Thank you so much! I could use it.

  16. Anand Kumar says:

    I use shortcut Ctrl+Alt+V to bring up Paste Special dialog box. Just because it’s easier to remember that Ctrl+V is shortcut for paste and add Alt to it to paste special… works perfectly in Excel2007

  17. Chandoo says:

    @Anand… Thanks for that shortcut. I have been using ALT+ES for the last few years. But I like the “easy-to-remember” nature of CTRL+ALT combo.

    @David: I guess my demo data set is not accurately reflecting the power of this. Sometimes, your data may not be so well structured. Even then you can use the Add operator to copy just numbers and skip everything else.

    @Garyk:

    i use pastespecial to convert numbers stored as text to numbers. enter a 1 in a blank cell. copy it. select the cells to be converted. do a pastespecial and choose multiply.

    Very good tip. Donut to you :)

  18. scotty says:

    I’m surprised none of the previous comments have brought this up but it would be better to use the “V” accelerator key in the past special dialog to paste as values!

    Using the Add method if you already have numbers in the price column then they will get added to when really you would want them overwritten by the values just copied…

  19. Chandoo says:

    @Scotty…. The “v” key will not work as it will also replace the text. If you see above demo again, you will notice that the product names havent changed after pasting. Only the prices.

  20. scotty says:

    …. but if you are pasting values surely you would WANT to replace the text as well? It is pretty clear that rocket powered roller skates and an icicle maker are NOT the same thing. If you don’t replace the text then clearly the table is wrong.
    I accept that the paste special Add method is very useful when you only want to replace values over a mixed area of text and blank cells where you want values (but this is a pretty limited and unique case!). Normally to paste only values without formulas, formats, etc. you would use paste special values. Maybe the post should just be renamed slightly to satisfy sticklers like me and not confuse novices who might be surprised by the results if they try to use this method to paste over a range that already contains numbers??

  21. Hui... says:

    @Scotty
    I am sure Chandoo is showing us a technique for pasting tables of numbers where they are intermixed with text and I can think of several cases where this technique is applicable.
    The example presented may not have been the best as the different items has confused people, but the technique is still valid.

  22. Marko says:

    “Transpose” is one of mine frequently used paste special options, really handy in lot of situations :o)

  23. Anton says:

    To activate paste special dialog use CTRL + ALT + V

  24. TNL says:

    Thank you! This, in itself, is worth a million dollars to me! It will save me TONS of time! I loathe the drop down menus/ribbons. Key commands typically stick with programs from version to version. Whereas the appearance and locations of commands in drop down menus/ribbons change regularly.

  25. jeewan says:

    Past special : Try Ctr + Alt + V

Leave a Reply