2 Simple Ways to Convert Formulas to Values in Excel
2 Simple Ways to Convert Formulas to Values in Excel: In today’s fast-paced world, Excel has become an indispensable tool for data analysis and management. From basic calculations to complex financial models, Excel offers a range of functions and formulas that help users manipulate data with ease. However, sometimes it’s necessary to convert formulas into values to prevent changes to data or to hide formulas from others. In this article, we’ll explore two simple ways to convert formulas into values in Excel.
Method 1: Using the Paste Special feature
To convert formulas to values in Excel, there are several ways. The simplest way is to use Paste Special. To do this, follow these steps:
Step 1: Select the data range you want to convert. Press CTRL + C to copy this data range.
2 Simple Ways to Convert Formulas to Values in Excel
Step 2: In the place where the data is pasted, right-click and select Paste Special, then select the Paste Values icon. Or you can directly select the Paste Values icon below the Paste Options section as illustrated below.
2 Simple Ways to Convert Formulas to Values in Excel
Immediately after that, the data range containing the formula has been copied to another data range without being referenced by the formula, so it still retains its value.
2 Simple Ways to Convert Formulas to Values in Excel
Method 2: Using VBA
The second way to convert formulas to values in Excel is to use VBA. To do this, follow these steps:
Step 1: On the Developer tab of the ribbon, select Visual Basic. The VBA window will appear.
2 Simple Ways to Convert Formulas to Values in Excel
Step 2: In the Microsoft VBA window, select Insert. Then select Module.
2 Simple Ways to Convert Formulas to Values in Excel
Step 3: Copy the code below into the Module.
Sub DisplayedToActual()
‘Updateby20131126
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = “CONVERT”
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
2 Simple Ways to Convert Formulas to Values in Excel
Step 4: Press F5 to run the code. The CONVERT dialog box will appear. You select the data range to be converted and then press OK to complete.
2 Simple Ways to Convert Formulas to Values in Excel
The result is that all cells containing formulas have been completely converted to values.
2 Simple Ways to Convert Formulas to Values in Excel
In conclusion, Excel provides a wide range of tools to manage and analyze data, and the ability to convert formulas into values is an important function for many users. Whether you prefer using Paste Special or VBA, knowing how to convert formulas into values can save time and help you achieve your goals more efficiently. We hope this article has been helpful in providing you with valuable information and insights.
