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.
- Enter the following formula in cell D2:
- 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.
- Select the formula cells. In this case, D2:D11.
- Choose Edit - Copy
- Select the first cell in the original data column (in this case, cell A2).
- Choose Edit - Paste Special. This displays the Paste Special dialog box.
- 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.
- 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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks