Using a Macro

To use this macro, select Developer → Code → Visual Basic (pre-2007, Tools → Macro → Visual Basic Editor) or Alt/Option-F11. While in the VBE, select Insert → Module to insert a standard module. Enter the preceding code directly into the module. Click the window's Close button, or press Alt-Q to get back to Excel. Select Developer → Code → Macros (pre-2007, Tools → Macro → Macros) or Alt/Option-F8, select ValuesOnly, and then click Options to assign a shortcut key to the macro. When you use the macro you will be presented with an InputBox and asked to select a range that contains your formulas. The selected range address will show automatically in the InputBox, and all you need to do to make the conversion is click OK.

If you frequently convert cells containing formulas and functions to their values, you can use this simple macro:

    Sub ValuesOnly( )
    Dim rRange As Range
        On Error Resume Next
            Set rRange = Application.InputBox(Prompt:="Select the formulas", _
                             Title:="VALUES ONLY", Type:=8)
            If rRange Is Nothing Then Exit Sub
        rRange = rRange.Value
    End Sub