Roger Bakeman Date: April 10, 2021 Re: How to Transfer a

Memo

To: Colleagues

From: Roger Bakeman

Date: April 10, 2021

Re: How to Transfer a Correlation Table from SPSS to Microsoft Word

Problem. This memo shows how to create correlation tables in a Microsoft Word document

beginning with SPSS output in a way that is easy, accurate, and relatively free of error.

Step 1 (SPSS). Select Analyze > Correlate > Bivariate. In the Bivariate Correlations dialogue box,

uncheck Flag significant correlations (unintuitively, the syntax is /PRINT SIG; if you leave the box

checked, the syntax is /PRINT NOSIG). With the box unchecked, correlations are displayed

without asterisks. This means that when you copy a correlation table all variables will be

numeric; if any had asterisks, they would be copied as string variables. Additionally, I would

recommend checking Show only the lower triangle and Show diagonal.

Step 2 (SPSS). After selecting variables and OK (or running from syntax), select and Copy (right

click) the correlation table displayed in the SPSS output.

Step 3 (Excel). Paste (right click) into an Excel spreadsheet. Be sure to use Paste (P) and not

Match Destination Formatting (M). Next (table still selected after paste), on the Home menu,

Alignment tab, select Wrap Text and on the Merge & Center dropdown box select Unmerge

Cells. With Paste (P), numbers are copied with their full precision; with Match Destination

Formatting (M), they will be truncated to the three digits after the decimal point displayed,

which can cause double-round errors as discussed in Step 5.

Step 4 (Excel). The first row of the correlation table, beginning in Column 3, consists of the

variable names; Columns 1 and 2 are blank. Enter a word (like “stat”) in the second column of

the first row, thus providing it a label. The remaining rows consist of groups of three rows for

each variable. The first column in the first row of each group contains the variable name. The

second column contains labels for the three rows: Pearson Correlation, Sig. (2-tailed), and N.

Select the table, including the first row of labels. Then, on the Data menu, Sort and Filter tab,

select Sort. In the Sort dialogue box make sure My data has headers is checked. Then select

“stat” under Column on the Sort by drop-down box and OK. This brings all the rows containing

Ns, correlations, and p values together.

Step 5 (Excel). Select the correlations in the table, then Format Cells... (right click). In the

Format Cells dialog box, select Category: Custom. In the box under Type that contains the

default “General” replace “General” with “#.00” (a custom format). This formats all

correlations with two digits after the decimal point and without the leading zero, per APA

guidelines. In particular, this procedure will round correlation coefficients to two digits,

avoiding the double-round error.

How to Transfer a Correlation Table from SPSS to Microsoft Word

Likewise, select the p values in the table, then Format Cells... (right click). In the Format Cells

dialog box, select Category: Custom. In the box under Type that contains the default “General”

replace “General” with “#.000” (a custom format). This displays all p values with three digits

after the decimal point and without the leading zero.

What is the double-round error? SPSS rigidly—and in my view wrongly—prints correlation

coefficients with three digits after the decimal point and with a leading zero. There appears to

be no option to change this. The unalert among us then round to two digits from the three-

digit display. This can be incorrect. If the correlation coefficient is .03446..., for example,

rounded to three digits it is .035, but rounded to two digits it is .03. Rounding the three-digit

display to .04 is a double-round error; it should be .03.

Step 6 (MS Word). Now, select the correlation table in Excel, Copy (right click), and Paste (right

click) into a MS Word document. It will paste as a MS Word table. You can then edit the MS

Word table as appropriate.

Options. In Excel, I find it convenient to use conditional formatting to note weak, medium, and

strong correlations with yellow, green, and red and significance levels less than .01 with red and

less than .05 with green.

Also, in SPSS output minus signs are rendered as hyphens and em dashes as double hyphens

(on the diagonal). They should be changed to actual minus signs and actual em dashes (2212

and 2014 in Unicode hex). In MS Word, on the Insert menu, Symbols tab, select Symbol, then

More Symbols, and then enter the hex code in the Charter code box with the from: dropdown

box showing from Unicode (hex). (Shortcut key for em dash is Ctl+Alt+hyphen on the numeric

keyboard; shortcut key for minus sign is Alt+M in my APA paper and memo templates).