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.

Paste Values Does Not Include Formulas or Formatting

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.

Paste Values into Range that Contains Formatting

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.

Paste Special Commands from Home Tab and Right-click Menus

The Paste Special… button on those menus opens the full Paste Special Menu.

Paste Special Button Opens Paste Special Menu in Excel

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.

  1. Alt, E, S, V, Enter (Mac: Ctrl+Cmd+V)
  2. Alt, H, V, V
  3. Menu Key + V
  4. Custom Quick Access Toolbar (QAT) Button: Alt+1
  5. Ctrl+V, Ctrl, V
  6. Custom shortcut with The Paste Buddy Add-in.

In the video I also mention my article on the best keyboards for Excel keyboard shortcuts.

Keyboard Guide Excel Keyboard Shortcuts Comparison

If you're more of a mouse user then checkout my article on my favorite mouse for Excel.

Best Mouse for Excel - Logitech MX Master Mouser

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! 🙂

75 comments

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

  • 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.

  • 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.

  • 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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly