Do you teach computer users to create spreadsheets? If so, you know that it often requires almost super human effort to keep the classes interesting and creative. In the following text, TechRepublic contributor Schoun Regan shares some of his “greatest hits” for teaching spreadsheet applications. Try applying these crowd-pleasing suggestions in your training classroom, and you may become the next star in your training department.

You sunk my battleship!
People who are new to creating spreadsheets may have a difficult time understanding how they work. In my classes, I use the game Battleship as an analogy. I have my students open a practice file with the following numbers on it: 4 fours in a row in row 5, 5 fives in column D, 3 threes in a diagonal from columns A to C, and so forth. All of the numbers represent ships. To get my students used to moving around the screen, I have them look in cell A4, and then C5. We then practice using the GoTo command. Next, the students are paired up and begin to play the game. When a student requests a number, all of them must change that cell’s color to red for hits, and blue for misses. Once they have mastered the game, they are now familiar with the fundamentals of spreadsheets—the locations of the cells.

What’s in a name?
Everything, when it applies to cell range naming. Cell range naming is an important part of teaching spreadsheet creation, and unfortunately, it isn’t communicated effectively. Because people would rather use words to refer to a cell than numerical cell references, cell range naming is important. First, make sure your students know tthat they can name a single cell, by clicking on the desired cell and typing in a name in the cell range box, located just above column A. For example, a cell with an interest rate could be named Interest_Rate. That way, when a user needs to reference the cell with the interest rate, the person simply types in Interest_Rate rather than the cell coordinates.

Basic math
The Sum and Average functions are important applications to teach your students, and will take up a great deal of time in a beginner class. Make sure students know they can choose separate cells and apply the Sum or Average function to that range of cells. When teaching these functions, I use the following example: I present the students with a spreadsheet with three columns titled:

  • Names
  • Departments
  • Salaries

Then I ask them to find the average salary of department 101. To find this, I tell them to hold the Control key and click only on the salaries of the people in Department 101. I then have my students name that range of cells by clicking in the cell range box above column A. In another cell I have them use the average function and reference the cell range name and, Presto!, the average salary of department 101 appears. Although this can be done using the Pivot Table, this is an excellent exercise for a beginning class.

Chart a course
Charts are so cool! To make sure your students understand the fundamentals, here’s a trick for reinforcing the concept of the X,Y axis. First, draw a horizontal line on your whiteboard or use the room edge as an example. Then draw the letter Y on the left of the horizontal line about 1-2 feet above the horizontal line. Draw the two top parts of the Y first and then drag the bottom part of the Y all the way down and connect it with the horizontal line. You’ve just given your students an excellent way to remember which axis is X and which is Y. Draw it again to reinforce the X,Y axis idea.

Radar love
If your spreadsheet course doesn’t include how to create a radar chart, use this exercise to illustrate how to use one. Create a column with numbers from 1 to 12 and title it Location. Title the next column Tolerance and enter numbers from 0 to 75 placing them in random locations in the column. Now create a radar chart using these columns. Tell your students that the Location column is the clock location around a tire. Then tell them that the Tolerance column was created when someone took measurements around the tire when looking for defects. The Tolerance numbers indicate the locations on the tire where the tire deviated from the set standard.

The Hunt for Red October
Remember that movie? Sean Connery’s submarine went into a deep-sea trench. Teach your students how to rotate and modify a 3D chart by recreating that trench. Here’s how it works. Take a spreadsheet and type in the numbers illustrated in Graph A below.


Graph A
Type the numbers into your chart as illustrated.

Do you understand how I created it? Select the chart information and click on the Chart Wizard. Under Standard Types, select Surface. Then click on Next and there you go. The chart shows a deep-sea trench, but the students must rotate the chart to actually find the trench. To rotate the chart, select the chart, right click on the chart and select 3-D View. In 3-D view, use the rotating options to rotate the chart around until they see the trench. Then click OK. The end product is illustrated below in Graph B. Be sure to explain how the numbers are used to create the chart, cartographer style.

Graph B
The trench (created using the 3-D option and rotating the chart graph).

5, 10, 15, 20, Stop
Math scares a lot of people. So make your spreadsheet training fun and your students will never know what hit them. There are many more spreadsheet tips in my bag. I’m sure you’ll see more soon.
If you’d like to comment on this article, please post your response below. If you have any application tips you’d like to share with your fellow IT trainers, please send us a note. We are also looking for contributors interested in writing training-related articles and tips about Novell, Cisco, UNIX, and Linux. If you are interested in a possible writing opportunity, please send us a note.

Schoun Regan is a consultant to training firms and travels across North America educating people for Complete Mac Seminars.