Making data collection templates | CASTLE - UCEA Center for the Advanced Study of Technology Leadership in Education

[72 minutes total]

Rather than just using Excel to analyze existing data, these tutorials show how you can use Excel to create data collection templates for use by teachers, administrators, and other K-12 educators. These tutorials were designed to be viewed in sequence (i.e., Time to learn some new formulas! before Example 1,Example1 before Example 2, and so on). Also note that these tutorials require mastery of many, if not most, of the other Excel skills covered in this web site.

You may need to turn off your browser’s pop-up blocker or install the latest Flash plug-in to view these tutorials.

Time to learn some new formulas! (13 minutes)

In order to understand the examples below, there are two new formulas you need to know.

If, then – Introduction (2:38)
If, then – Nesting statements (3:35)
If, then – Blank cells(2:59)
If, then – Is blank (3:30)
Download Excel file used for these tutorials

Example 1: Principal discipline template (19 minutes)

This example utilizes formulas, format cells, paste special, and charts and graphs to create a template that can be used by a principal to track student office referrals, suspensions, and expulsions by teacher and month.

Getting started (0:47)
Column layout for data entry (1:14)
Formatting the template (1:45)
Column layout for summary table (2:04)
Formulas for summary table (5:10)
Conditional formatting (4:51)
Charting the results (2:29)
Download Excel file used for these tutorials

Example 2: Classroom teacher academic template (11 minutes)

This example utilizes formulas, format cells, conditional formatting, filters, and freeze panes to create a template that can be used by a classroom teacher to track student progress on monthly assessments.

Column layout for data entry (2:03)
Entering student data (0:54)
Adding row averages and conditional formatting (1:48)
Adding column averages and filters (1:52)
Using the template (3:26)
Download Excel file used for these tutorials

Example 3: Grade-level team academic template (19 minutes)

This example utilizes formulas, format cells, and pivot charts to create a dynamic template that can be used by a grade-level team to track student progress on monthly assessments. Note what is happening in this template: we create pivot tables and charts using dummy data, then we delete the data. When the teachers enter the data back in again (this time real, not dummy, data), the dynamic pivot tables / charts recreate themselves!