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.
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 firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
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