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.

Use Paste Special

Step 1: Select the data area you want to convert.

Step 2: Right-click in the selected data area. Select the Copy button to copy the data.

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

Or you can always click the Paste Values icon 123 just below the Paste Options section.

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

Step 2: Microsoft VBA window appears, select Insert (1) => click on the Module icon (2) .

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

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 .

As a result, the formula for the cell has been converted to a value.

Above Software Tips showed you some ways to convert formulas into values ​​in Excel. Good luck!