We are now going to learn how to create a frequency table, in APA style, using
Microsoft Word and Excel. These same steps will also work if you're using
alternative word processing or spreadsheet software. Typically you will
use Microsoft Word for writing your research paper,
and although word does have tools for building tables, the secret to creating a
proper table in Microsoft Word - is to use Microsoft Excel. Here is our workflow. We
will calculate basic frequencies in SPSS. We will export those frequencies in a
Word format, but immediately copy them into Excel, where we will create and
format the frequency table. Then we will bring the finished table back into our
research paper in Word. I am using a fictitious data set "myclass.sav".
These are scores on a math quiz for 112 students. I will be focusing on the
variable "Quiz 4". The possible range of scores is 0 to 10 points. Our frequency
table will include simple frequency, relative frequency, cumulative frequency,
percentile, and sample size which will go in the title. Go to analyze > descriptive
statistics > frequencies. You will notice that this option, display frequency
tables, is checked by default. This is what we want. If you uncheck display
frequency tables, a dialog box will pop up warning you that you have just turned
off all output, so nothing will be calculated. If that happens, just click OK
and recheck display frequency tables. Move Quiz 4
(or Quiz 4 score) into the variables box. Click on "format". Under order, choose
"descending values". This will make our job easier later, because a proper frequency
table organizes the frequencies in descending order. Now click continue. We
have just set our frequency tables to appear in descending order. However - and
this is important - if any options are checked inside of charts, such as a
histogram, then your frequency table will always be in ascending order. . .
not what we want. If that happens, fix it by clicking reset and starting fresh
using just the frequency table. But we have our example set up perfectly, so
click OK. In the output window, we see the results. We have four columns including
frequency. The sample size is down here, 112. Because we have no missing cases, the
percent and valid percent columns are exactly the same. Looks like everything
that we need for our finished table right? Not so fast!
Actually there is only one usable column of data here. Let's look at those column
headings. We want simple frequency for our table, so we need this first column
labeled frequency. We also want relative frequency, cumulative frequency, and
percentile, but these columns are percent, valid percent, and cumulative percent.
This is cumulative percent, not cumulative frequency. The only data that
are useful to us are the descending list of scores and simple frequency, so only
the first column will be useful to us. Also - and this is very important - not only
are these "not the data you are looking for" this table is not in APA style. You
cannot submit a table like this for publication, and you cannot submit it in
your homework either. This table is raw output. It's up to you to serve it up in a
proper APA formatted table. There are a few other things wrong with this table.
Now look at this list of quiz scores. Yes, they are in descending order, but what
else do you see? Several missing categories. We are missing a 4 and a 1. So
we have some work to do, but we can't do any of it until we get these numbers out
of SPSS and into Excel. However, before we get to Excel, we need to export this
output to word. Click on this icon "export". We will save this file to the desktop
and call it "Quiz4". This will give us an .rtf file that will allow us to cut and
paste much more easily than exporting directly to excel. I am going to open
this .rtf file in Microsoft Word, and then save it as a Word document. I recommend
that you do this whenever you run an analysis in SPSS. Export your findings to
a Word document so that you can easily get to them, even if you do not have SPSS
loaded on the computer that you happen to be using later.
Open the "Quiz4" word document,
and then open a new Excel workbook. We are going to cut and paste from the
first two columns in that table that we exported. . .
. . . and move them into the Excel workbook.
When I create tables, I like to give myself a little margin all around, so I'm
going to paste these data into cell C3. Now we have extra columns and rows all
around the table in case we need them. Because I only copied the columns that I
needed, I will not need to delete the rest of the table. So now I can add
column headings. Across the top row type "Score" and "f" for simple frequency. Simple
frequency is the number of times that a score occurs, a count of the occurrences
of every score in the data. The frequency table displays a column of data, x scores,
and the number of times that that data point occurs. Scores are arranged in
descending order of magnitude. And here's a formatting note: use a lowercase f for
frequency, or if you want this cool script f, type option + f on Mac or alt + f
on PC. Back to our column headings, we need relative frequency, cumulative
frequency, and percentile. Next, let's deal with these missing categories. We need a
row for the score of 4, and we need a row for the score of 1. If you need more rows,
highlight an entire row where you want the new row to go, right-click (or
control-click) on the table and choose insert from the drop down menu. We also
need a row for the score of 1. The row is inserted above the row that you select.
This same procedure works to insert columns if you need to, and you can
delete unnecessary columns and rows this same way.
Now that we have the rows for our missing categories, we need to add their
frequencies. The fact that they were missing in SPSS means that they both had
a frequency of zero. So now we have to calculate relative frequency, cumulative
frequency, and percentiles, and here is where Excel really shines, because we can
use the formulas to do the math for us. For instance, relative frequency is the
proportion of times that a score occurs. Relative frequency is simple frequency
divided by sample size. In cell E3, type the formula = D3/112.
Highlight the cell and double click to copy it down the rest of the column.
Cumulative frequency is the frequency of all the scores at and below a particular
score. The cumulative frequency of a score is the frequency of that score
plus the cumulative frequency of the score below it. To create this column, we
need to start at the bottom type a 2 for the first cumulative frequency. Above
that, in cell F12, type = F13 + D12. Now drag that formula up the
column. The top cumulative frequency will be the same as the sample size, 112. And
finally, percentile is the percent of all of the scores in the data that are at or
below a certain score. It is the cumulative frequency divided by n times
100. Start with the numbers in the cumulative frequency column. Transform
each cumulative frequency value to a percentile by dividing the cumulative
frequency by n = 112, then multiplying by 100. In cell G3, type
= (F3/112) x 100.
Double click to copy that formula down, and complete the column. Now that we have
all of our data in place, we can do our APA formatting in Excel. First, let's
clear any existing formatting and change the font. Select the entire table and
from the borders tool, select "no border". To clear the shading, select the fill
color tool, and choose "no fill". Finally, the font for this table will be Times
New Roman 12-point. Good. Now we need to add rules or horizontal black lines
above and below the table. Highlight the top row of data along our five columns.
Using the borders tool we will add a top rule and a bottom rule. Excel calls them
borders. We will also add a bottom rule at the very bottom of the table. Rather
than using the column spacing that Excel gives us, let's select the entire range
of columns and adjust them slightly they can be a little narrower. I like to keep
all of the columns the same width when I can. Next, we should center the titles in
the header, and we should center the scores in the first row. Right-justify
all scores in the other cells, this will help the decimal points line up
correctly. And speaking of decimals, we can adjust the display of decimals with
these icons to add or to remove decimal points as
needed. Select these three columns and use the decreased decimal tool to make
them all two decimal places. Of course, cumulative frequency does not
need decimals so we can select only that column, and set the decimals to zero. Okay.
Now we are done with the APA formatting. Time to get this table into Microsoft
Word and finish it. Select the entire table, copy, and now flip back to your
word research paper document. Paste the formatted table here. Now we need to
title the table. Each table must have a number and a title. The numbers run
sequentially. Your first table is Table one, so we will call this table one. Your
second is Table two and so on. On the next line, add the title. Type frequencies
of scores for quiz four. This is a good place to add the sample size as well.
Type , (N = 112). Titles are
in title case and italicized. And there is our properly formatted APA style
frequency table!