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.