Microsoft Office Tutorials and References

In Depth Information

**Transforming Data without Using Formulas**

Transforming Data 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 5 percent. Or, you might need to divide each

value by 2. This tip describes how to perform addition, subtraction, multiplication, and division on

a range of values without using any formulas.

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

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

5 percent:

1.
Activate any empty cell and enter
1.05
.

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

2.
Press Ctrl+C to copy that cell.

3.
Select the range to be transformed.

It can include values, formulas, or text.

4.
Choose Home

Clipboard

Paste

Paste Special to display the Paste Special dialog box.

➜

➜

➜

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

6.
Click OK.

7.
Press Esc to cancel Copy mode.

The values in the range are multiplied by the copied value (1.05). 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: addition, subtraction, multiplication,

and division.

For more versatility, see Tip 77, which describes how to use formulas to transform values.