The Robservatory

Robservations on everything…

 

Use the keyboard in Excel’s Paste Special dialog box

When I’m working in Excel, I spend a lot of time in the Paste Special dialog box—pasting formulas, pasting all but formats, pasting only formats, etc. You can call up the dialog with a keyboard shortcut (⌃⌘V), but it then looks like you’re stuck using the mouse, because there aren’t any keyboard shortcuts for the various actions. But really, there are…

(Note: This applies to the current version of Excel, i.e. the one in Office 365. Based on the comments, it apparently also works in Excel 2011 if you add the Command key.)

On Excel for Windows, one character in each option has an underline, indicating that option’s keyboard shortcut. The good news is that these same shortcuts work on the Mac, even though they’re not shown. (There is one apparent oversight: The O key should select Operation: None, but it doesn’t seem to work on the Mac.)

Here are all the shortcuts, graphically:

Press the highlighted key, and that action will be selected; press Return to execute the chosen command, and you can use the Paste Special dialog without ever touching the mouse. (Note that the Paste Link action executes immediately when chosen, so it’s a one-key operation.)

Because graphics are horrid for web searching, the text version of each shortcut, in alphabetical order, is shown below.

  • A = Paste: All
  • B = Skip Blanks
  • C = Paste: Comments
  • D = Operation: Add
  • E = Transpose
  • F = Paste: Formulas
  • G = Paste: All, merge conditional formats
  • H = Paste: All using Source theme
  • I = Operation: Divide
  • L = Paste Link [this operates immediately]
  • M = Operation: Multiply
  • N = Paste: Validation
  • R = Paste: Formula and number formats
  • S = Operation: Subtract
  • T = Paste: Formats
  • U = Paste: Values and number formats
  • V = Paste: Values
  • W = Paste: Column widths
  • X = Paste: All except borders

For me, F, T, and V get the most use, so I’ve made those few even easier to use…but more on that in a future tip.

3 Comments

Add a Comment
  1. Just tested in Excel for Mac 2011 and it only works if I combine the Command key with the shortcut. Which is still a big win since I assumed that it was impossible all these years. Thanks!

    1. Thanks for the update on an older Excel version—I added a clarifying blurb to the write-up.

      -rob.

Leave a Reply

The Robservatory © 2017 Built from the Frontier theme