Bottom line: Learn 5 different ways to copy and paste values to remove all formulas and formatting.
Skill level: Beginner
Video Tutorial
What is Paste Values?
Copying and pasting values is probably one of the most common tasks we do in Excel.
Paste Values will paste the values ONLY of the copied range WITHOUT formulas and formatting. This allows us to extract the numbers or text from cells.
There are a TON of reasons to paste values.
One common use is for scenario analysis where we want to “freeze” numbers that are results of formulas and place them in some blank cells.
Another common use is when we want to paste numbers or text into a range that already contains formatting. Pasting values will not change any existing formatting that is applied to the cell/range.
In the image above, the Scenario 1 column already contained both cell formatting (colors) and number formatting. When we paste values, any existing formatting in the paste range will NOT change.
In the first example above, the blank cells have the default General format, and that is why there is no number formatting applied when we paste values.
The Paste Special Menu
Paste Values is one of the many pasting options on the Paste Special menu.
We can also access some of the Paste Special commands from the Home tab and right-click menu in Excel.
The Paste Special… button on those menus opens the full Paste Special Menu.
Keyboard Shortcuts for Paste Values
There are keyboard shortcuts for all of the Paste Special commands. As I mentioned before, the most common we use is Paste Values.
In the video above I share 5 keyboard shortcuts (plus a bonus) to paste values. Here is a list of the shortcuts.
- Alt, E, S, V, Enter (Mac: Ctrl+Cmd+V)
- Alt, H, V, V
- Menu Key + V
- Custom Quick Access Toolbar (QAT) Button: Alt+1
- Ctrl+V, Ctrl, V
- Custom shortcut with The Paste Buddy Add-in.
In the video I also mention my article on the best keyboards for Excel keyboard shortcuts.
If you're more of a mouse user then checkout my article on my favorite mouse for Excel.
How do you Paste Values?
I'd love to know which method you use for pasting values. Please leave a comment below with your favorite. There are other methods too, so please leave a comment below if you use a different shortcut.
Thank you! 🙂
What about if I was trying to paste not from another spreadsheet, but from a website’s table that isn’t excel based? The “value” cannot be pasted, but the source formatting and displayed information can. Is this a hopeless issue?
I you want to try you hand at using macros, this one is great — I use this constantly. There are also a few others I have that are also great for pasting just format, just column width, or just formula.
This will require some understanding of VBA. I recommend adding this code to your Personal.xlsb file so that it’s available from all spreadsheets. Also, you’ll have to link the hotkey combination yourself (alt+F8 to get to where that can be done).
Type SaveRange
val As Variant
addr As String
format As CellFormat
width As Double
End Type
Type SaveRangeArr
val() As SaveRange
End Type
Public OldCount As Integer
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange
Function UndoMacroSetup() As Boolean
On Error GoTo Problem
‘ Abort if a range isn’t selected
If TypeName(Selection) “Range” Then Exit Function
‘ The next block of statements
‘ Save the current values for undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).addr = cell.Address
OldSelection(i).val = cell.Formula
OldSelection(i).width = cell.ColumnWidth
Next cell
‘ Format may or may not exist
‘On Error Resume Next
‘i = 0
‘For Each cell In Selection
‘ i = i + 1
‘ OldSelection(i).format = cell.format
‘Next cell
‘ success
UndoMacroSetup = True
Exit Function
‘ Error handler
Problem:
MsgBox “Can’t save undo”
UndoMacroSetup = False
End Function
Sub PasteSpecialValues()
‘
‘ PasteSpecialValues Macro
‘ Created by Francis Hayes (The Excel Addict)
‘ http://www.TheExcelAddict.com
‘
‘ Keyboard Shortcut: Ctrl+Shift+V
‘
Dim result As Boolean
result = UndoMacroSetup
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
Selection.PasteSpecial Paste:=xlPasteValues
If result Then
‘ Specify the Undo Sub
Application.OnUndo “Undo the PasteSpecialValues macro”, “UndoMacro”
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
Menu Key + v was a revelation. Thank you!
Ctrl+Alt+V, V, Enter will also work.
It is a variation on the Ctrl+V, Ctrl, V. The former just opens up the dialog box for all the paste special options.
Alt+h+v+v works perfectly fine for me
Very nice and informative…….
Try this link….
https://excelintoexcel.blogspot.com/2019/08/excel-copy-paste.html
EXACTLY what I wanted, thank you! I like Ctrl+V, Ctrl, V because it’s easy to remember, but Alt, H, V, V also works nicely.
excellent
Alt+h+v+v
Custom Quick Access Toolbar (QAT) Button: Alt+1
My office has provided me with an older version of Excel (for Windows), and as far as I can see, the only way to reach the Values function is with ctrl-V, ctrl-V. So that is the one I will use.
The video went fast, but it was helpful.
Right-Click, V. Done!
Thanks!
this is the best answer
Alt+1 is the Best and Simple. It was a good Video — Thank you
My new keyboard does not have the Menu key, and I use it A LOT! Thank you for the new ways I can Paste Special Values.
I think I will use the QAT Button option. There are two reason why. First is that it can be done in 2 keystrokes. The second is because I can do the entire sequence with my left hand while my right hand remains on the mouse.
I might try the CTRL+V, CTRL, V because I can use it in Word just by changing it to CTRL+V, CTRL, T. I just do not like that it takes 3 keystrokes to complete the steps.
Now that I know, Menu Key + V!
Thank you!
Once again, you have come through for me. I have taught computer training classes for many years and have only used the Menu Key for Right-click options. Today’s new finding I will love. Menu + V for paste special. LOVE IT!!!!
Thanks!
Number 5 (Ctrl+V, Ctrl, V) amazed me! I didn’t believe it until I tried!
You’re the man!
The CTRL+V,CRTL+V didn’t work for me, and since I’d like to use as minimal key strokes as possible, I really like the Menu+V option. Thanks much! Saves me from creating my own keyboard shortcut.
used for decades via Personal xml (with other vbas)
Sub PasteValues()
If Application.CutCopyMode = False Then Beep
Else
ActiveCell.PasteSpecial Paste:=xlPasteValues
End If
End Sub
Apply shortcut to macro (mine is Ctrl+Swift+v) and done!
excellent tips. never heard these before. saves an age.
SECTION FOR PASTE VALUES HAS THE COMMANDS BUT TELLS US NOTHING WHAT IT DOES —- THIS IS USELESS – VIDEO IS USELESS, YOU HAVE TO TAKE NOTES OR WATCH IT OVER AND OVER TO TRY AND REMEMBER THE SHORTCUTS
Why there’s no ‘Paste Value’ icon in my excel 2019?
I used ‘Paste Value’ often and it would very inconvenience to always click ‘Paste Special’ to get to the ‘Paste Value’ icon. I remember there was a ‘Paste Value’ individual icon in previous version of excel.
Can I get the ‘Paste Value’ file to include in my excel?
Unfortunatelly it doesn’t work with hungarian excel, as all the shortcut letters are different (eg. É instead of V), and sometimes they are the same for multiple commands (eg. É for normal paste, É for paste value).
QAT and ALT+1 is the best, thanks for the idea, it helps!
Yes I got a keyboard