How to Convert a Formula to Value in Excel

 



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.

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

All you need to do:

  • Select the range of the cells where you have formulas.
  • Press Ctrl + C to copy cells.
1 copy cells to convert formulas to a static value min
  • And then, Alt + E S V.
2 shortcut key to convert formula into a static value min

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.

  1. First of all, select your data range or the entire column.
  2. And then, right click and copy it.
  3. Go to Home 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 right click and go to Paste Special -> Values.
4 right click to use paste special to convert formula into a static value min

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:

11 convert formula into a static value using right click method
  1. First of all, select the range of the cells where you have formulas.
  2. Now, right click on the edge of the selection.
  3. 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.
  4. Here you will get the options menu.
  5. 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

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

5. Convert To Values Inside the Formula