Bottom line: Learn how to use a simple mouse shortcut to quickly copy and paste values in Excel.
Skill level: Beginner
An Unusual Way to Paste Values with the Mouse
In last week's post on 5 Keyboard Shortcuts to Paste Values there were a lot of comments on this method.
I call it the right-click & drag method for pasting values. When we use the right mouse button to move a range, a special menu appears that allows us to paste values.
Here is the shortcut in action.
Here are the steps for the right-click & drag method to paste values:
- Select the range you wan to copy.
- Move the mouse cursor to the border of the selection until the cursor turns to the cross hairs, like you are going to move the range.
- Right-click and hold.
- Move the range to a new location.
- Release the right mouse button.
- A new menu appears. Select the third option: Copy Here as Values Only.
- The range is copied and pasted as values only.
This can be slightly faster than right-click > Copy and right-click > Paste Values. There are some pros & cons that I'll discuss below.
Replace Formulas with Values in a Range
Probably the most common use for this shortcut is to replace a range of cells that contains formulas with values.
The process is the same as above, except that you will drag the range back onto itself.
- You first have to drag the range up/down/right/left by one cell so that the destination border moves.
- Then move the mouse back to it's original location so the original range you selected is highlighted.
- Release the right mouse button to choose Copy Here as Values Only from the menu.
I've always done a drag right then left for this technique, but down then up is probably faster. Since the mouse cursor is at the bottom of the range when you select it, you can just right-click and quickly move down then up. Here's a screen cast.
Things to know
There are a few important things to know when using this shortcut.
- This method can only be used when copying & pasting on the same sheet. You cannot right-click and drag to a different sheet.
- The range is NOT copied to the clipboard. This can be a pro or con, but I typically consider it to be a positive. You don't have to worry about hitting Escape to exit CutCopy Mode (stop the marching ants around the range) because the range is never copied to the clipboard. This can prevent accidental pastes with the Enter key.
- After releasing the right-mouse button, you can hit the Escape key if you want to cancel the operation.
- There are quite a few other handy commands on that right-click menu, and I encourage you to check those out too.
Share it with your co-workers
The quickest way to get your co-workers to buy you lunch is to show them this shortcut. Ok, I'm totally kidding!
However, it's one that not many people know, and it's always exciting to learn a new Excel tip. So feel free to teach your friends or share this post with them.
Please leave a comment below if you have any additional tips or suggestions for pasting values. And don't forget to checkout my last post on 5 Keyboard Shortcuts to Paste Values.