Copying and Pasting are two of the most common tasks you’ll perform in Excel.
This is especially true for me and I can tell because the shortcut keys (CTRL+C for copy and CTRL+V for paste) on the new keyboard I got just last week are already showing signs of wear.
Most of the time I don’t just Paste, I Paste Special (right-click > Paste Special or ALT+E, S or CTRL+ALT+V):
Today I’m going to cover the ‘Operation’ commands in the Paste Special dialog box because in recent weeks I’ve had a few questions from members which were easily solved using these:
Enter your email address below to download the sample workbook.
1. Convert Values to Percentages
Convert Values to %: Copy cell containing 100 (A5) > select values in cells C5:C9 > right-click and select Paste Special > select 'Values' and 'Divide' (or ALT+E, S, V, I).
Then format as percentages: CTRL+1 > Format as percentages.
2. Add a Range of Values to Another Range of Values
Add range to another range: Copy first range of cells > select values in cells second range > right-click (or ALT+E, S, V) and select Paste Special > select 'Values' and 'Add' (or ALT+E, S, V, D).
3. Convert Text to Numbers using Multiply
Convert Text to Numbers: Copy cell containing 1 > select values in cells containing text > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).
4. Convert Text to Numbers using Add
Convert Text to Numbers: Copy any empty cell > select values in cells containing text > right-click and select Paste Special > select 'Values' and 'Add' (or ALT+E, S, V, D).
5. Add 10% to all Numbers
Add 10% to all Numbers: Copy cell containing 110% > select values you want to increase > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).
6. Reduce all Numbers by 20%
Reduce all all Numbers by 20%: Copy cell containing 0.80 > select values in cells that you want to reduce > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).
7. Convert Negative Values to Positive and Vice Versa
Convert Negative to Positive and vice versa: Copy cell containing -1 > select values in cells to be converted > right-click and select Paste Special > select 'Values' and 'Multiply' (or ALT+E, S, V, M).
Problem with Paste Special
For a long time I wished there was a way to set the default paste to ‘Values’ just like you can in the Word Advanced Options (File/Windows button > Options > Advanced > Cut, copy and paste section):
Or, at the very least a shorter shortcut key for Paste Special > Values, since having to press the unwieldy combination of ALT+E, S, V and then ENTER was just too prone to error for my fingers. Even the other option of CTRL+ALT+V, V then ENTER is only for double-jointed people!
Thankfully my friend, Jon Acampora, created an add-in for Excel called PasteBuddy. I’ve been using it for a few weeks now and I can’t imagine ever going back.
PasteBuddy allows you to create custom shortcut keys for any of the Paste Special commands. You can even choose your own key combinations so, if like me, you just can’t get your fingers (on one hand) to press CTRL+ALT+V, then you can choose a different letter or character on the keyboard.
Configure PasteBuddy
You can configure up to 12 different shortcut keys for any of the Paste Special commands in the Setup Window:
Once you’ve configured it as you want this window closes and you can merrily Paste Special to your heart’s content.
If you use the Paste Special dialog box often, or you’re left handed, then PasteBuddy is going to give you the shortcuts you’ve been yearning for.
You can find out more about PasteBuddy here.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Disclosure: If you purchase PasteBuddy I make a few dollars. I don't recommend any old product though. It has to be great quality and relevant to my readers. I love PasteBuddy and I hope you will too.
Jim says
I frequently want to replace a bank of formulae with their values immediately after calculation
Since these are usually all entered together (with ctrl-enter or double-clicking the fill handle – these are great tips too btw), the range is already selected:
Now, using the right mouse button grab and drag any border in any direction and then back again before releasing the button, then click paste values or go back to the keyboard and hit a “v”
Sounds long-winded but it’s really easy and well worth leaving the keyboard for (I usually avoid using the mouse too!)
Jim
Mynda Treacy says
Great tip, Jim! Thanks for sharing. I’ll try to remember that one.
Mynda
DETRA Putman says
Wow! I use “past special” all the time to paste the value of formulas. I had no idea there were so many other options.
Thank you so much for sharing your knowledge.
Mynda Treacy says
Thanks, Detra. Glad you found it useful 🙂
manou says
thank you so mush,your works It works great
Regards
manou
Philip Treacy says
You’re welcome.
Phil
Gary says
Fantastic Mynda. I didn’t know you could do such things with Paste Special.
Philip Treacy says
Thanks Gary, glad that you’ve learned something new.
Phil
Pablo says
Hello,
Not too long ago, Chandoo send a keyboard shortcut for the paste special.
I n his example he used values. Just copy as usual with Ctrl V and paste special with the key than duplicates de mouse right button and V.
This key is like the Windows key, but it’s on the right side of the keyboard.
Or just use the mouse, shortcut menu or right mouse key.
It works great
Thanks
Pablo
Mynda Treacy says
Cheers, Pablo. Stuart also mentioned the ‘Right-click’ key in his comment above.
Mynda
KV says
Is there any way to subscribe to the comments on a post, via email ?
It would be great to receive the comments via email if a particular topic is of interest, rather than remembering to check each topic every few days.
Philip Treacy says
Hi KV,
I’ve just added some code to allow you to do this.
Below the Submit button on the comment form you’ll see a link that allows you to sign up for notifications of new comments.
You can sign up for notifications on posts you’ve already commented on too, no need to leave a comment just to receive notifications.
Regards
Phil
KV says
That’s great Phil, thanks ! 🙂
Philip Treacy says
you’re welcome
Bryan Metz says
One of the first macros I ever wrote was for Paste Special (Values). Originally the idea was it would take the cells you had highlighted, copy them, and paste the values back into the same cells. I’ve since updated it to also act as a standard Paste Values if there’s already something in the clipboard. I have it set to a keyboard shortuct of Ctrl+S which is SO much easier than Ctrl+E,S,V!
Mynda Treacy says
Hi Bryan,
I gasped when I read that you repurposed CTRL+S for paste values. As much as I like Paste Values, I couldn’t give up the save shortcut for it 🙂
Mynda
Bryan Metz says
I know, it’s a controversial choice. But for some reason, I never got into the habit of using Ctrl+S, even though I DO compulsively save my files. For a while there I was using Alt+F,S (don’t know why), but now I put the save button in my QAT and any time I touch the mouse I head up there to click on it.
And if I’m honest… I probably use paste values more than save… 🙂
Mynda Treacy says
🙂 I have CTRL+S OCD!
Susan says
One of my favorite Paste Special commands is Ctrl+Alt+V and then w to paste column widths and then Ctrl+V to paste the rest. Whenever I need to copy some data to a new sheet, I do this so that the columns widths are correct 🙂
Mynda Treacy says
Cheers, Susan. I too like the Column Widths option.
You must be one of those double-jointed people I mentioned in my post if you can wrangle your fingers to press CTRL+ALT+V. Impressive 🙂
Mynda.
KV says
That’s a new keyboard shortcut I’ve learnt today…
THANKS Susan 🙂
stuart says
paste-values shortest keyboard-shortcut i’ve found is the right-click key followed by v (only excel2010 or later)
(right-click key is between space-bar & right ctrl-key – also called program-key or menu-key)
or if you’re a mouse-person, if not pasting far away, no need to copy, just right-drag the cell or range in any direction (even back to original location if just converting formula to value), on release select ‘copy here as values only’
Mynda Treacy says
Cheers, Stuart. Great tips, although that Right-click key requires me to either let go of my mouse or move my hand a long way 😉
I still prefer something with the speed and convenience of CTRL+C then CTRL+V, except for pasting values. Even the mouse action of right-click and drag is too many steps for me.
What can I say, I’m fussy 🙂
I’m sure there are others who will appreciate your tips, so thanks for sharing.
Mynda.
stuart says
ah! – well if your hand is on the mouse, instead of the right-click key, right-click the mouse!
Mynda Treacy says
🙂 he, he. Noooo. Using the mouse is too slow.
Tahir says
Excellent Demo.
Learned extra ordinary by this tutorial.
Jef says
This is great. Handy paste tricks. Thanks for sharing.
Mynda Treacy says
Cheers, Jef and Tahir 🙂
KV says
Hi Mynda, there is an option to make the PasteSpecial > Values or Formats commands into single click operations.
Just add them to the QAT 🙂
In fact, most of the PasteSpecial options can be added to the QAT depending on your preferences.
Mynda Treacy says
Indeed, KV. However I prefer a keyboard shortcut since I’ve just pressed CTRL+C and my fingers are ready to complete the pasting 🙂
KV says
I agree Mynda – even I hate moving my hands off the keyboard unless there’s no other option except to use the mouse 🙂
Almost all buttons on the QAT have keyboard shortcuts for them, depending on (a) the sequence in which they are added to it, and (b) the number of buttons added to the QAT.
Starting from the left, the first button on the QAT is assigned the keyboard shortcut Alt + 1, the second one is Alt + 2, and so on.
E.g., on my computer, PasteValues is fifth on the QAT, so I use Alt + 5 for that command.
Mynda Treacy says
Great tip, KV. Thanks.
Although I’m preferring my CTRL+SHIFT+A shortcut that I set up in PasteBuddy. The keys are nice and close togehter which means the chance of pressing the wrong key is reduced, and they don’t require double jointed fingers to press.
I think think there is something to suit everyone with all the tips shared here. Thank you all.
If we could just get that ‘Right-click’ key beside the CTRL key on the right hand side of the keyboard moved to where the Windows key is, then it would be even better. Not holding my breath though.
Mynda
Cathy says
Wow, I had no idea. I hope you work through the other Special Paste features as I’m seriously missing out.
Mynda Treacy says
Awesome! Glad I could teach you a new trick 🙂