How to Convert Formula to Value for Multiple Cells in Excel

Generating data using various formulas is quite common in Microsoft Excel. In case of data shift to another datasheet, it gets inconvenient as the formula gets on the clipboard. The same formula might not be applicable in another datasheet. On the other hand, manually typing the values in a new sheet is a hectic process. If the source data can be converted to a value in the old or new sheet, those can be avoided. So, you can convert formula to value for multiple cells in Excel. This article will guide you through seven methods as solutions. All the methods have elaborate instructions. You can get access to them graphically as well.

📁 Download Excel File

Download the Excel file below.

Learn to Convert Formula to Value for Multiple Cells in Excel with 7 Approaches

The article is going to introduce six methods to convert formula to value in Excel for multiple cells. Here, the methods will provide simple to complex solutions gradually. The simple solution involves Keyboard Shortcut, Paste Values, Paste Special, and Drag-Drop Wriggle. On the other hand, complex ones will be covering Function keys, the Power Query Tool, and the VBA method. In brief, users will get this versatile walkthrough of these solutions to one of the fundamental problems.

This method is most commonly used to convert formula to value for multiple cells in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells or column(s) or row(s) and copy them to the clipboard using Ctrl+C.

• To complete the conversion, press Shift+F10+V on the same cell. It will show the values as numerical values, instead of formulas on the Formula Bar.

The Home tab has many Paste options from its Context Menu. Copy-paste using it is a classic and easy method. It works on multiple cells accurately.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells or column(s) or row(s) and copy them to clipboard using Ctrl+C.
• Under the Home tab, select the drop-down arrow below the Paste option.
• Choose Values Only option from the menu.

• Excel will Paste values on the selected region. Pressing Enter or Esc will unselect the section and Formula Bar will show the values of respective cells.

This method is for distinct selection on pasting data. Using keyboard shortcuts and a few clicks can complete the action to convert formula to value eventually.

⬇️⬇️ STEPS ⬇️⬇️

• Select the cells or column(s) or row(s).
• Now, copy them to the clipboard using Ctrl+C.
• Select the cell(s) where you want to paste the conversion.
• Press Ctrl+Alt+V to get the Paste Special option.
• Next, choose Values from the Paste category and None from the Operation.
• Proceed with clicking OK.

• The selected region will show the values instead of formulas through the cell and Formula Bar.

📕 Read More: How to Convert Formula to Value in Excel Without Paste Special

You can use this method most efficiently as it doesn’t need any keyboard shortcut or ribbon tool. Mouse clicks and drags can make the job done in seconds.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the range of cells you want to convert.
• Secondly, move your cursor to any edge of the entire selection to get a four-directional arrow icon over your pointer.
• Thirdly, move the cursor randomly away from the selection while holding down the right click of your mouse.
• Then, move the cursor back into the selection. Release the right click on your mouse.

• Releasing the right click will get you the options as it is shown in the picture below.
• Choose Copy Here as Values Only option.

• Lastly, the conversion is complete and you will get your required cells with values only.

The fastest, yet strenuous method is using two function keys only to convert formula to value for multiple cells in Excel. This method needs to be made individually for every cell you want the conversion on. However, in the case of multiple cells, this method is the easiest for multiple non-adjacent cells.

  ⬇️⬇️ STEPS ⬇️⬇️

• Initially, select a cell that you want to convert. Press Function key F2. The cell will go to edit mode.

• Now press another Function key F9, while in edit mode. It will immediately convert your formula to value.

• Repeat the process for another non-adjacent cell(s) to get the value conversion.

If you need to convert your formula to value for multiple cells in another column or worksheet from the source column or worksheet, Power Query Tool is the method for you. It can perfectly present your data into a table and shows it to you in any form you want. Therefore, this article will instruct you using the example for better understanding.

  ⬇️⬇️ STEPS ⬇️⬇️

• First, If you want to convert the formula of E6 which is under the formula “C6*D6” to its value, you should select the range of data first.
• Now, from the Data tab, select From Table/Range, shown as step 3 in the image.
• After that, from the pop-up window Create Table, choose the range. As it has chosen initially, you can proceed to OK.

• Now, Excel will open a Table window as Power Query Editor.
• Here, you can see the column being converted to values already.
• Next, select the drop-down option from Close & Load, from the Home tab.
• Choose Close & Load to to channel the location where the conversion will be shown.

• Another dialogue box Import Data pops up.
• Next, choose Existing Worksheet if you want the conversion on the same worksheet. You can choose a new worksheet for it as well, selecting the latter option as an image.
• Here, you can also select the range of cells where you want the values.

• Proceeding with the steps discussed so far, a new column has been pasted on Column F, with the previous header “Price”.
• As shown in the image, you can place both before and after conversion side by side.

Apart from manual methods, Visual Basic Applications (VBA) enable various sets of functionality in Excel. To convert a formula to value in case of multiple cells, you can write a code that will be embedded into the excel and you can put it to use for any set of ranges, whenever required.

  ⬇️⬇️ STEPS ⬇️⬇️

• Initially, open Microsoft Visual Basic for Applications (VBA) by pressing Alt+F11.
• From the window, choose Module, under the Insert tab to create a new module. You can use the shortcut Alt+I+M to create a new module.

• Here, this is a VBA code that is necessary for the conversion. You should copy and paste it accurately into your module window.

Sub Formulas_into_Values()
Dim mRng, Cell As Range
On Error Resume Next
Set mRng = Application.InputBox( _
Prompt:="Please Select", Type:=8)
For Each Cell In mRng
If Cell.HasFormula Then
Cell.Formula = Cell.Value
End If
Next Cell
End Sub

• Keep the cursor on the code and press F5 or the Play icon like the image below to run it.

• To run it, you can choose directly from the Excel window as well. Choose Macros under the Developer. You should get a Macro window that will ask you if you want to run the aforementioned VBA code. Choose Run and proceed to the next step.

• Now, you should get a dialogue box to confirm the range of data to apply the macro on. Select the range and continue with OK.

• Therefore, you will get the conversion successfully.

📝 Takeaways from This Article

The article allows the readers to understand the varieties of options available to convert formula to value for multiple cells.

📌  Using keyboard shortcut is a common practice. It starts with copying with Ctrl+C and converting with Shift+Alt+V.

📌  From the Paste Context Menu, Values Only allows the conversion eventually. It is applicable for multiple cells.

📌  Paste Special Box by Ctrl+Alt+V enables to choose exact option and paste the conversion successfully.

📌  Wriggle is an interesting approach to convert formula to values. You need to utilize few clicks by mouse pointer only.

📌  F2 key enables the edit mode, Allowing F9 key to swipe in and convert formula to value. Although the method is not applicable for multiple adjacent cells, it is the fastest possible for non-adjacent ones.

📌  Power Query Tool is the most advanced and effective method. Besides, it can not only convert formulas to values, but also place them as the user wishes.

Conclusion

To convert formula to value for multiple cells in Excel, keyboard shortcut by Shift+Ctrl+V, Paste Context Menu, Paste Special by Ctrl+Alt+V, Four directional pointer to wriggle, F2 – F9, Power Query Tool and VBA tool can be used as per the article. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.

Related Articles

• How to Stop Formula to Convert into Value Automatically in Excel