Transcript Using Excel for Data Analysis

W531 Excel Tutorial
To begin, please visit
the Indiana
Department of
Education website at
www.doe.state.in.us.
From the menu on
the left, click “K-12
School Data.”
Next, select the link that says,
“Data for one School or one
School Corporation in Indiana.”
Use the menu to search for
your particular school.
View AYP
Once you find your school, you
will see the “snapshot.” Spend
some time clicking around on the
links to familiarize yourself with
its contents.
For elementary and middle
school, please look at the
enrollment and attendance rate.
For high school, please look at
the graduation rate.
Click on the red arrows to explore
these topics further.
View Attendance
By Gender,
Ethnicity
When you click the AYP link, you
will see a Summary Report. From
this page, you can view if the
school made the AYP history for
the year.
To learn more about AYP and
what factors determine whether
a school has met this
requirement, please CLICK HERE.
By clicking on the
Attendance rates, users can
view the breakdown by
year and compare the
results to state totals.
Delving Deeper into the
data allows the user to
breakdown the ISTEP
scores further. This is where
you can search by year,
grade level, gender,
ethnicity, socioeconomics,
and more.
When you input
your search
requirements, you
will see data
arranged like this.
Clicking on the
graduate rates link
for high school will
take the user to this
type of data.
Now that you’ve
had a chance to
explore the Indiana
Department of
Education website’s
K-12 School Data
Snapshots, it’s your
turn to input your
school’s data into a
spreadsheet you
create.
To get started, type the title
of your school in a cell. You
will notice it will take up
more than 1 cell.
Use the “merge and center”
tool to combine the cells of
your title together.
To adjust the width or length
of a column or cell, simply
RIGHT-CLICK on the letter
or number, and click
“Column Width…” or “Row
Height…”
For a shortcut, put your
cursor on the edge of the
column or row and gently
slide your mouse to the
desired position.
Another shortcut is to click
FORMAT from the menu
circled above.
Alignment and text placement in cell position.
Add your data to the spreadsheet by typing
into the cells. You can add words, numerals,
or formulas.
Add borders to your cells so your data is easy
to view and print.
You can also change the position within the
cell and alignment of your cells by using the
left-align, center, or right-align tools in the
Home menu.
Go on to the next slide to see the specific
data requirements for your school.
Using your school’s “Data Snapshot” from the DOE website, find the following
information and add it to your spreadsheet: Avg ISTEP Pct score, State Avg, and
AYP.
The Avg ISTEP Pct score and State Avg is found on the main page of the
snapshot in the bar graph. If you put your cursor over the graph, you will see the
percentage values.
Once your data is imported, use the “Cell Styles” to format your cells.
Continue to format
your cells by adding
percentage values.
Simply click “FORMAT”
from the HOME menu,
then find the
Percentage. Change
the decimal place to 1,
and click OK.
TIP: When you change
your number to
percentage, you may
have to retype it in
correctly.
To insert a column into a table that’s already been
formatted, simply RIGHT-CLICK on the column
letter at the top (shaded in blue) and click INSERT
from the menu. This will move your cells over, and
add a column.
Move your AYP data to the new column you just
created (C), and add data for your school’s
attendance rate (for elementary and middle school)
or graduation rate (for high school) in column D.
So others can easily view your school district “Snapshot,” please hyperlink the title of
your school to the IDOE website.
Once hyperlinked, you may need to resize your title.
Data Input
Now it’s time to input more data into your spreadsheet. From the
DOE snapshot of your school, click on the “DELVE DEEPER” link,
and search for ISTEP scores by GENDER, ETHNICITY.
Find the following percentage values for one grade level:
•OVERALL % passing E/La and Math by school, corporation, and
state.
•E/La and Math % passing scores by gender
•E/La and Math % passing scores by ethnicity
•E/La and Math % passing scores by F/R vs. paid lunch
Go on to the next slide to see how you might organize this
information into a spreadsheet.
Decide how you want to
organize your data, then
insert your data from the
IDOE website.
TIP: Format your cells to
percentage values
BEFORE you begin to
insert your data.
For the bulk of your spreadsheet, you’ll be inputting the
data shown above for YOUR SCHOOL DISTRICT. Please
select ONE grade you want to focus on for ISTEP scores in
your school.
Use color, formatting,
gridlines/borders,
merging, and cell
alignment to assist you in
making your spreadsheet
user-friendly.
You can rotate the direction
of your text by adjusting
the “orientation” in the
alignment.
To insert a graphic to your spreadsheet, click on the INSERT
tab from the top. Click CLIPART, then search. Click on your
selection to import it .
To resize it, click on the image. Resize it from the corners to
keep the image in proportion.
Once your data has been inserted,
now your ready to make a graph.
For this assignment, you are going
to make a LINE graph to show the
trend in passing scores by ethnicity.
Highlight the cells in the ethnicity
category from your data. In this
case, it would be cells A14 to G20.
Once highlighted, click INSERT
from the menu at the top, and
select LINE. Select the 2-D line
graph with markers (fourth option).
Your chart will appear on the
spreadsheet, and you can make
adjustments in size and location.
Now you’re ready to use simple
formulas within Excel to calculate
averages, differences, and sums.
Select 2 sub-groups from the
ethnicity category for one subject
(either E/LA or Math).
Use the AVERAGE formula to find
the average percentage value for
your school, corporation, and
state.
Note when you
type the formula
down here, it
appears at the top.
Type =average (click on the first
cell, click on the second cell, click
on the third cell) , press enter.
Repeat this AVERAGE formula for
your second sub-category.
Now find the difference between
the two by using a simple
subtraction formula.
Type = click on the larger
percentage cell – click on the
smaller percentage cell, press
enter.
Now you’re able to see the
difference.
Finally, the last skill you need for
this assignment is to use the SUM
formula.
Using the “Delve Deeper” data from
the school snapshot, input the total
number of tests administered by
gender for the grade level you
selected.
Use the SUM formula to calculate
the total by typing =SUM(highlight
the cells from top to bottom), press
enter.
You can also just highlight the cells
in the column first, going down to
one extra row, then clicking
AUTOSUM from the formula menu.
Finally, use the CONDITIONAL
FORMATTING feature to format
cells LESS than the state average.
Be sure you have met all the
requirements for this portion of
the assignment. Next you will
have the opportunity to look into
the data you have collected and
presented, and you will compare
your school with other colleagues
in the class.
Please submit your Excel
spreadsheet to TWO locations:
1) The ASSIGNMENTS, in
SUBMISSSIONS.
2) Upload it as an attachment to
your small group discussion
forum in Oncourse CL.