Excel Hacks Ebook [Electronic resources]

Hack 36 Convert Excel Formulas and Functions 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.

You can do this manually in a couple of ways, or you can use a macro that will make the job a breeze. Let's look at the manual methods first.

Using Paste Special

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. Select this range, select Copy, and then select the starting cell for the mirror results. Select Edit Paste Special Values and click OK.

If you want to override the original formulas with their results, select the formula range and select Copy. With the formula range still selected, select Edit Paste Special Values and then click OK.

Using Copy Here as Values Only

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.

Select the formula range and right-click the right or left border of the selection (in other words, anywhere in the selection except for the fill handle). While holding down the right mouse button (or Ctrl-clicking on a Macintosh), drag to the destination, release the right mouse button, and click "Copy Here as Values Only" from the resulting pop-up shortcut menu.

You also can override the original formulas with their results. Select the formula range, then right-click the right or left border of the selection (again, anywhere in the selection except for the fill handle). While pressing the right mouse button (or Ctrl-clicking), drag over one column to the right or left and then back to the starting range, release the right mouse button, and click "Copy Here as Values Only" from the resulting pop-up shortcut, shown in Figure 2-23.

Using a Macro

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

To use this macro, select Tools Macro Visual Basic Editor (Alt/Option-F11). While in the VBE, select Insert Module to insert a standard module. Enter the preceding code directly into the module. Click the window's Close button, or press Alt/-Q to get back to Excel. Select Tools Macro Macros (Alt/Option-F8), select ValuesOnly, and then click Options to assign a shortcut key to the macro. 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.