How Do I Change A Formula To A Value In Excel

From the starting days of my job, I have learned one thing difficult fashion:

Earlier sending a report (In Excel) to someonewe must convert a formula to value. Information technology simply ways to replace a formula with its issue value. Just think this way, when you lot send a written report to someone, they are not concerned with formulas but with the values, a formula returns.

Allow me tell you some of the interesting ways to do this but before that.

Why Yous Should Catechumen a Formula to Value

Hither are some reasons why y'all need to supersede a formula with its result value.

If you are sending a report to someone without its source data and so it's better to convert all the formulas into static values.
Some functions are volatile and when y'all update your worksheet their value will recalculate.
To prevent the end-user to make any change in the calculation.
Static values are easily editable and anyone tin can copy-paste them easily.
You don't desire to misfile cease-user with complex formulas.
Or, simply don't want to bear witness the formulas.

Quick Note: It's one of those Excel Tips that can help to get better at Basic Excel Skills.

i. Paste Values with a Simple Keyboard Shortcut

When it comes to speed and ease, shortcut keys are the best and to turn formulas into values the easiest mode is to use a shortcut cardinal.

All you need to do:

Select the range of the cells where you take formulas.
Printing Ctrl + C to copy cells.

1 copy cells to convert formulas to a static value min

And and then, Alt + E S 5.

2 shortcut key to convert formula into a static value min

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

Notation: If you don't want to lose original formulas and so you should make a backup file.

two. Using Paste Special for Converting to Values

If you don't like to use a shortcut key and then you tin can only use the paste special option to replace it.

Start of all, select your data range or the unabridged cavalcade.
And and so, correct-click and copy information technology.
Get to Abode Tab ➜ Clipboard ➜ Paste ➜ Paste Special.

3 go to home tab to use paste special to convert a formula into a static value min

Or, you can also press correct-click and go to Paste Special -> Values.

4 right click to use paste special to convert formula into a static value min

This method but works like the shortcut key method to supplant all the formulas into their static result values.

And if you don't desire to utilise paste special and so you tin employ the correct-click elevate-drop method for this conversion. Here are the steps:

11 convert formula into a static value using right click method

First of all, select the range of the cells where you have formulas.
At present, right-click on the edge of the choice.
And by holding the correct-click, elevate the range to the right side and instantly bring information technology back to the original identify and drop it.
Here yous will get the options menu.
From this options bill of fare, select "Copy Here as Values Only".

This will instantly convert all the formulas from those cells into static result values.

4. Catechumen Formulas into Values with a VBA Lawmaking

If you want to automatically convert all the formulas into text or numeric values so you tin can use VBA for that. Here I have mentioned a simple VBA code where you merely demand to select the range of the cells and run this macro.

                Sub ConvertToValues()  Dim MyRange Every bit Range Dim MyCell Equally Range  Select Case  MsgBox("Yous Tin can't Undo This Activity. " & _ "Save Workbook First?", _ vbYesNoCancel, "Alarm") Example Is = vbYes ThisWorkbook.Salve Case Is = vbCancel Get out Sub  Terminate Select  Set MyRange = Selection  For Each MyCell In MyRange If MyCell.HasFormula So MyCell.Formula = MyCell.Value Terminate If Next MyCell  End Sub

Related: Excel VBA Tutorial

5. Convert To Values Inside the Formula

Let's recollect differently. Y'all have a complex formula which is a combination of two or iii functions. Here you have a long formula to make clean a text value and and so convert it into the proper case.

5 edit formula to convert a formula into a static value min

Now if you want to convert the result of ii substitute functions in static text values, hither are the steps.

Edit the formula using F2.
Select just that part of the formula which y'all need to catechumen to a static value.
Printing F9.

6 press f9 to convert a formula into a static value text min

Now you but have a text value to convert into the proper text example.

six. Get Formula'southward Issue Value with Ability Query

There could be a situation when you need to go the result value of a formula in a carve up prison cell instead of simply converting them. And in that case, you can use a power query.

You lot demand to convert it into values earlier sending it to your client. But the affair is you don't desire to lose all the formulas. And then here are the simple steps to get values from formulas using power query.

Select the tabular array and become to Data Tab ➜ Get & Transformation ➜ From Table.

7 load data into power query to convert formula into a value min

It will open your table into the power query editor.

8 data in power query editor to convert formula into a static value min

From hither, only click on "Shut & Load".

9 close and load data into excel sheet to convert formula into a static value min

Now the ability query volition insert a new table in a new sheet where you accept price values instead of formulas.

10 get data in a seprate sheet after converting a formula into a value min

The best role is when you lot brand whatever changes in your formula table you just demand to refresh the power query table. Past using this way you lot don't need to copy-paste data every time.

Related: Excel Power Query Tutorial

Conclusion

Sometimes using complex and large formulas make your workbook heavy in size, so it'due south better to convert them into accented values before sharing them with others.

And all the methods which we have discussed above can aid you in dissimilar situations. If you ask me, my favorite one is the keyboard shortcut but I besides like to employ ability query sometimes.

I promise you found this tip useful, simply now, tell me one thing.

What's your favorite one? Do you lot accept any other method which you use while converting formulas into values?

Please share with me in the comment section, I'd dear to hear from you and don't forget to share this tip with your friends, I'thousand sure they will appreciate information technology.

More than Excel Tutorials