Convert Excel Formula/Functions to Values

Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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.

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS