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!