### Using Excel for Data Analysis

```W531 Excel Tutorial
the Indiana
Department of
Education website at
www.doe.state.in.us.
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
View AYP
Once you find your school, you
will see the “snapshot.” Spend
some time clicking around on the
its contents.
For elementary and middle
enrollment and attendance rate.
For high school, please look at
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
school made the AYP history for
the year.
what factors determine whether
a school has met this
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,
ethnicity, socioeconomics,
and more.
When you input
requirements, you
will see data
arranged like this.
Clicking on the
for high school will
take the user to this
type of data.
Now that you’ve
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
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
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
desired position.
Another shortcut is to click
circled above.
Alignment and text placement in cell position.
into the cells. You can add words, numerals,
or formulas.
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
Go on to the next slide to see the specific
Using your school’s “Data Snapshot” from the DOE website, find the following
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
percentage values.
Simply click “FORMAT”
then find the
Percentage. Change
the decimal place to 1,
and click OK.
TIP: When you change
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
Move your AYP data to the new column you just
attendance rate (for elementary and middle school)
or graduation rate (for high school) in column D.
your school to the IDOE website.
Data Input
Now it’s time to input more data into your spreadsheet. From the
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
Decide how you want to
IDOE website.
percentage values
BEFORE you begin to
select ONE grade you want to focus on for ISTEP scores in
Use color, formatting,
gridlines/borders,
merging, and cell
alignment to assist you in
user-friendly.
You can rotate the direction
the “orientation” in the
alignment.
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,
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
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
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
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
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
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