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

Get FREE Advanced Excel Exercises with Solutions!

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:

### 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, • 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. ## 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.

## 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. Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

1. Reply How can convert in value or inactive to formula using after one time calculation, OR After one time Calculate the Formula is become inactive or convert in Value? Without Using Special Paste Value.
Or you send your personal Email i can send my Excel sheet so you can put in it as shown in my Question.

2. Reply Md. Abdur Rahim Rasel Sep 19, 2022 at 11:24 AM

This article is how to Convert Formula to Value Without Paste Special in Excel. You can convert Formula in Value using the Keyboard shortcuts(F9), Right-Click Drag Down Option, Notepad to Convert Formula in Value, VBA code, Power Query, and so on. One suitable example is given below regarding your questions.
Suppose we have a data set that contains securing marks and results on Physics of several students of XYZ school. If a student secures less than 50, his/her result will Fail, and more than or equal to 50, his/her result will Pass. We will apply the IF function to do that. Let’s follow the instructions below.
First of all, 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. Please repeat the procedure for the rest of the cells in column D.
Personal Email for sending your Excel sheet: [email protected]
Regards
Md. Abdur Rahim Rasel (Exceldemy Team) Advanced Excel Exercises with Solutions PDF  