From the starting days of my job, I have learned one thing hard way that before sending a report to someone we must convert a formula to value.
It simply means to replace a formula with its result.
Just think this way, when you send a report to someone, they are not concerned with formulas but result.
Here are some reasons that why you need to replace a formula to its result value.
I’m sure at this point you are convinced that you should convert formulas to values.
So let me share with you 6-Quick Methods for this.
All the methods which I've shared here are different in nature and you can use them according to need. One method works in one situation and other in different.
When it comes to speed and ease, shortcut keys are the best. And, to replace a formula with a static value the easiest way is to use a shortcut key.
All you need to do:
By using this shortcut key all the formulas will be replaced with their static result value.
If you don’t like to use a shortcut key then you can simply use the paste special option to replace.
This method just works like the shortcut key method to replace all the formulas into their static result values.
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:
This will instantly convert all the formulas from those cells into static result values.
If you want to automatically convert all the formulas into text or numeric values then you can use VBA for that.
Here I have mentioned a simple VBA code. Just select the range of the cells and run this macro.
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
Let’s think in a different way. You have a complex formula which is a combination of two or three functions.
Here you have a long formula to clean a text value and then convert it into the proper case.
Now, if you want to convert the result of two substitute function in a static text values, here are the steps.
Now, you just have a text value to convert into the proper text case.
There could be a situation when you need to get result value of a formula in a separate cell instead of simply converting them.
And, in that case, you can use power query. Below you have price table where price is calculated with some calculations.
You need to convert it into values before sending to your customer. But the thing is you don't want to loose all the formulas.
So, here are the simple steps to get values from formulas using power query.
The best part is when you make any change in your formula table you just need to refresh the power query table. By using this way you don’t need to copy paste data every time.
Sometimes using complex and large formulas make your workbook heavy in size so it’s better to convert them into absolute values before sharing with other.
And, all the methods which we have discussed above can help you in different situations.
If you ask me my favorite one is the keyboard shortcut but I also like to use power query.
What’s your favorite one? Do you have any other method which you use while converting formulas into values?
Please share with me in the comment section, I’d love to hear from you. And, don't forget to share this tip with your friends.