Converting Formulas to Values

If you have a range of formulas that always produce the same result (that is, dead formulas), you may want to convert them to values. You can use the Home Clipboard Paste Paste Values command to do this.

Suppose that range A1:A10 contains formulas that calculate a result that never changes. To convert these formulas to values:

1. Select A1:A10.

2. Choose Home Clipboard Copy (or press Ctrl+C).

3. Choose Home Clipboard Paste Paste Values.

4. Press Enter or Esc to cancel paste mode.

You can also take advantage of a Smart Tag. In Step 3 in the preceding list, press Ctrl+V to paste. A Smart Tag will appear at the lower-right corner of the range. Click the Smart Tag and choose Values Only (see Figure 2-5).

Figure 2-5: A Smart Tag appears after pasting data.

This technique is very useful when you use formulas as a means to convert cells. For example, assume you have a list of names (in uppercase) in column A. You want to convert these names to proper case. In order to do so, you need to create formulas in a separate column; then convert the formulas to values and replace the original values in column A. The following steps illustrate how to do this.

1. Insert a new column after column A.

2. Insert the following formula into cell B1:

    =PROPER(A1) 

3. Copy the formula down column B, to accommodate the number of entries in column A. Column B then displays the values in column A, but in proper case.

4. Select all the names in column B.

5. Choose Home Clipboard Copy.

6. Select cell A1.

7. Choose Home Clipboard Paste Paste Values.

   When to Use AutoFill Rather Than Formulas

   Excel's AutoFill feature provides a quick way to copy a cell to adjacent cells. AutoFill also has some other uses that may even substitute for formulas in some cases. I'm surprised to find that many experienced Excel users don't take advantage of the AutoFill feature, which can save a lot of time.

   For example, if you need a list of values from 1 to 100 to appear in A1:A100, you can do it with formulas. You enter 1 in cell A1, enter the formula =A1+1 into cell A2, and then copy the formula to the 98 cells below.

   You also can use AutoFill to create the series for you without using a formula. To do so, enter 1 into cell A1 and 2 into cell A2. Select A1:A2 and drag the fill handle down to cell A100. (The fill handle is the small square at the lower-right corner of the active cell.) When you use AutoFill in this manner, Excel analyzes the selected cells and uses this information to complete the series. If cell A1 contains 1 and cell A2 contains 3, Excel recognizes this pattern and fills in 5, 7, 9, and so on. This also works with decreasing series (10, 9, 8, and so on) and dates. If there is no discernible pattern in the selected cells, Excel performs a linear regression and fills in values on the calculated trend line.

   Excel also recognizes common series names such as months and days of the week. If you enter Monday into a cell and then drag its fill handle, Excel fills in the successive days of the week. You also can create custom AutoFill lists using the Custom Lists panel of the Options dialog box. Finally, if you drag the fill handle with the right mouse button, Excel displays a shortcut menu to enable you to select an AutoFill option.

8. Press Enter or Esc to cancel paste mode.

9. Delete column B.