Transforming Data With Formulas

This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data.

The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case. This will be done by creating formulas that use Excel's PROPER function.

The steps below are specific to this example. But they can easily be adapted to other types of data transformations.

Creating the formulas

In this case, the formulas will go in column D. As you'll see, this is just a temporary location. The formula results will eventually replace the names in column A.

1. Enter the following formula in cell D2:

=PROPER(A2)

2. Copy the formula down the column to accommodate the data. In this case, the formula is copied down to cell D11. The worksheet now looks like this (the formula cells are selected, so they appear highlighted).

Copying and pasting the formula cells

In this step, the formula cells are copied, and pasted as values -- overwriting the original data in column A.

1. Select the formula cells. In this case, D2:D11.
2. Choose Edit - Copy
3. Select the first cell in the original data column (in this case, cell A2).
4. Choose Edit - Paste Special. This displays the Paste Special dialog box.
5. In the Paste Special dialog box, click the Value option button. This step is critical. It pastes the results of the formulas -- not the formulas.
6. Click OK.

At this point, the worksheet looks like this:

Deleting the temporary formulas

The formulas in column D are no longer necessary, so you can delete them.