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.

Figure 2-23. Pop-up shortcut menu

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.