Basic Paste
The fastest way to cut, copy and paste is by using the keyboard shortcuts Ctr+X, Ctr+C and Ctr+V. Ctrl+V performs the basic paste operation — and includes the formulas and formatting, if any. But sometimes you want to do more — or less — when pasting. And pasting can be used in ways you’ve never imagined. Enter Paste Special.
Paste Special
The animation below shows some of the neat things Paste Special can do in Excel: pasting just values (no formatting or formulas), pasting a picture (that you can move around) and pasting formatting onto other numbers.
Below are what I’ve found to be the fastest Paste Special methods for various tasks. As for nomenclature, RC stands for Right Click and MK stands for Menu Key. Below is a picture of the Menu Key in case there’s confusion (it’s at the bottom right of the keyboard). The Menu Key basically gives you the same menu as Right Clicking does. So, in effect, RC=MK, except one uses the mouse and the other uses the keyboard (note: some keyboards surprisingly don’t have a menu key.
Paste Options Menu
To start at the beginning, what’s the fastest way to bring up the basic Paste Options menu that you see below? If your right hand is on the mouse, simply right click (RC). If your right hand is on the keyboard, simply hit the Menu Key (MK).
Paste Options Menu: RC or MK — use RC if your right hand is on the mouse and MK if it’s on the keyboard
Each method will bring up the same basic Paste Options menu below, which includes the following six options from left to right (I’m also giving their shortcut key): “normal” Paste (P), Values (V), Formula (F), Transpose (T), Formatting (R) and Paste Link (N):
If you want to paste Values, then either click on the “123” icon (slower) or simply type the shortcut for it, which in this case is “V” (faster). Typing “V” is faster than maneuvering and clicking the mouse since it’s an easy target for the left index finger (no need to move the hand across the keyboard). So the fastest way to paste values is RC-V (or MK-V if your right hand is on the keyboard already).
Paste Values: RC/MK-V
Another good one is RC/MK-R for Format:
Paste Formatting: RC/MK-R
Hover over each icon to (a) get its shortcut key and (b) get a preview of what the paste option will look like. Below you’ll see the “Values (V)” telling you that “V” is the shortcut key for pasting only Values (“T” is for Transpose, “N” is for link, etc.). You’ll also see the preview for Values in the second column reflecting what the job will look like if I choose this option.
Note: typing just “V” for Values works because nothing else in the Context Menu uses that key. (You can tell if something else in the menu uses the key if the letter is underlined). The same for “P” for Paste and “R” for Formats. But you unfortunately can’t do the same with “F” (Formula), “N” (Link) and “T”( Transpose), since they all share their keyboard shortcuts with other items in the Context Menu (Format Cells, Clear Contents, Cut). For these, you need to hit the spacebar after the letter (which you can do with your left hand) – which is a small sacrifice.
Paste Formula (hybrid #1): RC-F-spacebar
Since T is shared Transpose is the second T on the list, you need to type it twice:
Transpose (hybrid #1): RC-T-T-Spacebar
“N” (Link) requires the right hand on the keyboard, so I recommend just clicking instead of hitting the keyboard.
Read more about RC shortcuts in my Excel Shortcuts: Mouse/Keyboard Hybrids post.
Learn more about formatting in my Excel Formatting Shortcuts post.
Paste Special Sub-Menu
How about the “Paste Special…” line just below the icons? Clicking on that line gives you a sub-menu with even more paste options. But clicking on it isn’t the fastest method. You can activate that menu faster by simply typing “S” (since “S” is underlined, meaning it’s the shortcut key for that option). Whenever you see an underlined letter, that is the shortcut key for that menu option (though if that shortcut letter is shared, then you have to type “Enter” after the letter). Thus, the fastest sequence to pull up this sub-menu is RC-S (or MK-S if your right hand is already on the keyboard).
Paste Special Sub-Menu: RC-S or MK-S — use RC-S if your right hand is on the mouse and MK-S if it’s on the keyboard
Now how about the “Paste Special…” line just below the icons? Hovering over that line gives you a sub-menu with even more paste options that you can choose by clicking. But hovering and clicking isn’t the fastest method to select your option. You can activate that menu faster by simply typing “S” (since “S” is underlined, meaning it’s the shortcut key for that option). Thus, the fastest sequence to pull up this sub-menu is RC-S.
Paste Special Sub-Menu: RC-S
From there you can type keyboard shortcuts or click.
Pulling up that sub-menu gives you 14 options (including some overlap with the Paste Options menu), plus yet another Paste Special option at the bottom. As with the other icons, hovering over them shows you the keyboard shortcut and a preview. To paste values and number formatting, the fastest way is RC-S-A (“A” is the shortcut for Values and Number Formatting — the icon with 123%). If you hover on the sub-menu, the main menu is temporarily greyed out so you can see the preview beneath it.
Paste Special — Values & Number Formats: RC-S-A
If you’re not going to use the values and number formatting very often, it probably doesn’t make sense to memorize it. I suggest only memorizing your high frequency actions, adding one or two to your memory each week, using the hover technique to remind you of the shortcut key.
To Paste Picture (something I use a lot, especially to email screenshots), you’ll end up either needing your right hand on the keyboard or stretching your left hand a little to get to the U key (you have to decide whether it’s faster to do that or to simply use the mouse to click on the Picture icon, which I do, since finding the U on the keyboard takes my eyes off the screen). So here’s the shortcut in case you like it:
Paste Special — Picture: RC-S-U
Now recall that our first shortcuts for Transpose (RC-T-T-spacebar) and Format (RC-F-spacebar) both required the spacebar because their shortcut letters (T and F) were shared with other shortcuts. Well, these options are also available on the Paste Special Sub-Menu – where they do not share their letters with other shortcuts. Therefore, it’s possible there’s a faster way to select them using the Sub-Menu than there is using the regular menu. Let’s take a look.
In the Sub-Menu, F is for Formula and T is for Transpose. Thus for Transpose, you can save a keystroke by doing RC-S-T instead of RC-T-T-spacebar.
Transpose (hybrid #2): RC-S-T
For Formula, you can do RC-S-F instead of RC-F-spacebar – technically a wash, but you may prefer it to RC-F-spacebar:
Paste Formula (hybrid #2): RC-S-F
Pasting Link still requires the right hand if you’re doing RC-S-N, so I don’t recommend it – unless your right hand is already on the keyboard (in which case you’re better off doing MK-S-N instead of RC-S-SN).
I don’t use any of the remaining menu options that often, but you can decide for yourself whether you want to use them by determining if (a) you use them frequently and either (b) they can be selected with the left hand, or (c) you would tend to have your right hand on the keyboard already when needing the menu option.
Now back to that other “Paste Special…” line — at the bottom of the sub-menu we just covered – see below:
Paste Special Pop-Up Menu
Selecting the “Paste Special…” line gives you the pop-up menu below, with even more options — most importantly, the “Operation” choices (more on them in a moment).
The fastest method of getting to this menu is RC-S-S (because “S” is the shortcut in the RC menu AND the sub-menu (“Paste Special…”) for this pop-up menu). This should be an easy one to remember, and extremely easy to perform since “S” is so easy for the left hand to type.
Paste Special Pop-Up Menu: RC-S-S
You can bring it up using Ctrl-Alt-V (but I find it slower than RC-S-S, since it’s like doing Twister with your left hand):
Paste Special Pop-Up Menu (keyboard #1): Ctrl+Alt+V (pressed simultaneously)
Yet another option is to use a legacy Alt sequence:
Paste Special Pop-Up Menu (keyboard #2): Alt-E-S
Alt-E-S is actually pretty quick, and is maybe a tie with (or slightly slower than) RC-S-S, so maybe you’ll like it.
Finally, you can simply click on the “Paste Special…” line (either the upper or lower one). I still find RC-S-S fastest.
Within this menu you see additional underlined letters. After typing the letter, using the arrow and Tab keys or clicking on the radio button, you have to either hit Enter or click OK – or hit Tab with your left hand until the OK button is selected, then hit the spacebar.
My favorite option in this menu is to add 0 (zero) to a selection to convert it from text-formatted numbers to actual numbers. To perform this neat time-saving trick, simply copy a blank cell (Excel interprets it as zero) and add it to your selected cell/range by selecting Values as your Paste option and Add as your Operator — or RC/MK-S-S-V-D-<OK> (I think clicking on OK is faster than tabbing to the OK button and then hitting spacebar).
Convert text-formatted numbers to actual numbers by adding zero (0): RC/MK-S-S-V-D-<OK> (after copying a blank cell)
While this is a long sequence to memorize, remember that after you do RC/MK-S-S, the rest of the letters — V and D — are visible on the pop-up menu, so you don’t really have to memorize them (similar to Alt sequences). It’s worth trying this keyboard approach even if you go back to using your mouse for the V and D.
Another “weird trick” using this pop-up menu is changing the sign of an entire range (e.g., from negative to positive) by multiplying by -1 (negative one). Do this by pasting a value of -1 and multiplying it as your operation. Yet another time-saver is to convert from 1’s to 1,000’s (or 1000’s to 1,000,000’s) by multiplying by 1,000 — or go the other way and divide by 1000. Just remember to exclude your totals formulas or they’ll effectively be “operated” on twice. I recommend clicking M instead of typing it:
Multiply Every Cell In A Range By 1000: RC/MK-S-S-V-<M>-<OK> (after copying a cell with 1000 in it)
(Note: you can also access the pop-up by simply clicking on the FIRST “Paste Special…” line (in the sub-menu). But this is still slower than RC/MK-S-S).
Here’s a graphic to help sum things up:
Slower Paste Special Methods
Finally, here are some slower methods for various Paste Special tasks in case you’re interested (if you think I’m underselling them, let me know). If you keep your right hand on the keyboard (which I don’t), they’ll be faster.
Paste Options Menu (keyboard): Alt-H-V
This method shows the letters without hovering — see below (we covered this menu earlier) – but is slow because it requires the right hand (and thus I neither use nor recommend it).
Another way to access this menu (but without the letters showing or the Paste Special line at the bottom) is to activate the little “tag” or Paste Options menu you get right after you paste – see below (you’ve probably ignored it to the point that you’ve forgotten it). You activate it by hitting Ctrl:
So the full sequence is as follows:
Paste Options Menu (keyboard): Ctrl+V-Ctrl
It’s faster than Alt-H-V and uses only the left hand. But it’s slower than RC-S, so I wouldn’t use it.
Interestingly (or ironically) to Paste Values you just type “V” next, so the funny little sequence here (that probably none of your friends has ever heard of) is:
Paste Values (keyboard): Ctrl+V-Ctrl-V
You can always perform other sequences using the letters (e.g., Ctrl+V-Ctr-T for Transpose).
If you want to hide this little menu (because sometimes it just won’t go away), go to the File > Options > Advanced menu (or Alt-F-T-A) and de-select “Show Paste Options button when content is pasted” (see below):
Conclusion
I hope this overview conveys to you the power of Paste Special. It’s a great tool that can save you lots of time, converting long and tedious tasks into short and simple ones. While you may only use a few of the features on a regular basis, it helps to be aware of all them. Luckily you don’t have to memorize everything since you can just look at the menu and, in many cases, get a preview of what the option will do. For those options you employ often (e.g., Paste Values) remember the fastest way (e.g., RC-V) to save you even more time.
Do you have anything faster, or have a different opinion on Paste Special? Please share.
Leave a Reply