How to Convert Formula to Value in Excel Without Paste Special

Sometimes we need to convert formula to value in Excel without using special paste. In this article, we are going to show you how to convert a formula to value in Excel without using paste special in 6 swift approaches. We have also included some relevant concerns at the end of this article. So let’s get started.


📁 Download Excel File

Download the free workbook from here:


Learn to Convert Formula to Value in Excel Without Paste Special with Easy Approaches

While writing this article, “How to Convert Formula to Value in Excel without Paste Special”. we’ve considered a dataset that contains approximately 10 columns and 5 rows. In this dataset, we added Name, Salary, Deduction, and Net Salary to make you better understand. But if you want you can change the entities and put the values of your own.

Dataset of converting formula to value without paste special in Excel

Generally, the value of a particular cell is given as a formula in the Formula Bar. The following approaches will change this form.

convert formula to value without paste special in Excel

APPROACH

1. Application of Keyboard Shortcut

Using keyboard shortcuts for converting formulas to values is one of the easiest methods. If you want to follow this procedure, you can do it in two ways. Both ways have been shown here.

APPLICATION OF KEYBOARD SHORTCUT

1.1 Usage of F9 Function Key on Keyboard

There is a function key named F9. You can convert formulas to values using this function key. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Keyboard shortcut sheet.
  • Then select the Formula Bar.
  • After that press the F9 function key.

usage of keyboard shortcut for converting formula to value without paste special in Excel

  • Consequently, you will see that your formulas have converted to values as well.

Result of using function key to convert formula to value without paste special in Excel

Finally, using the F9 function key will save you time while converting formulas to values in Excel without using paste special.

APPLICATION OF KEYBOARD SHORTCUT

1.2 Pressing Shift and Function Keys

If you want to use the keyboard shortcut in another way then this procedure is for you. By pressing the Shift key and Function keys simultaneously you can do it. Just follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the Keyboard shortcut sheet.
  • After that, select the entire E column.
  • Then, tap Ctrl+C on the keyboard.
  • As a result, your whole E column will be copied.

Convert formula to value without paste special in Excel

  • Secondly, press Shift + F10 + V simultaneously.

keyboard shortcut for copying formula to value without using paste-special in Excel

  • Finally, the formulas will convert to values.

Consequent of using keyboard shortcut

Undoubtedly, this is a very simple method of converting formulas to values in Excel.

APPROACH

2. Converting Formula to Value via Notepad

Converting formulas to values in Excel via Notepad is a very simple technique. If you perform the following steps, you can do this very easily.

⬇️⬇️ STEPS ⬇️⬇️

  • primarily, you need to go to the Use of Notepad sheet.
  • Like before, select the E column.
  • Then press Ctrl+C on the keyboard.
  • It will copy the entire values of that column.

Converting Formula to value via notepad without paste special in Excel

  • Now open the Notepad on your computer.
  • And paste the values here in the notepad.
  • Again copy the values from that Notepad by tapping Ctrl+C.

  • Then go back to the Use of Notepad sheet.
  • And paste the values to the E column, copied from the Notepad.

Result of converting formula to value via notepad without paste special in excel

Finally, you will see the formulas have been converted to values in Excel.

APPROACH

3. Using Power Query Method

When you intend to convert formulas to values in Excel without using paste special, then this approach may be very helpful to you. Follow the steps given below.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the entire cells. In our case, we have selected B4 cells to E10 cells.
  • Now go to the Data tab.
  • Then click on the From Table/Range option.

Power query method of converting formula to value without paste special in excel

  • After that, you will find a dialogue box named Create Table.
  • Click on the OK.

create table box of power query method

  • After clicking on OK, the Power Query Editor will be activated.

power query editor

  • Then go to the Home tab of the Power Query Editor.
  • Select the Close & Load option.
  • Finally, click on Close & Load.

click on Close and Load option

  • Consequently, you will obtain all the formulas converted to values.

result of using power query method

Finally, the Power Query method is a very simple method while converting formulas to values without using paste special in Excel.

APPROACH

4. Using Mouse Trick

While converting formulas to values in Excel without paste special, you can use the mouse wriggle trick. This is one of the simplest ways to convert formulas to values in Excel. Here, the following steps are given for you to perform the action.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Mouse trick sheet.
  • Then select the E column ( from cell E5 to E10).

Selecting column

  • Put the mouse cursor above the outline of the selected cells. You will see an icon of four arrows pointing in four directions.

Using Mouse trick

  •  Right-Click on the mouse. Hold the button and drag the outline to the right and bring it back to its original position.
  •  The moment you leave the mouse button (Right button), you will see a menu.
  • In the menu, click on the Copy Here as Values Only option.

Selecting "Copy here as Values Only" option

  • Consequently, you will get the formulas turned values as well.

result of using mouse trick

Finally, the mouse wriggle trick is very user-friendly if want to know how to convert formulas to values in Excel using paste special.

APPROACH

5. Copying and Pasting Formulas as Values in Excel

In this article, we are discussing the approaches of converting formulas to values in Excel without a paste special. Copying and pasting formulas as values are one of them. You can obtain this approach by following some simple steps given below.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Copy-Paste Option sheet.
  • Select the desired cell (we took cells E5 to E10) and tap Ctrl+C.
  • It will copy all the particulars of those cells.

  • Now, go to the Home tab.
  • Select the Paste option.
  • Click on the Values from the Paste Values option
  • For your better understanding-  HomePastePaste ValuesValues

Copying and pasting formulas as Values

  • Finally, You will see the formulas converted to values as well.

result of using copy paste option

So we can say that copying and pasting is one of the easiest approaches to convert formulas as values in Excel.

📕 Read More: How to Convert Formula to Value for Multiple Cells in Excel

APPROACH

6. Applying VBA Code

If want to convert formulas to values using VBA code, you need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the VBA sheet.
  • Then select the E column.
  • Now, press Alt + F11.

  • The VBA window will be opened.
  • Select Insert and then click on the Module option.

Inserting Module

  • After clicking in the Module, you see a box.
  • When you get the box, write down the following code.

Code

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 = R
End If
Next cell
End Sub

VBA code

  • Now, click on the Run option.
  • Finally, you will obtain the formulas converted to values as well.

result of using VBA code

Surely, this approach is very simple while converting formulas to values without using paste special in Excel.


📄 Important Notes

While working on the above-mentioned procedures you need to keep the following things in your mind-

🖊️  When you use the keyboard shortcut, be careful about the function keys.

🖊️  You need to use Notepad while using the method of converting formula to value via Notepad.

🖊️  You need to be cautious while using the VBA code. There you make any mistake when using the VBA code, you won’t get the desired result as well.


 📝  Takeaway from This Article

If we summarize the whole article, we basically have tried to show you how to convert formula to value without paste special in Excel with some approaches.

📌  Firstly, in Approach 1, we’ve tried to show you the usage of the Keyboard Shortcut. This procedure can be done in two ways.

 I. By using the F9 function key.

II. By pressing the Shift and Function keys simultaneously.

📌  Secondly, in Approach 2, we have shown you the procedure of converting formulas to values without using paste special via Notepad in Excel.

📌  Thirdly, in Approach 3, we adopted the Power Query Method to convert formulas to values which is a simple method.

📌  Forthly, in Approach 4, we applied the Mouse Trick which is another very easy method.

📌  Fifthly, in Approach 5, we have used the Copying and Pasting method.

📌  Finally, in Approach 6, we have applied VBA Code which is a very important feature while converting formulas to values without using paste special in Excel.


Conclusion

We wish that you would be able to know- how to convert formula to value without using paste special in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Articles

(Visited 96 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo