Wasim Akram
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.
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.
- Click the Home tab from the ribbon.
- From there select “Paste Special” from the “Paste” option.
- 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.
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.
- 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?
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.
- After copying those cells just press Shift+F10+V.
- 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.
- From the keyboard Holding the Alt button press E+S+V.
- 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.
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.
- Holding the Alt button press H+V+S+V.
- Hit the Enter button to continue.
- This way we will get our converted result within seconds.
Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting
Similar Readings
- How to Stop Formula to Convert into Value Automatically in Excel
- Putting Result of a Formula in Another Cell in Excel (4 Common Cases)
- How to Convert Formula Result to Text String in Excel (7 Easy Ways)
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).
- 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”.
- Here your precious output is ready converting formulas into 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.
- 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.
- Paste the output in your excel workbook desired cells you want.
- You can check the output from the following screenshot that you will get only the values extracting formulas from the cells.
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.
- Just hit the F9 button to convert to values.
- As you can see we have our single-cell successfully converted to values without hesitation.
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.
- 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.
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”.
- Go to the “Module” from the “Insert” option.
- 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
- 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.
