EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Convert Excel Formula/Functions to Values

| | Information Helpful? Why Not Donate.

 

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

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.

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.

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

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates