Convert Excel Formula/Functions to Values

Most Excel Spreadsheets contain formulas. There are also some instances when you want only the result of a formula rather than leaving the formula in place where it will change if or when the data it references changes. There are a few ways to do this manually, which we will look at first. Then I will show you a very simple macro that makes the job a breeze.

Paste Special

If You Are Going To Be Making A Copy Of The Formula Results And Still Leave The Original Formulas In Place, The Use Of Paste Special Is One Way. Let's Assume Your Formulas Reside In The Range A1:A100. Select This Range Then Copy, Now Select The Starting Cell For The Mirror Results And Go To Edit>Paste Special-Values And Then Click OK.
If You Want To Override The Original Formulas With Their Results Then Select The Formula Range Then Copy. Now With The Formula Range Still Selected Go To Edit>Paste Special-Values And Then Click OK.

Copy Here as Values Only

Here is another way to do the same thing, but without the use of Paste special via the Paste special dialog. This way is much quicker and shows a pop-up menu many didn't even know existed.

If You Are Going To Be Making A Copy Of The Formula Results And Still Leave The Original Formulas In Place. Select The Formula Range And Now Right Click On The Right, Or Left Border Of The Selection (Anywhere Except The Fill Handle -Small Black Square Bottom Right Of Selection) Now, Holding Down The Right Mouse Button, Drag To The Destination, Release The Right Mouse Button And Click "Copy Here As Values Only" From The Resulting Pop-Up Shortcut Menu.
If You Want To Override The Original Formulas With Their Results. Select The Formula Range And Now Right Click On The Right, Or Left Border Of The Selection (Anywhere Except The Fill Handle -Small Black Square Bottom Right Of Selection) Now, Holding Down The Right Mouse Button, Drag Over One Column To The Right, Or Left And Then Back To The Starting Range, Release The Right Mouse Button And Click "Copy Here As Values Only" From The Resulting Pop-Up Shortcut.

Screen Shot of the resulting pop-up menu

Using a Macro Written in Excel VBA

If converting formulas and functions in Excel is a common task for you, consider using this simple macro.

Sub ValuesOnly()

Dim rRange As Range

'www.ozgrid.com

    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

To use this macro, go to Tools>Macro>Visual Basic Editor (Alt+F11), now while in the VBE (Visual Basic Editor) go to Insert>Module to insert a Standard Module. Copy the code above and paste it directly into the module. Click the top right X, or push Alt+Q, to get back to Excel. Now go to Tools>Macro>Macros (Alt+F8) select Values Only then click Options to assign a shortcut key.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.