Using Excel to Analyze Data
Microsoft Excel is a spreadsheet program with excellent
statistical and data analysis capabilities. Most of the data analysis that you
want to do with your research can be done with Excel, but you need to understand
how to format and prepare your information for analysis. Remember, Excel is a
number cruncher. The following formatting tips should be heeded:
Do not put letters or + and – marks in your data. It will
not work. Also, ALL DATA IN A COLUMN MUST BE OF THE SAME TYPE! Do not mix %
with $ or numbers. If you have two scales that you are using to measure the
same thing place only one scale in each column. Apples and oranges will give
you cobbler, not a peach pie. Do not leave blanks in your columns. This will
cause problems. If you have a blank, place a zero there if the answer is zero.
If there is no data for the blank, rearrange your information to omit that row
of data. For example: if student A has 7 points, student B 6, and student C is
n/a, you cannot have a comparison between A, B and C. Only A and B can be
compared. Make your data contiguous. Do not skip lines or leave blanks in the
tables that you create. If you do, then Excel will have a hard time figuring
what data you want analyzed and what type of analysis you want to perform.
Getting your information onto a spreadsheet in the correct format is the most
important step in analysis. Everything else will follow.
There are a number of data analysis tools available in
Excel. Some can be accessed via formulae that you type in, others can be more
conveniently utilized by pressing the fx on the
formula bar. Still others can be obtained via the Sigma ∑ key on the toolbar.
More advanced functions can be found via the Data Analysis tool, which is
accessed by going to the Menu bar and selecting TOOLS – DATA ANALYSIS. You can
also graph your information. The criteria for each are as follows:
Type in: If you know what you are doing, and want to
perform If statement analyses or add non-contiguous data, then this is a
convenient method of entering your formulae.
fx: This key will provide you with a
vast list of functions. When you select one you are provided with a helpful
script that asks you to plug in the information. There is a box for selecting
the category (statistical, engineering, financial, etc.) and once a category is
selected you will see available functions.
∑: This gives you Sum, Avg, Max, Count, Minimum and a link
to other functions. Utilize this key after highlighting the data that you want
analyzed.
Data Analysis: This menu item takes you to a series of
statistical (sadistical?) analysis tools including: T test, F test, Regression
and others. Unlike the other tools, where a simple answer is placed on the
worksheet, the results will be formatted and categorized.
Graphing: A wide variety of graphs are available. Select
your headings and data (all in contiguous cells and columns) and then follow the
instructions.
Sample Data Set Links
T-test sample data set
Using
Microsoft (MS) Excel to Analyze Data
"An Example"
Research
example
