Basic Data Analysis
Using MS Excel
Teresa McCoy
Assistant Director, University of Maryland Extension
Evaluation & Assessment
First Things First: Toolpak
Click
on
“File”
then
on
“Op1ons”
Click
on
“Add
Ins”
then
on
“Manage
…
Go”
Click on the box, “Analysis Toolpak,” and
Then click on “OK”
You will see the “Data Analysis” option
appear on the right-hand side of your
ribbon.
Overview
I. Data Management
II.Data Manipulation
III.Data Analysis
IV.Data Communication
Preface
Relax and just listen
Beginner pace
Exposure to the Excel platform
Exposure to T-Tests
Tips & Tricks
Support Materials
Data Manipulation
1)Sort
2)Filter
Tip: Auto- or Flash-fill
Select the data in my
Sort spreadsheet.
Go to “Data” tab.
Select “Sort.”
I’m going to sort by Column B
(county) in alphabetical order.
Sorted
by
Column
A
alphabe1cally
Sorted
by
Column
B
alphabe1cally
and
then
by
Colum
C
smallest
to
largest
Sort &
Filter
Auto or Flash Fill:
Can fill in the same value or formula or a sequence
Grab the handle at the bottom
right corner. Hold and drag
There
is
also
a
“Fill”
op/on
on
the
Home
Menu
Ribbon
where
you
can
set
up
mul/ple
fill
op/ons.
III. Data Analysis
Caveat
Multiple ways to go about your analysis.
1) Toolpak
2) Formulas
3) Manual entry
Descriptive Stats Using Toolpak
N=149
Example: Typing in a Formula
Using Formulas from the Formula
Tab
P-‐Value
shows
as
.36
III. Data Analysis
Review Main Points
• T-test looks for significant difference
between two sets of data.
• Eliminates our bias.
• Science wants a 95% confidence level.
• T-test returns the p-value (probability).
• Want a p-value that is <.05
• Because that says the difference in the data
is NOT attributable to random chance.
Review Main Points
If the results are not
attributable to chance,
Then our program intervention
worked.
Question
Is there a significant difference between
girls and boys in the number of hours spent
in 4-H activities each week?
Open-ended; therefore a two-tailed t-test
Question
Do boys spend more time each week in 4-H
activities than girls?
Closed-ended; one direction; therefore a
one-tailed t-test
Paired or Unpaired Data
Paired
Paired or Unpaired Data
Unpaired
Let’s go back to a question
Is there a significant difference between
girls and boys in the number of hours spent
in 4-H activities each week?
Open-ended; therefore a two-tailed t-test
Two-tailed T-Test on Unpaired Data
Using Formula Bar
T-Test Result of p=.36
Can we be confident that there is a significant
difference between girls and boys in the number of
hours spent in 4-H activities each week?
No
Two-tailed T-Test on Unpaired Data
Using Toolpak
Remember,
• Look back at the video.
• Don’t be intimidated.
• Ask for help.
I. Data Management
Data Entry Quality Tool = Data Validation
Data Validation
This
is
the
area
1) Select the cells that you on
the
sheet
for
want to include in the data data
entry.
validation.
2) Choose an empty space in These
are
the
your spreadsheet and type only
entries
I
in the answers you will will
allow
in
accept. the
cells.
3) Click on your data
validation option.
Data Validation
4) The data validation dialog
box will open.
5) In “Allow,” choose the
drop-down box and
choose “List.”
6) You will then have to tell
Excel what list to use.
7) Go back to the area of the
sheet where you typed in
the allowable entries and
drag across those boxes.
Data Validation
• Users can only choose
what you have listed.
• This eliminates user
error in data entry.
