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


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

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

Now if you want to convert the result of two substitute function in  static text values, here are the steps.

  1. Edit the formula using F2.
  2. Select only that part of the formula which you need to convert to a static value.
  3. Press F9.
6 press f9 to convert a formula into a static value text min

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 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 a 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 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.
7 load data into power query to convert formula into a value min
  • It will open your table into power query editor.
8 data in power query editor to convert formula into a static value min
  • From here, just click on “Close & Load”.
9 close and load data into excel sheet to convert formula into a static value min
  • Now the power query will insert a new table in a new sheet where you have price values instead of formulas.
10 get data in a seprate sheet after converting a formula into a value min

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

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.


9 thoughts

Leave a Comment

Your email address will not be published.

  1. Hi
    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

  2. Easier keyboard shortcut sequence to remember (Excel 2010 on)
    Select data
    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

  3. Hi,

    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.


  4. I’ve got problem with:- VBA Code is “ERROR” It turn to Red Text from

    4. Convert Formulas into Values with VBA

    Select Case
    MsgBox(“You Can’t Undo This Action. ” & _
    “Save Workbook First?”, _
    vbYesNoCancel, “Alert”)

  5. This are the VBA statements i use to change formulas into values:

    Application.calculation = xlCalculationManual

    With selection


    .Value = . Value

    End With

    Application.calculation = xlCalculationAutomatic
    I think it´s faster than a loop over all selection cells.
    Sincerely Yours,