How to convert formulas to values in Excel
In some cases users just want to display the calculated value in a cell instead of leaving the formula there. Because you don't want to change the data, the cell's reference cell will be changed, or you simply don't want to show the cell's formula to others. You can do how to convert a formula to a value in the following ways.
How to convert formulas to values in Excel Picture 1
Use Paste Special
Step 1: Select the data area you want to convert.
How to convert formulas to values in Excel Picture 2
Step 2: Right-click in the selected data area. Select the Copy button to copy the data.
How to convert formulas to values in Excel Picture 3
Or you can replace by pressing Ctrl + C .
Step 3: Right-click on the selected area. Select Paste Special (1) => click Paste Values icon 123 (2) .
How to convert formulas to values in Excel Picture 4
Or you can always click the Paste Values icon 123 just below the Paste Options section.
How to convert formulas to values in Excel Picture 5
Replace with VBA
To show Developer card, please refer to the following link: http://TipsMake.vn/macro-trong-excel-la-gi-cach-tao-va-chay-1-macro-trong-excel/.
Step 1: Open the VBA window by selecting the Developer tab (1) => click on the Visual Basic icon (2) .
How to convert formulas to values in Excel Picture 6
Step 2: Microsoft VBA window appears, select Insert (1) => click on the Module icon (2) .
How to convert formulas to values in Excel Picture 7
Step 3: Copy the following code into the Module .
Sub DisplayedToActual() 'Updateby20131126 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "THUTHUATPHANMEM" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False For Each Rng In WorkRng Rng.Value = Rng.Text Next Application.ScreenUpdating = True End Sub
How to convert formulas to values in Excel Picture 8
Step 4: Press the F5 button to run the code, the THUTHUATPHANMEM dialog box appears, select the data to be converted, click the OK button .
How to convert formulas to values in Excel Picture 9
As a result, the formula for the cell has been converted to a value.
How to convert formulas to values in Excel Picture 10
Above Software Tips showed you some ways to convert formulas into values in Excel. Good luck!
You should read it
- MS Excel - Lesson 5: Excel formulas and functions
- How to hide formulas in Excel
- How to convert a CSV file to Excel
- How to delete all formulas in Excel fastest
- Guidelines for importing Chemistry formulas in Excel
- Instructions on how to copy formulas in Excel
- How to create an Excel formula in Notepad ++
- Simple way to convert Excel files to PDF
- How to color formula cells in Excel automatically
- Array formulas in Excel - Tutorials and examples
- How to delete quick recipe on Excel
- How to display formulas and print formulas in Excel