Microsoft Office Tutorials and References

In Depth Information

**Transforming data by using temporary formulas**

The values in the range are multiplied by the copied value (1.05), and cells that contain text are

ignored. Formulas in the range are modified accordingly. Assume that the range originally contained

this formula:

=SUM(B18:B22)

After you perform the Paste Special operation, the formula is converted to

=(SUM(B18:B22))*1.05

This technique is limited to the four basic math operations: add, subtract, multiply, and divide.

For more versatility, keep reading to learn how to use formulas to transform values.

Transforming data by using temporary formulas

The previous section describes how to perform simple mathematical transformations on a range of

numeric data. This tip describes the much more versatile method of transforming data (numerical or

text) by using temporary formulas.

Figure 57-2 shows a worksheet with names in column A. These names are in all uppercase letters,

and the goal is to convert them to proper case (only the first letter of each name is uppercase).

Figure 57-2:
The goal is to transform the names in column A to proper case.