Techniques to Paste Values in Excel

Lose the formulas and keep the values

Sometimes in Excel you need to paste just the values from copied cells. You don’t want the formulas. You may have created temporary formulas that you need to replace with their values. You may need to capture the current values, make some changes and then compare the new values with their old values to see the difference.

The Paste Special dialog offers a number of pasting options and one of them is Paste Values.

There are a few ways to Paste Values. Try the first three techniques after you have copied the cell or range and navigated to the destination cell.

1. Right click option (Excel 2010 onwards)

Right click and click the Paste Values icon, it has 123 on it. This works in Excel 2010 onwards.

Right Click Paste Values

2. Paste icon drop (all versions)

To Paste Values in all versions you can click the Paste toolbar icon drop down and select Values (2003) or Paste Values (2007 onwards).

Excel 2003

 

Excel 2007

Paste Values xl2010

The last image shown above is from Excel 2010 and has a few extra Paste Values options, where you can keep the number format or all formats.

3. Alt key shortcut (Excel 2007 onwards)

To Paste Values press the keys in sequence Alt h v v (do not hold the keys down).

4. Right dragging (all versions)

This technique works best when you are pasting the values close to the source. A right drag involves selecting the range and then using the right mouse button to click, hold and drag the selected range. This can take some practice, but it is worth the effort. You need for use the arrow shaped icon to drag, which appears on the border of a selected range or cell.

arrow shaped icon

When you release the mouse one of the options is Copy Here as Values Only.

Right Dragging

I use this technique to quickly paste values on top of existing formulas. Select the range and then click, hold and drag with the right mouse button to the right and then immediately drag back to the left on top of the range and release the mouse and select Values Only option. This technique works to remove temporary formulas that you may have created to fix a data issue.

Please note: I reserve the right to delete comments that are offensive or off-topic.