# 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 a time-saving task. 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 worksheet that contains information about several sales representatives of the 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-clicking drag down command, the Power Query, and running 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 a 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 selected 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 a paste special by using the F9 function key. To do that, first select the formula in the formula bar, then press F9.

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

### 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 the paste special feature. 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 the paste special. Letâ€™s follow the instructions below to convert the formula to value without the 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 selected 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 the paste special!

Steps:

• First, select the cells E5 to E14. After that, copy the selected 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.

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

## Related Articles

<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

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

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