Categories
Excel

Convert Formulas to Values

Most Excel spreadsheets contain formulas. Sometimes you may want to force only the result of a formula to occupy a cell, instead of leaving the formula in place, where it will change if/when the data it references changes.

To convert a formula to its calculated value in Excel, you can use the following methods:

  1. Paste Special
    Press CTRL+C to copy and press Shift+F10 then press V.
  2. Using Copy Here as Values Only
  3. Create a Macro to Convert Formulas to Values

Use Paste Special to Convert Formulas to Values

You can copy the formula results and still leave the original formulas in place using Excel’s Paste Special tool. Assume you have formulas residing in cells A1:A100:

Paste Special as Values: To copy press CTRL+C and to paste press Shift+F10 then V
  1. Select this range.
  2. Go to Home tab.
  3. Select Copy (CTRL+C).
  4. Then select the starting cell for the mirror results.
  5. Click the down-arrow key of thePaste icon.
  6. Click the Values icon under the Paste Values section or press Shift+F10 then V.

If you want to override the original formulas with their results, skip the step 4.

You also can copy formula results and still leave the original formulas in place by using a pop-up menu that many users don’t even know exists.

  1. Select the formula range.
  2. Right-click the border of the selection (anywhere except the fill handle).
  3. While holding down the right mouse button, drag to the destination (or Ctrl-clicking on a Macintosh).
  4. Release the right mouse button.
  5. Click Copy Here as Values Only from the resulting pop-up shortcut menu.

Create a Macro to Convert Formulas to Values

If you frequently convert cells containing formulas and functions to their values, you can use this simple macro:

Sub ValuesOnly()
Dim rRange As Range
 On Error Resume Next
  Set rRange = Application.InputBox(Prompt:="Select the formulas", _
      Title:="VALUES ONLY", Type:=8)
  If rRange Is Nothing Then Exit Sub
 rRange = rRange.Value
End Sub
  1. To create this macro, press Alt+F11 (or Options+F11) to open Visual Basic Editor (VBE).
  2. While in the VBE, select Insert » Module to insert a standard module (if there is not already exist).
  3. Enter the preceding code directly into the module.
  4. Click the window’s Close button to get back to Excel.
  5. Press Alt+F8 (or Option+F8) to open the Macro dialog box.
  6. Select ValuesOnly, and then click Run.

When you use the macro, you will be presented with an InputBox and asked to select a range that contains your formulas. The selected range address will show automatically in the InputBox, and all you need to do to make the conversion is click OK.

Formula and Functions

  1. Extract Number From Cells That Mix of Text and Numbers
  2. Convert Text to Numbers
  3. Using Code and Char Function
  4. Creating Dependent Combo Box Using Option Buttons
  5. Convert Formulas to Values