Microsoft Office Tutorials and References

In Depth Information

**Tip 57: Transforming Data with and Without Using Formulas**

Transforming Data with and Without

Using Formulas

Often, you have a range of cells containing data that must be transformed in some way. For example,

you might want to increase all values by five percent. Or you might need to divide each value by two.

This tip describes two ways to perform these types of transformations.

Transforming data without formulas

The following steps assume that you have values in a range and you want to increase all values by five

percent. For example, the range can contain a price list and you’re raising all prices by five percent:

1.
Activate any empty cell and enter
1.05
.

You will multiply the values by this number, which results in an increase of five percent.

2.
Press Ctrl+C to copy that cell.

3.
Select the range to be transformed.

The range can include values, formulas, or text.

4.
Choose Home➜Clipboard➜Paste➜Paste Special to display the Paste Special dialog box (see

Figure 57-1).

5.
In the Paste Special dialog box, click the Multiply option.

6.
Click OK.

7.
Press Esc to cancel Copy mode.

Figure 57-1:
Using the Paste Special dialog box to multiply a range by a value.