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:
- Right click on any toolbar, and click ‘Customize’
- Click on the ‘Commands’ tab
- Select the ‘Edit’ category
- Scroll down in the commands list until you see the ‘Paste Values’ button
- Click and drag this button to the toolbar where you want it.
- 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.
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)
I discovered Juan’s drag method by accident awhile back.
I too, have the paste values button on one of my toolbars.
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”
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.
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
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
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)
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
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
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.)
“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
This fits nicely under the category of “Way too cool…”
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
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
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.
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
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
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
Bruce – have a look at the third comment down (by Fadi Chalouhi)…
My code to convert formulas to values is:
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…
Bruce –
Professional Excel Development (by Bullen, Bovey, and Green) has a nice example of a paste special toolbar.
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!
Robert Sterbal’s tip (3/17/6) was EXACTLY what I needed. Undying gratitude, Robert!
Troy
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….
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.