How to Convert Formulas to Values in Excel (8 Quick Methods)

Microsoft Excel is a versatile place to work with functions, formulas, and shortcuts. It is widely used for financial analysis. While working with a large dataset we might need to edit a large dataset. After entering a formula in a cell you might find it difficult to edit or change the values. For that, we have to convert the formula to values. In this article, I am going to share with you some easy techniques to convert formulas to values in excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Simple Methods to Convert Formulas to Values in Excel

The following article is all about converting formulas to values. Stay tuned to learn 8 simple methods to convert formulas to values in excel.

Suppose we have a dataset of the Company’s employee salary sheet.

Convert Formulas to Values in Excel

When we try to edit or change the value from the total salary column, we are not able to do it. As the formula is used in the cell. For a better understanding, you can check the following screenshot.

Now we will convert the formulas to values with some quick techniques.


1. Use Paste Special Command to Convert Formulas to Values

A paste special tool is a common tool in excel which is mostly used to paste values in a way you want. In this method, I will describe to you how you can convert formulas to values using the paste special command.

Steps:

  • Select cells (F5:F14).
  • Press Ctrl+C to copy.

 Use Paste Special Command to Convert Formulas to Values

  • Click the Home tab from the ribbon.
  • From there select “Paste Special” from the “Paste” option.

 Use Paste Special Command to Convert Formulas to Values

  • A new window named “Paste Special” will appear.
  • Choose “Values” from the paste options and hit the OK button to continue.

  • Our selected data is converted to values now.
  • You can check from the screenshot below by selecting a cell and looking at the formula bar you will see values showing instead of formulas.

 Use Paste Special Command to Convert Formulas to Values

Read More: Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)


2. Perform Paste as Values Option to Convert Formulas to Values

In this method, I am sharing with you a quick method to convert formulas. You can perform paste as values options to Modify formulas to values. Follow the steps below-

Steps:

  • Choose cells from the dataset of your choice that you want to convert to values. Here I have selected cells (F5:F14).
  • Press Ctrl+C to copy.

Perform Paste as Values Option to Convert Formulas to Values

  • While the copy command is working place your mouse cursor over the cells and click the right button of the mouse to appear options.
  • From the “Paste Options” choose “Values”.

  • Thus all your copied cells consisting of formulas are converted into values. Simple isn’t it?

Perform Paste as Values Option to Convert Formulas to Values


3. Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

Those previous methods may seem tricky for you. For this reason, I have brought some simple and easy keyboard shortcuts to this method to work easier. I shared 3 shortcuts, you can use any of those three which you find suitable.

3.1 Use Shift+F10+V Key

Follow my footsteps to learn the shortcut below-

Steps:

  • Choose cells (F5:F14) from the dataset.
  • Press Ctrl+C to copy.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • After copying those cells just press Shift+F10+V.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • As you can see we have successfully converted our formulas to values.

3.2 Press Alt+E+S+V Key

You can also use the Alt+E+S+V keys from your keyboard to get the work done.

Steps:

  • From the dataset choose cells (F5:F14) with formulas from the F
  • Simply copy cells by pressing Ctrl+C.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • From the keyboard Holding the Alt button press E+S+V.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • Now just hit the enter button to activate the shortcuts.

  • Check out the screenshot, you will see we have changed the format of cells to values.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

3.3 Press Alt+H+V+S+V Key

This time we will convert with the help of the Alt+H+V+S+V key.

Steps:

  • Select cells (F5:F14)
  • Copy selected cell by pressing Ctrl+C.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • Holding the Alt button press H+V+S+V.
  • Hit the Enter button to continue.

Apply Keyboard Shortcuts to Convert Formulas to Values in Excel

  • This way we will get our converted result within seconds.

Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting


Similar Readings


4. Perform a Mouse Wriggle Trick to Convert Formulas to Values

This trick is a mouse wriggle trick that many of us don’t know. Follow the steps below-

Steps:

  • Choose cells from the dataset to convert. Here I have chosen cell (F5:F14).

Perform a Mouse Wriggle Trick to Convert Formulas to Values

  • Place the cursor over the selected line.
  • A four-pointed icon will appear.
  • When the icon appears hold the right button of the mouse and move towards any cell to get the output.

  • Release your mouse button you will get some options in the new columns.
  • Choose the “Copy Here as Values Only”.

Perform a Mouse Wriggle Trick to Convert Formulas to Values

  • Here your precious output is ready converting formulas into values.

Perform a Mouse Wriggle Trick to Convert Formulas to Values

Read More: Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)


5. Use Notepad to Convert Formulas to Values in Excel

Well, all the methods, in the beginning, we have converted into values with some tricks. Now we will convert them to values by copying the cells in a notepad.

Steps:

  • Select range of cells (F5:F14).
  • Press Ctrl+C.

Use Notepad to Convert Formulas to Values in Excel

  • Open your notepad and paste the selected range.

  • You will get the values without formulas as notepad is not capable of reading formulas.
  • Again copy the output from the notepad.

Use Notepad to Convert Formulas to Values in Excel

  • Paste the output in your excel workbook desired cells you want.

Use Notepad to Convert Formulas to Values in Excel

  • You can check the output from the following screenshot that you will get only the values extracting formulas from the cells.

Use Notepad to Convert Formulas to Values in Excel


6. Edit a Formula to Convert Formulas to Values in Excel

In this method, I am going to share with you a quick trick so that you can work smoothly.

Steps:

  • Select a cell that has formulas in it.
  • Press F2 to edit the formula.

Edit a Formula to Convert Formulas to Values in Excel

  • Just hit the F9 button to convert to values.

  • As you can see we have our single-cell successfully converted to values without hesitation.

Edit a Formula to Convert Formulas to Values in Excel


7. Utilize Power Query to Convert Formulas to Values in Excel

One of the most powerful tools of excel is the power query tool which is used for making your calculation easier. Check the steps below to learn how you can convert formulas to values using the power query tool of excel.

Steps:

  • Select the whole dataset from the worksheet.
  • Go to the “From Table/Range” from the “Data” tab.

Utilize Power Query to Convert Formulas to Values in Excel

  • A new window will pop up confirming the range.
  • Press OK to continue.

  • From the “Power Query Editor” click the “Close & Load” to continue.

  • A new worksheet will appear converting all the formulas to values.
  • You can confirm the output just by choosing a cell and checking the formula bar output.

Utilize Power Query to Convert Formulas to Values in Excel


8. Run a VBA Code to Convert Formulas to Values in Excel

Running a VBA code you can also convert formulas to values in excel quickly.

Steps:

  • While in the worksheet press Alt+F11 to open the “Microsoft Visual Basic Applications”.

Run a VBA Code to Convert Formulas to Values in Excel

  • Go to the “Module” from the “Insert” option.

Run a VBA Code to Convert Formulas to Values in Excel

  • In the module copy the code from the following and then press “Run”.
  • The code is-
Sub Convert_Formulas_to_Values()
Dim X As Range
Dim Cell As Range
Set X = Selection
For Each Cell In X
If Cell.HasFormula Then
Cell.Formula = Cell.Value
End If
Next Cell
End Sub

Run a VBA Code to Convert Formulas to Values in Excel

  • After hitting the “Run” icon go to your workbook and check the result by choosing a cell.

Read More: Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)


Things to Remember

  • In method 6, I have shared conversion to formulas using F2 and F9 That method is applicable to a single cell. We can not select a whole range of data and convert formulas to values using the F2 and F9 keys. You have to change to values one by one cell.

Conclusion

In this article, I have tried to cover all the simple methods to convert formulas to values in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo