Note: also see this chapter from the Lertap manual. It might be somewhat more up to date; if it seems dated, have the browser refresh the page.

 

SPSS is a data analysis package widely used in the social sciences.

 

We hauled out SPSS 17 for the work described in this topic.

 

What we wanted to do was, first of all, copy the Data sheet from the Mente2010.xlsx workbook into an SPSS data table.

 

In days gone by, select, copy, and paste was a guaranteed way to get data from an Excel worksheet into an SPSS data editor table. This worked okay providing there was sufficient capacity in our computer's clipboard, which was not always the case when a large amount of data was involved.

 

These days SPSS has more smarts. It is capable of actually opening an Excel workbook, and grabbing data all by itself, without any need to select, copy, and paste.

 

To get started, look at the top of the Data worksheet in Mente2010.xlsx:

 

 

Notice two things, please. We have the formula bar showing, and we have changed the "referencing style" so that worksheet columns use letters instead of numbers.

 

The formula bar is displaying the contents of cell A1 above, which is row 1, column 1, or, column A, row 1.

 

To see how to quickly toggle the formula bar so that it either shows or does not show, and to see how to change Excel's referencing style so that columns are letters instead of numbers, look here.

 

Having letters for the column "numbers" is handy when using SPSS. Extremely handy.

 

Do you have SPSS warmed up? Good. Do you have the Mente2010.xlsx workbook open in Excel? Could be Bad. You may have to close Mente2010.xlsx if you have in mind getting SPSS to read data from it; you'll know -- try the stuff below without closing Mente2010.xlsx; if SPSS complains, close Mente2101.xlsx and start again.

 

Now, where do the data begin in the Mente2010.xlsx Data worksheet? In cell A3. What's the last cell with data? Cell BD96. We'd like SPSS to import data from cell A3 to cell BD96. In Excel shorthand, this range is denoted as A3:BD96.

 

Ready. In SPSS click on File. Then on Open. Then on Data.

 

Pick out files of type "Excel (*.xls, *.xlsx, *.xlsm). Navigate through your computer's folders until you find Mente2010.xlsx.

 

A box similar to the one below should appear. Into it we have already typed the Range.

 

 

And that should do it. Click on OK, and okay is what you should be, free to SPSS-away the rest of the day.

 

How about some factor analysis activity? Say we got an IStats report for Mente2011.xlsx. We did. It turned out to have item scores in the range B3:AT96. It's an easy job pick up these item scores in SPSS.

 

If desired, the interitem correlation matrix, located in the IStats range B105:AT149, could also be picked up and brought into SPSS. Easy.

 

Now, what's not so nice is having to type all the "variable names" in SPSS. You'll find that SPPS defaults to variable names starting with "V1".

 

Wouldn't it be fine if we could get SPSS to read our Lertap column headers and use them for its variable names?

 

For the Mente2010.xlsx Data worksheet, the column headers start with "ID code", and, in the snippet below, go through "T2".

 

 

SPSS has an option to recognize and use column headers as its "variable names". We tried the following; notice the tick in the check box, and how our range now starts with A2:

 

It didn't work. We've tried this a few times in recent years, and have come to settle for a work-around: we get Excel to make, for example, a copy of the Data worksheet. This is easy to do. Right-click on the Data worksheet's tab at the bottom of the screen. From the menu which drops down, select "Move or Copy ..."

 

 

Be sure to tick the "Create a copy" option. Then, in the copy of the Data sheet which results, delete the first row so that the column headers we want to have SPSS use as its variable names will be at the very top of the worksheet. The data we want to import, including the first row with its column headers, will now be in the range A1:BD95.

 

 

Bob's your uncle, mate!