The Recode macro described in the preceding topic is not really a recoder. It doesn't alter the contents of the original Data column; instead it copies the indicated Data column, and then lets you create a new column with values, or codes, based on those found in the original column. This is much more along the lines of creating a "new variable", or of "transforming" an original variable to a new one (to use terms which may be familiar to SPSS users).

 

Excel 2007 has Find & Select options under the Editing section of the Home tab (look way to the right-hand side of the Home tab).

 

 

Click on Replace, and the following dialog box will pop up.

 

 

The Options>> button is what data recoders will want to use:

 

 

To give an example of using Excel to recode a column, let's say that we wanted to change every occurrence of 'F' in a given column to '1'.

 

To do so, we'd select the column, and then fix up the dialog box so that it looks something like this:

 

 

If we dared to click on Replace All, Excel would dutifully find and replace all Fs with 1s in the selected column. We could have selected more than one column -- this is a quick and effective way to truly recode values over a number of columns.

 

It is very possible to get Excel to do more. We might have a column with numeric values, such as "number of years of test experience", and wish to create a new column with a coded experience letter, such as "H" for high, "M" for medium, "L" for low, and "N" for none.

 

Take a deep breath and look below at the =IF statement seen in the Formula Bar:

 

 

The =IF formula does the recoding for us. It says that, if YrsTest (column AM) has a value greater than 10, then column AN is to have an "H", otherwise, if column AM's value is greater than 5, then column AN is to show "M", otherwise, if column AM's value is over 0 (zero), show "L", else show "N".

 

(Note that we used Lertap's Excel shortcuts option to "Change the referencing style" so that the column headings are letters, not numbers -- this can often make writing Excel formulas easier.)

 

This looks very, very IFfy, you say? Well, among all the nice things we might say about Excel, one is that there are lots of resources to turn to when help is needed. You can try Excel's Help -- look up 'create conditional formulas' for some very helpful hints and examples. Or, try the internet. Or the local bookstore (perhaps there's now Recoding Excel Columns for Dummies!). Or even try us at: lertap5@gmail.com.