Mouse shortcuts

So Dick is the keyboard guru here, but there’s a lot of people that use the mouse a lot. I know I do. And I was reading Jensen Harris‘ blog today about how a Paste Special operation takes 6 clicks right now, but only 3 in Office 12.

However, there are a number of ways in which you can shorter this operation. One way, is to customize the toolbars, and display the ‘Paste Values’ button as shown in the screenshot.

The steps required to add this button are:

  1. Right click on any toolbar, and click ‘Customize’
  2. Click on the ‘Commands’ tab
  3. Select the ‘Edit’ category
  4. Scroll down in the commands list until you see the ‘Paste Values’ button
  5. Click and drag this button to the toolbar where you want it.
  6. Click on ‘Close’ and you’re done!

This reduces the number of clicks needed to 3 (Select Range, click on Copy, click on Paste Values).

However, there’s another method that blows people away each time I use it… it’s that cool factor I guess.

Take this data for example, I’m using =RAND(), just like Jensen is.

Now, select the range, right click and hold on one of the borders (*not* in the fill handle), drag the range as if you were going to move it to a different place, and then drag it back to its original position. Just offseting one row or column willl do the job.

When you release the mouse button, a popup menu will appear, giving you some pretty cool options, one of those being ‘Copy here as Values only’. Basically doing the Copy and Paste in one single operation.

I guess technically this is still a 3 click operation, but there’s a lot less mouse movement involved, and, you can use it to create links or hyperlinks for example.

Posted in Uncategorized

24 thoughts on “Mouse shortcuts

  1. I’m a bit in between really… I use the mouse a lot (I’ve played a lot of Quake in my time), but I use the keyboard when it’s quicker to do so.

    I have Paste Special Formats and Paste Special Values as toolbar buttons. That works great of me. I’m starting to need Paste Special Formula too, but I’m too lazy to configure the button just now.

    Some time ago (when I was participating in NG), I came across a keyboard shortcut for paste-special values.
    Alt e s v enter
    (I had to fire up Excel and validate the sequence, my fingers know it better than I do)

    It took a little while to get the hang of, since the keys are in awkward positions, but now I perform in a flash. I hardly ever use the toolbar buttons for Paste Special. Whats more, it’s available on any Excel I use (I work in an IT dept, so I remote control other ppl’s computer often)

  2. I discovered Juan’s drag method by accident awhile back.

    I too, have the paste values button on one of my toolbars.

  3. this right-button trick is great ! I’m interested to know how you discovered it !?

    I am a keyboard-addict myself. I used to use the “Alt E S V” (Values) “Alt E S T” (Formats) and “Alt E S F” (Formulas). but then I read a tip somewhere about the macros below that can reduce the number of keystrokes. In addition, once you assign a keyboard shortcut for each macro, the process becomes even simpler. On my keyboard it’s the Shift+Ctrl+V for Paste Values, Shift+Ctrl+T for Formats, Shift+Ctrl+F for Formulas, etc…

    here are the macros :
    Sub PasteFormulas()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlFormulas
    End Sub

    Sub PasteValues()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlValues
    End Sub

    Sub PasteComments()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlComments
    End Sub

    Sub PasteFormats()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlFormats
    End Sub

    and the Declaration in the Workbook_Open of the Add-in :
    Application.MacroOptions _
    Macro:=”PasteComments”, _
    Description:=”Paste Special –> Comments”, _
    Hasshortcutkey:=True, _
    ShortcutKey:=”M”

  4. I customized my menus when I got tired of Alt-e-s-v, so I have several paste-special buttons on the toolbar (including one that brings up the paste special dialog without having to visit the Edit menu). But this keyboard shortcut is good in Word and PowerPoint, at least the Alt-e-s, at which point I usually need to be reminded of the choices anyway.

    The nice thing about Juan Paulo’s technique (which I’ve used for a long long time) is that usually you’re usually already in the region with the mouse, perhaps having filled down a formula. You don’t have to put own the mouse to use the keyboard, or even travel a couple inches with the mouse.

  5. Thanks. I frequently use paste special to remove formulas. Based on your idea, I developed a quick macro and put a button on my menu bar. I can now remove my formulas with 2 keystrokes.

    Stroke 1 – select starting cell in target column
    Stroke 2 – press macro icon on menu bar

    Sub cpy_wo_frmla()
    Dim tmp_rng As Range
    cl = ActiveCell.Column
    rw = ActiveCell.Row
    lst_rw = Cells(Rows.Count, cl).End(xlUp).Row
    Set tmp_rng = Range(Cells(rw, cl), Cells(lst_rw, cl))
    tmp_rng.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells(rw, cl).Select
    End Sub

  6. I use paste special all the time to remove formulas. your post gave me an idea on how to develop a simple macro to get the job done in 2 keystrokes:

    1 – select starting cell in Column
    2 – press macro button on menu bar

    Here’s what I came up with.

    Sub cpy_wo_frmla()
    ‘ Macro copies and pastes special range wo formulas
    Dim tmp_rng As Range
    cl = ActiveCell.Column
    rw = ActiveCell.Row
    lst_rw = Cells(Rows.Count, cl).End(xlUp).Row
    Set tmp_rng = Range(Cells(rw, cl), Cells(lst_rw, cl))
    tmp_rng.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells(rw, cl).Select
    End Sub

  7. Thanks!

    This has to go in a “Things I used to know, but forgot” list.

    I find myself formatting names using =PROPER()in an empty column then Copy/Paste Value in the original location. The next step is to clear the temp column.

    It might be nice to have a Move and Paste Value.
    (or go ahead and write the macro since I know I’ll be doing this exercise a million times in the future)

  8. I like to take as many steps as possible to paste values.

    1. I start with a range of cells with formulas
    2. I save my file as a .CSV file
    3. I open Access
    4. I import the CSV file
    5. I rename some fields
    6. I export the table to Excel

    Tadaaa!!! No formulas!
    Feel free to share that cool trick with all your clients and friends

  9. Mike –

    Couldn’t you just do this by manipulating recordsets in SQL Server?

    Kelly –

    This might be easier:

    Sub cpy_wo_frmla()
    ‘ Macro copies and pastes special range wo formulas
    If Typename(Selection) = “Range” Then
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    End If
    End Sub

  10. At work I’m almost 100% a mouse user but at home I use a laptop. I’ve become an obsessive touchpad user – without using the right and left buttons, which are both quite sticky. I get the features of the mouse, much more conveniently located. Tricks requiring right-clicking while dragging – such as Juan Pablo’s here – are the most difficult, but they can be done. (One of my favorite touchpad features is that just brushing the narrow strip on the right of the touchpad sends you scrolling up or down web pages and documents, at variable speeds.)

  11. “a Paste Special operation takes 6 clicks right now, but only 3 in Office 12.”

    Interesting: I know this particular statement to be twaddle but went to read the blog anyway.

    Guess what, someone else already pointed out that with your toolbars set up it takes a lot less than 6.
    What’s not clear is how they count the clicks of course.

    My way:
    1 Click on Copy Icon
    2 Click on Paste Values icon

    that’s two clicks
    or 4 if you include selecting the cell to be copied then the target.

    I look forward to such flexibility in the new version.

    M

  12. Re: copy & paste values (in two steps: one click and one keyboard punch)
    I copy and paste values a lot and I wrote this little macro to speed it up. I use a keyboard shortcut to execute the macro: Ctrl/p (or whatever you choose). All you do is select the range to be copied and pasted (i.e. replaced) with values and then Ctrl/p.
    Store the macro in personal.xls and assign the keyboard shortcut to the macro.

    Sub CAPV()

    ‘ Paste Values Macro
    ‘ This macro copies a selection and pastes values in one step – use Ctrl/p

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

  13. You can also add the Paste Values entry to the right click menu using VBA. Can’t remember how/why I came up with this originally:-

    Sub AddPasteValues()
    Application.CommandBars(“Cell”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars(“Row”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    Application.CommandBars(“Column”).Controls.Add Type:=msoControlButton, ID _
    :=370, Before:=5
    End Sub

  14. I have a worksheet which runs microsoft queries when it opens. I want to write a macro, which will copy only the results of the query and paste-special in a new sheet and the queries should not get transported.

  15. Hi Dick,

    I work in a AS-400 Mainframe where I have to selected a particular range every Minute. Iam unable to define the same. Pls help.

    Regards
    Prashanthy

  16. This button is absolute first thing I add when I install excel…

    I also always customize the button to image and text. I rename the button &1

    Now I have 1 click and hot key (Alt-1) access to the most frequently used function for me in Excel.

    -Robert Sterbal

  17. To All,

    I’m trying to create a toolbar for Paste-Special-Formulas, much like the Paste-Special-Values, and Paste-Special-Formats, which come with Excel. I’m using Excel 2003. Can anyone provide me with the VBA code to put into the paste special formulas toolbar?

    Thank you!!
    Bruce

  18. Bruce – have a look at the third comment down (by Fadi Chalouhi)…

    My code to convert formulas to values is:

    Sub Value_Selection()
        Selection.Value = Selection.Value
    End Sub

    This has been discussed here and on other Excel sites before – seems to work a lot faster than PasteSpecialValues…

  19. This is exactly what I needed! I didn’t know that button for paste as value, neither this contextual right-click drag menu! THanks a lot!

  20. To think of all of the calories I’ve wasted over the last 5 and half years with all of the Right Arrow and Down Arrow, C, arrow, arrow, arrow and ESV ‘s I’ve been doing….

  21. All good ideas, but all the keystroke accelerators attached to macros that I’ve tried cannot be undone! Alas, a good percentage of the time that I use Paste Values, I undo it after I have checked out the impact elsewhere on the spreadsheet.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *