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 CTRL+SHIFT+V (Microsoft 365 only) or CTRL+ALT+V, 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):
But at least now we Microsoft 365 users can use the new keyboard shortcut CTRL+SHIFT+V to paste values.
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
Great tip, Jim! Thanks for sharing. I’ll try to remember that one.
Mynda
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.
Thanks, Detra. Glad you found it useful 🙂
thank you so mush,your works It works great
Regards
manou
You’re welcome.
Phil
Fantastic Mynda. I didn’t know you could do such things with Paste Special.
Thanks Gary, glad that you’ve learned something new.
Phil
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
Cheers, Pablo. Stuart also mentioned the ‘Right-click’ key in his comment above.
Mynda
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.
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
That’s great Phil, thanks ! 🙂
you’re welcome
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!
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
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… 🙂
🙂 I have CTRL+S OCD!
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 🙂
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.
That’s a new keyboard shortcut I’ve learnt today…
THANKS Susan 🙂
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’
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.
ah! – well if your hand is on the mouse, instead of the right-click key, right-click the mouse!
🙂 he, he. Noooo. Using the mouse is too slow.
Excellent Demo.
Learned extra ordinary by this tutorial.
This is great. Handy paste tricks. Thanks for sharing.
Cheers, Jef and Tahir 🙂
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.
Indeed, KV. However I prefer a keyboard shortcut since I’ve just pressed CTRL+C and my fingers are ready to complete the pasting 🙂
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.
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
Wow, I had no idea. I hope you work through the other Special Paste features as I’m seriously missing out.
Awesome! Glad I could teach you a new trick 🙂