From the starting days of my job, I have learned one thing hard way:
Before sending a report (In Excel) to someone we must convert a formula to value. It simply means to replace a formula with its result value.
Just think this way, when you send a report to someone, they are not concerned with formulas but with the values, a formula returns. Let me tell you some of the interesting ways to do this but before that.
Why You Should Convert a Formula to Value
Here are some reasons why you need to replace a formula to its result value.
Top 6 Ways to Change a Formula to Value in Excel
These are the different methods which I have found to replace a formula with its result value.
1. Paste Values with a Simple Keyboard Shortcut
All you need to do:
- Select the range of the cells where you have formulas.
- Press Ctrl + C to copy cells.
- And then, Alt + E S V.
2. Using Paste Special for Converting to Values
If you don’t like to use a shortcut key then you can simply use the paste special option to replace.
- First of all, select your data range or the entire column.
- And then, right click and copy it.
- Go to Home Tab ➜ Clipboard ➜ Paste ➜ Paste Special.
- Or, you can also press right click and go to Paste Special -> Values.
This method just works like the shortcut key method to replace all the formulas into their static result values.
3. Using Right-Click Menu
And if you don't want to use paste special then you can use right click drag drop method for this conversion.
Here are the steps:
- First of all, select the range of the cells where you have formulas.
- Now, right click on the edge of the selection.
- And by holding the right click, drag the range to the right side and instantly bring it back to the original place and drop it.
- Here you will get the options menu.
- From this option menu, select “Copy Here as Values Only”.
This will instantly convert all the formulas from those cells into static result values.
4. Convert Formulas into Values with a VBA Code
Dim MyRange As Range
Dim MyCell As Range
MsgBox("You Can't Undo This Action. " & _
"Save Workbook First?", _
Case Is = vbYes
Case Is = vbCancel
Set MyRange = Selection
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
Related: Excel VBA Tutorial