Excel as You've Never Seen It
by Kay Lehmann Professional Development and Online Learning Consultant, 1999 Milken National Educator
MultiMedia Schools  January/February 2002 
[Editor's Note: The great ideas Kay Lehmann shares in this article require skills that can be yours by taking her online course: "Beginning Excel for Teachers: Spreadsheets Are for Everyone." See http://www.uni.edu/profdev/catalog.html#excel for more information.]
"W hat software program is this?" interrupts Diana's father.

Diana swiftly answers, "Excel!"

The father's whole body turns towards me, Diana's 8th grade social studies teacher, as he says, "I use Excel everyday at work. I had no idea you could do anything like this with it!"

Leaning down beside his daughter, he asks, "How did you do this?" Diana's explanation is rapid and extensive. As the signal to move to another classroom is given, her father mutters, "I can't believe what they are learning in here! It's amazing!"

It's curriculum night, and another group of students and parents has just exited my classroom.

What led up to that exchange? Diana had guided her dad over to the computers, saying, "Come here, Dad. I want to show you my project. This is my Table of Contents. It leads to all the projects we've done this year in Mrs. Lehmann's class."

When Diana's father first leaned over her shoulder, his face wore a dutiful look. The screen he saw was filled with softened graphic of a fireworks display. Superimposed on the graphic were project titles, in blue, underlined text. As any user of the Internet would know, this signified that they were hyperlinks. Hyperlinks can take you to different Web pages, but in this case, when Diana clicked on any of her project titles, the hyperlink caused the appropriate project file to open.

The Versatility of Spreadsheets
Spreadsheet software is amazing! Everyday tasks such as keeping track of student information or creating a simple gradebook program are within the grasp of every teacher. Students can gather data from scientific experiments and then sort it to analyze the resulting patterns. This information can then be turned into colorful graphs for reports or presentations. Rubrics that add themselves up and calculate a percentage score are simple to create. For those with more mathematical ability, intricate formulas can be used to analyze data. There is something for everyone.

I began using spreadsheet software for annual farm budgeting and expenses. At that time, nothing about the software packages seemed easy to use or understand. Upon my return to the classroom, I was selected for the Teacher Leadership Project funded by the Bill and Melinda Gates Foundation. During our training for this grant program we were shown how to use Excel.

To my surprise and pleasure, spreadsheets had apparently come a long way and had never seemed so inviting or so easy. Excel is a powerful program, but it is not hard to use. As part of the Microsoft Office suite of software, it shares many of the same menus and toolbars as Microsoft Word and Microsoft PowerPoint, which are more common classroom applications. Once you learn how to use a toolbar in one program, you can use it with the other programs. This decreases the learning curve. (What a novel concept these days, right?!)

So what was the project that so intrigued Diana's father? For our annual curriculum night students were instructed to create an interactive table of contents page that would link to projects in their network files. It was their choice whether to use Word, PowerPoint, Excel, or a Web page for the table of contents. Diana's class had used Excel to create interactive maps earlier in the year. When the table of contents assignment was given, Diana turned to the skills learned in the mapping exercise to create the project that wowed her dad.

The Interactive Map Project with Pop-Up Comments
What was the interactive map project? How did students use Excel to make their map? Good questions!

A map was placed in the background of an Excel worksheet and comments about various locations were added to the sheet. Hmmm.... Was that explanation a little too fast? Let's take it step-by-step.

Social studies is my subject matter, so I chose a map for the background graphic. However, a project of this technical nature would work just as well with any subject-matter-appropriate picture. Say, a drawing of the digestive tract, or a graphic of our solar system, or ... use your imagination.

The picture needed to be about 400 pixels by 600 pixels. I found a map of Egypt on the Internet, then copied and pasted it into a photo editing program. (Be sure that you have, or get, permission to do so, of course.) The size was much larger than my goal of about 400 x 600, so I scaled it down and then saved it. The suggested size is just that: a suggestion. Keep in mind that a picture smaller than 400 pixels square will not contain enough details. If it's too much larger, say 900 x 1,000 pixels, the whole picture may not be visible to students as they work. For this project, all students were going to work with the same picture, although they could have been working with a variety of pictures.

Pairs of students worked together researching locations important in ancient and recent Egyptian history. Some locations were mandatory: the Nile River and its six cataracts, the Aswan High Dam, and the cities of Luxor, Giza, and Rosetta. In addition, students were to research at least one additional site of their choosing, such as the Valley of the Kings. It was important that they complete their research and write descriptions in their own words before going to the computer. This prevented them from copy/pasting information directly from Web sites or the Encarta encyclopedia, while allowing for more efficient use of the computers.

Once their descriptions were ready and I had given them my approval, the students were ready to use Excel. They opened new Excel documents, selected Format/Sheet/Background, then inserted the Egypt map I had saved. The picture appeared in the background of the sheet, over and over again. Excel worksheets are massive so the image "tiled." It is because of this tiling effect that you don't want to start with an image that is too small.

Next, the students needed to make all the cells on the worksheet smaller so their research could be put as close to the correct location as possible. To change the size of all the cells at once they first clicked in the space above the row heading 1 and to the right of column heading A. Once the whole worksheet was selected, they went to Format/Column/Width and set the width to 2.0. To get rid of the annoying gridlines over the picture, they selected Tools/Options/View, removed the checkmark beside Gridlines, and clicked OK.

To make it appear that there was only one image on the screen, students highlighted several rows below the picture and filled the cells black. Likewise, they selected several columns to the right of the image and filled them with black.

Now that the worksheet looked nice, it was time to add the researched descriptions. By clicking on a city, students could select a cell of the worksheet. Into that cell students would put a pop-up comment box, selecting Insert/Comment on the menu bar. A small, yellow textbox appeared on the screen into which they typed their description. Once they clicked in a different worksheet cell, all that remained in the previous cell was a small, red triangle. When the cursor was placed over the red triangle, the comment box would reappear until the cursor was moved away.

Other effects can be added to this project through the use of hyperlinks, but space limitations don't permit me to go into that skill here.

Peer-Group Grading with Excel
Students in my classroom use Excel frequently. When student presentations were on tap, I created a rubric for peer grading the presentation. Peer graders for the day would watch the presentation from a computer and put scores into the rubric at the conclusion of the presentation. The rubric would add the scores for each criterion to give a total and a percentage score. Comments could be typed at the bottom of the rubric form.

I have found peer grading to be very effective provided you accomplish the following in advance:

  • Educate students about the presentation criteria. When they understand what they are looking for, their scores will be more accurate.

  • Pre-select a variety of students to peer-grade for the day. In that fashion, all the students in one social group don't dominate the grading. This negates the effect of students giving a more positive score to their buddies. Even adults unconsciously grade people they like more positively.

  • Make sure that you have at least four peer graders. The effect of the positive/negative mentioned above will be washed out when the scores are averaged.

  • Give students time to grade immediately after the presentation, while it is fresh in their minds.

  • Have them save the rubric/spreadsheet files frequently to avoid losing data.
The averaged peer score was equal to half the point value of my score for the presentation.

Professional Uses of Excel
The use of Excel shouldn't be limited to student projects: It's a great tool for teachers as well. Here are a couple of examples that worked well for me:

By default, each Excel file, called a workbook, has three worksheets. On Sheet 1, I entered student data, including home addresses, home and work phone numbers for guardians, assigned book number, student ID number.... An Excel cell can hold unlimited information, so you can design the sheet to hold whatever you want. If a student regularly goes back and forth between households, you can have the information in your spreadsheet. On Sheet 2 of the same file, you can easily create a simple gradebook, posting names by copying and pasting from Sheet 1. All of this information can easily be sorted alphabetically or numerically.

And budgeting for field trips is a snap with Excel. On one sheet you can keep track of expenses, and on another you can track which students have brought in money for the trip.

Need to keep names or information organized for a committee? Use Excel.

Want a nice-looking graph of expenditures for a presentation? Excel will make a graph of your data and update the graph every time the data changes.

Here's hoping you're a convert, a teacher who wants to work smarter, not harder. Excel is a tool that can help you do just that. It should be in every teacher's toolkit.

Communications to the author may be addressed to Kay Lehmann, Professional Development and Online Learning Consultant, 3120 Canberra Drive, Walla Walla, Washington 99362; 509/529-4006; fax: 720.533-8499; e-mail: kay_lehmann@hotmail.com.

[Information Today Inc.]
Information Today Home Page
[MultiMedia Schools]
Home Page
[Current Issue]
Current Issue
[Current Issue]

Copyright © 2002, Information Today Inc. All rights reserved.