Microsoft Office Tutorials and References

In Depth Information

**Transforming data by using temporary formulas**

Follow these steps to transform the data in column A:

1.
Create a temporary formula in an unused column.

For this example, enter this formula in cell C2:

=PROPER(A2)

2.
Copy the formula down the column to accommodate all cells to be transformed.

3.
Select the formula cells (in column C).

4.
Press Ctrl+C.

5.
Select the original data cells (in column A).

6.
Choose Home
➜
Clipboard
➜
Paste
➜
Paste Values (V).

The original data is replaced with the transformed data (see Figure 57-3).

7.
Press Esc to cancel Copy mode.

8.
When you’re satisfied that the transformation happened as you intended, you can delete the

temporary formulas in column C.

Figure 57-3:
The formula results from column C replace the original data in column A.

You can adapt this technique for just about any type of data transformation you need. The key, of

course, is constructing the proper transformation formula in Step 1.