MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel in the K-12 Classroom

January 25, 2006

Most teachers have computers in their classroom, but don’t know how to utilize Excel. In this segment, we’ll take a look at 3 classroom uses for Excel:

  1. Create customized math exercise sheets
  2. Track reading progress
  3. Track Attendance

Customized Math Exercise Sheets

Say that your first graders need to work on adding digits 1 to 9 where the sum does not exceed 10.

  • Tools - Add-Ins - Make sure Analysis Toolpack is turned on
  • For the top addend, use =RANDBETWEEN(1,8)
  • The second addend needs to be a number between 0 and (9-top number). Use a formula of =RANDBETWEEN(0,(9-B2))

  • Apply formatting, copy the formulas to create multiple problems.

Every time that you type the F9 key, you will produce a new worksheet.

Track Attendance

  • Enter the student names down column A, starting in row 2.
  • Enter the date for the first day of school in C1.
  • Move the cellpointer to C1. In the lower right corner, there is a square dot. This is the fill handle. Right-click the fill handle and drag to the right. Drag out for 180 columns. When you release the fill handle, choose Fill Weekdays.
  • To use the chart, enter an "X" for any day the student is absent.
  • The formula in B2 is =COUNTIF(C2:IV2,"X")

Track Reading Progress

This is a cool chart to share with parents during parent-teacher conferences. All of the reading scores are stored out of sight in rows 34 & below. In the top of the worksheet, you will select a single student from the dropdown in A4. The chart will update to show that student.

  • Enter your test data starting in row 34
  • In cell A4, use Data - Validation - List and specify a list range of A34:A64. This will add the in-cell dropdown.
  • The formula in B4 is =VLOOKUP($A4,$A$34:$I$56,2,FALSE). This will return the test result from column B for this student.

  • When you copy the formula in B4 to C4 & D4, change the 3rd parameter from 2 to 3 for column C and then to 4 for column D.
  • Build a chart using A3:D4. Format the chart so it is the right size for viewing with the parents.

As each parent comes in, choose the student from the dropdown in A4. The chart will update.

All of these workbooks are available for download with the book. Through the courtesy of the publisher, I am able to share the ReadingProgress.xls workbook with viewers of the show.