About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store

  Bookmark on!

Excel in the K-12 Classroom

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

The tip in this show is from Excel for Teachers.

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.

For the BEST TV show on technology, check out Call for Help.

This tip was originally published on January 25, 2006 and aired on TechTV in Canada and Australia on February 17, 2006.

The permanent URL for this page is

If you are looking for show notes from another episode, visit my complete list of TechTV appearances. Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.