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.


Download Practice Workbook

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


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

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.

excel convert formula to value without paste special


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.

Use Keyboard Shortcut to Convert Formula to Value Without Paste Special

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

Use Keyboard Shortcut to Convert Formula to Value Without Paste Special


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.

Use Keyboard Shortcut to Convert Formula to Value Without Paste Special

Read More: Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)


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.

Apply Right-Click Drag Down Option to Convert Formula to Value Without Paste Special

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

Apply Right-Click Drag Down Option to Convert Formula to Value Without Paste Special

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

Apply Right-Click Drag Down Option to Convert Formula to Value Without Paste Special

Read More: How to Convert Formula to Value Automatically in Excel (6 Effective Ways)


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.

Use Notepad to Convert Formula to Value Without Paste Special in Excel

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

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

Use Notepad to Convert Formula to Value Without Paste Special in Excel

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

Read More: How to Show Value Instead of Formula in Excel (7 Methods)


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

Run a VBA Code to Convert Formula to Value Without Paste Special in Excel

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

Run a VBA Code to Convert Formula to Value Without Paste Special in Excel

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

Run a VBA Code to Convert Formula to Value Without Paste Special in Excel

Read More: How to Convert Formula Result to Text String in Excel (7 Easy Ways)


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

Use Power Query to Convert Formula to Value Without Paste Special in Excel

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

Use Power Query to Convert Formula to Value Without Paste Special in Excel

Step 2:

  • Now, from the Home tab, go to,

Home → Close & Load → Close & Load

Use Power Query to Convert Formula to Value Without Paste Special in Excel

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

Use Power Query to Convert Formula to Value Without Paste Special in Excel

Read More: Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)


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

Md. Abdur Rahim Rasel

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.

2 Comments
  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.

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

    Hi AHMAD KHAN!
    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.
    Convert Formula to Value Without Paste Special in Excel
    As a result, you will be able to convert the formula to value without paste special.
    Convert Formula to Value Without Paste Special in Excel
    Please repeat the procedure for the rest of the cells in column D.
    Please download the Excel file for your practice.
    https://www.exceldemy.com/wp-content/uploads/2022/09/Convert-Formula-to-Value-Without-Paste-Special.xlsx
    Thank you for your comment.
    Personal Email for sending your Excel sheet: [email protected]
    Regards
    Md. Abdur Rahim Rasel (Exceldemy Team)

Leave a reply

ExcelDemy
Logo