Cheat sheet for making an Excel database Step 1

Document Sample

```					                                                                                    Amber Del Gaiso
akdelgaiso@ssd.kl2.mo.us
11/20/2007

Cheat sheet for making an Excel database

Step 1. Launch Excel, choose "new workbook."

Step 3. Enter data.

Step 4. Add a column for Percentile Rank. If the column next to the measure is blank, just type in
" P R as the heading. If you need to insert a column, click on the column where you want it to be
added, and go to the Insert drop down menu and select Column. Then type in "PR" as the heading.

Step 5. Calculate the Percentile Rank for Student A. Put your cursor idhighlight the cell for PR
for Student A. This is very important, because the formula is calculated in the highlighted cell, so if
you accidentally have highlighted a cell with data, it will erase your data!

Student A      Smith       2                  70              *
Once you have highlighted the correct cell, go to the Insert drop down menu and select Function.
A menu of possible functions will pop up. Choose PercentRank from the list, and click OK (if you
don't see it, choose "select a category - all," and it should then be in the list). First, you need to
choose the array - highlight all the data cells for the probe (if you click on the arrow in the right
side of the array box, it will minimize the menu which will allow you to select the data set). Using
my example, the box filled with D2:D20. For x, you will click on Student A's data point, D:2. Click
OK. Student A's percentile rank should pop into the box.

Student A     Smith       2                 70                       0.666

Step 6. Copy and paste the percentile rank formula to fill the rest of the PR column. Important:
before copying and pasting the formula cell, you must make a small change that will make the array
a constant. Click on Student A's Percentile Rank. In the formula bar above, you should see this:
=PERCENTRANK(D2:D20,D2). make the array constant, you need to add \$ before each letter
To
and number of the array, like this: =PERCENTRANK(\$D\$2:\$D\$20,D2). adding \$, you are
After
ready to copy the formula. Select Student A's percentile rank cell, right click, and click Copy. Next,
highlight the remaining percentile rank cells, right click, and click Paste.
Step 7. Format the percentile rank column so the numbers are displayed as percentages.
Highlight all of the PR cells, right click, and click format cells. Under the Number tab, click
Percentage, choose 0 decimals, and click OK.

Step 8. Sort the data. Highlight the entire data set, and click on the Data drop down menu. Choose
Sort. Select to sort by ORF-PR, and click on descending order (this will display the highest scores
at the top, and go down from there). Click OK.
Step 9. Color code your data (26 % -100% = green, 11% -25% = yellow, 0 % -10% = red).
Highlight the PR cells from 100%to 26%, right click, and choose format cells. Under the Patterns
tab, select green and click OK. Repeat for yellow and red.

Step 10. Add other data, create percentile rank column, sort and color! You will end up with a
report like this that can be printed out for individual teachers or whole grades. Feel free to work
through these steps using my sample data to see how you do!

Student D    Smith      2      105
Student A    Smith      2        70

Fb q
.   Student J    Smith      2       1 02
Student E    Smith      2        75
Student H    Smith      2        90                       17                  25
Student F    Smith      2        81                        5                  25

22
22
10                  17
17

M
17

1          1 3 1
1                 10

I would recommend having one excel worksheet per grade. It is easiest for me to enter data for one
teacher at a time (it helps if you alphabetize each classroom stack of data).

After the entire grade's worth of data is entered, calculate each student's percentile rank (select the
whole grade's worth of data when calculating PR rank, as you want to know'where they fall in the
whole grade, not just their class). Then when sorting and coloring the cells, make sure you select the
whole grade set of data. This will enable you to create a report that shows all students in that grade.

If, after printing out your grade report, you want to create individual teacher reports (which I would
recommend), you just select the whole data set, click on the Data drop down menu, click sort, and
choose sort by teacher (ascending order), then by percentile rank (descending order). This will give
you the data sorted by teacher first, and then by percentile rank within each class. You can then
highlight each teacher's section of the data and print that section only, so they get a personalized
report!

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 5 posted: 11/6/2009 language: English pages: 6
How are you planning on using Docstoc?