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.
Generally, the value of a particular cell is given as a formula in the Formula Bar. The following approaches will change this form.
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.
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.
- Consequently, you will see that your formulas have converted to values as well.
Finally, using the F9 function key will save you time while converting formulas to values in Excel without using paste special.
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.
- Secondly, press Shift + F10 + V simultaneously.
- Finally, the formulas will convert to values.
Undoubtedly, this is a very simple method of converting formulas to values in Excel.
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.
- 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.
Finally, you will see the formulas have been converted to values in Excel.
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.
- After that, you will find a dialogue box named Create Table.
- Click on the OK.
- After clicking on OK, the Power Query Editor will be activated.
- Then go to the Home tab of the Power Query Editor.
- Select the Close & Load option.
- Finally, click on Close & Load.
- Consequently, you will obtain all the formulas converted to values.
Finally, the Power Query method is a very simple method while converting formulas to values without using paste special in Excel.
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).
- Put the mouse cursor above the outline of the selected cells. You will see an icon of four arrows pointing in four directions.
- 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.
- Consequently, you will get the formulas turned values as well.
Finally, the mouse wriggle trick is very user-friendly if want to know how to convert formulas to values in Excel using paste special.
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- Home ➡ Paste ➡ Paste Values ➡ Values
- Finally, You will see the formulas converted to values as well.
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
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.
- 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
- Now, click on the Run option.
- Finally, you will obtain the formulas converted to values as well.
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.