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 with 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 static values.
- Some functions 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 and anyone can copy-paste them easily.
- You don’t want to confuse end-user with complex formulas.
- Or, simply don’t want to show the formulas.
Quick Note: It’s one of those Excel Tips that can help to get better at Basic Excel Skills
1. 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 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 to copy cells.
- And then, Alt + E S V.
By using this shortcut key all the formulas will be replaced with their static result value.
Note: If you don’t want to lose the original formulas then you should make a backup file.
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 it.
- First of all, select your date 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 with their static result values.
3. Using Right-Click Menu
And if you don’t want to use paste special then you can use the 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 options 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
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 where you just need to 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.HasFormula Then MyCell.Formula = MyCell.Value End If Next MyCell End Sub
Related: Excel VBA Tutorial
5. Convert To Values Inside the Formula
Let’s think differently. 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 functions into 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.
6. Get Formula’s Result Value with Power Query
There could be a situation when you need to get the result value of a formula in a separate cell instead of simply converting them. And in that case, you can use a power query.
You need to convert it into values before sending it to your customer. But the thing is you don’t want to lose 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 the power query editor.
- From here, just click on “Close & Load”.
- Now the power query will insert a new table in a new sheet where you have price values instead of formulas.
The best part is when you make any changes 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.
Related: Excel Power Query Tutorial
Sometimes using complex and large formulas make your workbook heavy in size, so it’s better to convert them into absolute values before sharing them with others.
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 sometimes.
I hope you found this tip useful, but now, tell me one thing.
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, I’m sure they will appreciate it.
- Convert Negative Numbers into Positive Ones in Excel
- Insert a Timestamp in Excel
- Insert Bullet Points in Excel
- Insert an Arrow in a Cell in Excel
- Automatically Add Serial Numbers in Excel
- Insert Delta Symbol in Excel in a Cell
- Insert (Type) Degree Symbol in Excel
⇠ Back to An Introduction to Microsoft Excel (Basics Knowledge + Part & Components)
10 thoughts on “How to Convert a Formula to Value in Excel”
Greetings I have the following problem:
I have a list of names and i want a function or a formula that will identify repeated value and change it automatically,
eg.: if i type John in cell A1, and then repeat the same name in cell A10, and press enter i need excel to identify it and change to John1, or John2, without changing the value of cell A1.
more like when you create a document in windows and it names it with a number to indicate a repetition.
This article wil help me so much to speed up with my business, thank you so much!
I found the vba tutorial to convert formulas into values with a vba code very useful. couple of question,
1)How to set up a command button to effect this
2) or what would be the code if you want to vba to automatically run this command if a say after
a 12 month cycle looking forward to your reply
Easier keyboard shortcut sequence to remember (Excel 2010 on)
CTRL + C
CTRL + V
CTRL (on its own)
V (on its own)
The last two steps activate the Paste Options button on the worksheet which has various Paste Special options, each with their own shortcut – V is paste values
An even easier way is to convert everything to pdf files. Send the pdf file and keep the Excel file for those that know what they are doing.
Thank you for helping me with excel.
I have one problem regarding the VBA code to convert formulas to text.
The VBA code gives me an error and highlights the select case statement when I run the program, I tried assigning a variable name to the case but this didnt help. Can you please tell me why the error occurs.
very very usefull thanks for ur helping nature
I’ve got problem with:- VBA Code is “ERROR” It turn to Red Text from
4. Convert Formulas into Values with VBA
MsgBox(“You Can’t Undo This Action. ” & _
“Save Workbook First?”, _
This are the VBA statements i use to change formulas into values:
Application.calculation = xlCalculationManual
.Value = . Value
Application.calculation = xlCalculationAutomatic
I think it´s faster than a loop over all selection cells.