Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)

Excel has an inbuilt data display orientation to indicate entries such as Numbers or Text. On occasions, we need to convert formulas to values without paste special in Excel. This is an easy task and time-saving task also. Today, in this article, we’ll learn five quick and suitable ways how Excel convert formula to value without paste special effectively with appropriate illustrations.

Convert Formula to Value Without Paste Special in Excel: 5 Suitable Ways

Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the sales representatives, the Product Name, the Revenue Earned by the sales representatives, and the Profit earned by the sales representatives are given in Columns B, C, D, and E respectively. We can easily convert formulas to values without paste special in Excel by using the keyboard shortcuts, right-click drag down command, the Power Query, and running a VBA Macros also. Here’s an overview of the dataset for today’s task.

1. Use Keyboard Shortcut to Convert Formula to Value

In this portion, we will apply the keyboard shortcuts to convert the formula to value without paste special. This is an easy task. This is time-saving also. We will use several keyboard shortcuts to convert formula to value without paste special.

1.1 Apply SHIFT Key

In this sub-method, we will apply the SHIFT key. Let’s follow the instructions below to convert the formula to value without paste special!

Steps:

• First of all, select the cells that contain the formulas. From our dataset, we will select cells E5 to E14. After that, copy the selecting cells. To do that, press Ctrl + C simultaneously on your keyboard.

• Hence, press SHIFT + F10 simultaneously, and then press V on your keyboard.

• As a result, you will be able to convert the formula to value without paste special which has been given in the below screenshot.

1.2 Use F9 Function Key

You can also convert the formula to value without paste special by using the F9 function key. To do that, firstly select the formula in the formula bar, then press F9.

• As a result, you will be able to convert the formula to value without paste special.

Related Article:

• How to Convert Formula to Value Automatically in Excel
• Convert Formula to Value in Multiple Cells in Excel
• How to Convert Formula Result to Text String in Excel

2. Apply Right-Click Drag Down Option to Convert Formula to Value in Excel

Now, we will use the right-click drag-down option to convert the formula to value without paste special. Undoubtedly, this is an easy and time-saving task. From our dataset, we can easily apply the right-click drag-down option to convert the formula to value without paste special. Let’s follow the instructions below to convert the formula to value without paste special!

Steps:

• First, select the cells that contain the formulas. From our dataset, we will select cells E5 to E14. After that, copy the selecting cells. To do that, press Ctrl + C simultaneously on your keyboard.

• After that, place your cursor on the right side of the selected cells. Instantly, a four-directional array will appear in front of you.

• Further, press the right-click on your mouse, and drag it to the right side of your copying cells. As a result, a window will pop up. From that window, select the Copy Here as Values Only option.

• After completing the above process, you will be able to convert the formula to value without paste special which has been given in the below screenshot.

3. Use Notepad to Convert Formula to Value

In this method, we will use Notepad to convert the formula to value without paste special. Let’s follow the instructions below to convert the formula to value without paste special!

Steps:

• First, select the cells E5 to E14. After that, copy the selecting cells. To do that, press Ctrl + C simultaneously on your keyboard.

• After that, open Notepad and paste the copying values with the formula.

• Hence, select the values and press Ctrl + C again on your keyboard.

• Now, paste the copying values into the Excel file from the Notepad. As a result, you will be able to convert the formula to value without paste special which has been given in the below screenshot.

4. Run a VBA Code to Convert Formula to Value in Excel

Now I’ll show how to convert the formula to value without paste special by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will convert the formula to value without paste special. Let’s follow the instructions below to learn!

Step 1:

• First of all, select the cells array E5 to E14, and then open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Convert Formula to Value Without Paste Special will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the convert formula to value without paste special module pops up. In the Convert Formula to Value Without Paste Special module, write down the below VBA.

Sub Convert_Formula_to_Value()
Dim R As Double
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
R = cell.Value
cell.NumberFormat = "@"
cell.ClearContents
cell.Value = CStr(Cells)
End If
Next cell
End Sub

• The macro takes cell entries from the selection and declares them as Double. Then, the VBA FOR loop checks whether each cell is not empty and numeric or not. Assigning cells and their format, the macro clears all the cell contents. Afterward, the VBA CStr function converts cell values to strings.

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the VBA Code, you will be able to convert the formula to value without paste special which has been given in the below screenshot.

READ MORE: Excel VBA: Convert Formula to Value Automatically

5. Use Power Query to Convert Formula to Value

Undoubtedly, the Power Query Editor can easily convert the formula to value without paste special. From our dataset, we can do it easily. Let’s follow the instructions below to convert the formula to value without paste special!

Step 1:

• First, select the cells that contain the formulas. From our dataset, we will select cells B4 to E14. Hence, from your Data tab, go to,

Data → Get & Transform Data → From Table/Range

• As a result, a Create Table dialog box will appear in front of you. From the Create Table dialog box, press OK.

• Hence, you will be able to enable the Power Query Editor.

Step 2:

• Now, from the Home tab, go to,

Home → Close & Load → Close & Load

• After completing the above process, you will be able to convert the formula to value without paste special which has been given in the below screenshot.

Read More:

• How to Stop Formula to Convert into Value Automatically in Excel
• How to Return Value of Cell Not Formula in Excel 

Things to Remember

👉 You can press SHIFT + F10 simultaneously, and then press V on your keyboard to convert the formula to value without paste special.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

👉 In Microsoft 365, Excel will show the #Value! Error if you don’t select the proper dimension. The #Value! error occurs when any of the elements of the matrices is not a number.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all of the suitable methods mentioned above to convert formula to value without paste special will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.