Get this Free Cheat Sheet To learn 6 simple ways to convert formula to a static value

Excel Productivity Guide

Useful Excel Tips | Keyboard Shortcuts | VBA Codes

**Download this Free E-Book **to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

From the starting days of my job, I have learned one thing hard way that before sending a report to someone we must convert all the formulas into values.

I have faced this problem so many times that after sending a file to someone, he/she made a change (mistakenly) into it and then they say, “there is some problem with your data”.

The biggest point is when you send a report to someone at a higher level, they are not concerned with your formulas but result.

So, whenever you share a file with someone make sure to replace all the formula with static values so that the result remain same.

And today, in this post, you will learn 6 simple ways to convert a formula to a static value.

Quick Navigation

Here are some reasons that why you need to convert a formula to its result value.

- If you are sending a report to someone without its source data then it’s better to convert all the formulas into hard values.
- There are some functions in excel which are volatile and when you update your worksheet their value will recalculate.
- To prevent the end user to make any change in the calculation.
- Static values are easily editable.
- Or, if you don’t want to confuse end user with complex formulas.

Don’t have time to read all the methods?

No worries. Get this Free Cheat Sheet so you can read it when it’s convenient for you.

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:

- Select the range of the cells where you have formulas.
- Press Ctrl + C.

- And then, Alt + ESV.

By using this shortcut key all the formulas will be replaced with their static result value.

Quick Note: If you don’t want to loose original formulas then you should make a backup file.

If you don’t like to use a shortcut key then you can simply use the paste special option for this conversion of formulas into values.

- 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 and it will replace all the formulas into their 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.

- 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 a option menu.
- From this option menu, select “Copy Here as Values Only”.

This will instantly convert all the formulas from those cells into numbers or whatever values you have.

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 for this.

To use this code all you need to do is, select the range of the cells and run this macro.

`Sub ConvertToValues()`

Dim MyRange As Range

Dim MyCell As Range

Select Case

MsgBox _("You Can't Undo This Action. " & _

"Save Workbook First?", _

vbYesNoCancel, "Alert")

Case Is = vbYes

ThisWorkbook.Save

Case Is = vbCancel

Exit Sub

End Select

Set MyRange = Selection

For Each MyCell In MyRange

If MyCell.Has

Formula Then

MyCell.Formula = MyCell.Value

End If

Next MyCell

End Sub

The only thing you need to take care is when you use a VBA code you can’t undo that action to come back to the original formulas.

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.

- Edit the formula using F2.
- Select only that part of the formula which you need to convert to a static value.

- Press F9.

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.

And, 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.

- Select the table and go to Data Tab -> Get & Transformation -> From Table.

- It will open your table into power query editor.

- From here, just click on “Close & Load”.

Now, power query will insert a new table in a new sheet where you have price values instead of formulas.

And, 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 would love to hear from you.

- beauzhr
- simlaoui