Excel in the Classroom

May 01, 2008

There are a variety of uses for Excel in the classroom. In this segment, we'll show how teachers can use Excel for classroom management and as a tool for their students to use.

Tracking Attendance

Enter the first school day in cell C1.
With C1 selected, choose Format, Cells. On the Alignment tab, drag the Orientation handle so it is at 90 degrees.
In the lower left corner of the cell pointer, there is a square dot. Right-click-and-drag the dot to the right. Go out about 45 days. When you release the mouse button, choose Fill Weekdays. Go through and delete any columns that are school holidays.
Enter student names in A2, A3, A4, etc.
The formula in B2 is =COUNTIF(C2:AZ2,"X")
Copy this formula down for all of the students.

When someone is absent, enter an "X" in the appropriate date. Column B will total the absences.

Call on a Random Student

If you are using Excel 97-2003, choose Tools, Add-Ins. Make sure Analysis Toolpak is checked.
Enter your student's names down a column. Perhaps A9:A23.
In cell B2, use the formula =INDEX(A9:A23,RANDBETWEEN(1,15)). Note that the "15" should be changed to the number of your students. Also, A9:A23 should reflect the actual range where you typed your students.

Every time you press F9, you will get a new student.

Lesson Plans

Enter times in column A, subjects in column B.
Make all of the cells tall. Select A2:A20 and use Format - Row - Height - 88
For columns A & B, use Format - Cells - Alignment - Orientation=90
For column C, use Format - Column - Width 61
For column C, use Format - Cells - Alignment - Wrap Text

To type multiple lines in column C, use Alt + Enter between each line.

Right-click the Sheet1 tab and choose Rename. Type a name such as Monday.
Ctrl+Click+and+Drag the worksheet tab to the right to make an identical copy for Tuesday. Repeat 3 more times. Rename the sheets as above.

Homework Checker

In the first example, the student enters the problem and the answer is shown. The formula for F6 is =B6*D6:

In the second example, the student types the answer into cell F6. A formula in B8 indicates is the answer is correct or not. If incorrect, they see:

When it is correct, they see:

Some steps to follow:

Select B8:F8. Use the Merge and Center icon:
The formula in B8 is =IF(B6*D6=F6,"Right!","Try Again")
Choose B8. Use Format - Conditional Formatting. Choose Cell Value Is, Equal To, Type "Right!". Click the Format button. Choose blue as the color on the Font tab and Green as the color on the Pattern Tab. Click OK to close the Format dialog. Click Add to add a second condition.
For the second condition, choose Cell Value Is, Equal To, Type "Try Again!". Click the Format button. Choose yellow as the color on the Font tab and Red as the color on the Pattern Tab. Click OK to close the Format dialog. The dialog should look as below. Click OK.

In the last example, the student enters the problem in C2 and C3. Formulas in C4:C7 build the answer. Here is how the worksheet looks:

Here are the formulas to make it work: