This page addresses the history and role of spreadsheets, its relationship to the LEAP model, criteria for selection spreadsheets and other resources. It also provides a series of specific tutorials on the Excel and Appleworks spreadsheets. It further provides basic background information and a set of tutorials on Geographic information systems (GIS).

History and Role of Spreadsheets

The concepts of spreadsheets and of graphing have been around as long as business accounting practices have been around and as long as teachers have kept gradebooks on their students. Tables of rows and columns on paper were used to organize data for record keeping and analysis. As personal computers came along in the 1970's, it became possible for programmers to eliminate many tedious and repetitive activities involved in managing paper spreadsheets. This also greatly improved accuracy. Calculation activities done by hand were prone to producing many human errors. A pair of graduate students became bored and frustrated with the tedium of such activities in their coursework, one from Harvard (Dan Bricklin) and one from MIT (Robert Frankston). Out of that frustration the first spreadsheet was born in 1978, Visicalc. The invention of the spreadsheet made personal computers have real value in the marketplace and legitimated the personal computer industry. Without the invention of this software category, spreadsheets, the impact of the personal computer might have been delayed for years. Others have also developed a brief history of spreadsheets. GIS (Geographic Information Systems) software extends the concept of columns of data and graphing to themes and maps.

 Computers eliminate many basic mathematical errors. They cannot prevent all errors. Their existence does shift our educational focus from basic calculator activity to issues of planning, design and judgment. Our planning though will only be as good as the quality of our data and designs. As the saying goes, "garbage in , garbage out." Spreadsheets need to be seen as a strong introductory tool to a broad range of composition tools for mathematics and other disciplines. Though many of these mathematical procedures were first designed for calculators, then desktop software, increasingly they are available as online web-based tools on the Internet. Spreadsheets and their graphing features are to mathematical thinking what word processors are to language arts. Within the North Carolina K-12 technology competencies, spreadsheets are now formally to be introduced in second grade in North Carolina. Spreadsheets skills play an important part in the state-wide 8th grade computer skills competency exam. Further, there is an entire section of spreadsheet skills in the teacher technology competencies for NC pre-service and in-service teachers.

The LEAP Model for Problem Solving

Spreadsheets are one of the many tools that are placed at the Evoke stage of problem processing. Mathematics is both a form of expression and a language of human expression. To compose mathematically can mean composing solutions to given or known problems using this language. English language instruction could also taught in a similar manner, by providing students with opening paragraphs and then asking them to complete the essay. However, fluency with mathematics would seem to require increasing an approach more common to writing instruction. This would require emphasis on fuzzy problem situations in which the question or problem must be found within the setting or situation and then the composition completed (mathematically) once the question is framed.

Criteria for Selecting Spreadsheets

Beyond cost, there are several basic technical considerations to consider:
• Platform compatibility. Will the program run the equipment that is in the school classroom or computer lab? It may also be useful to have the program run on different types of computers found in the school district.
• Features. Have you personally used the program to test whether it can do the activities that students need at your grade level? Check the feature set against the state competencies for your grade level.
• Ease of use. Does the program provide an appropriate level of ease of use for its intended age level? "Feature glut" refers to have far more features than the user (student) will ever use or need and that these features are displayed before users are ready to understand or use them. There are spreadsheets on the market that simplify their screen displays for even primary level students.
• Help. Are there detailed help features built in to the program that not only explain how different features work, but walk the user through more complex activities (assistants or wizards)?
• Graphing. Can the spreadsheet automatically produce a variety of charts and graphs? It is also very beneficial to have a "hot" link between the data and the graph so that any change to the data in the cells is automatically reflected in the graph.

Reviews of Common Spreadsheets in Use Today

To find reviews, search the Internet using these terms in search fields: "spreadsheet review" or "spreadsheets reviews" or product based such as "Excel reviews." Yahoo in particular keeps an index to spreadsheet reviews. Software houses often keep the more positive reviews of their spreadsheets on their company web site.

Net Sites Focused on Spreadsheets

• Comp.apps.spreadsheets, a newsgroup for discussion of spreadsheets on various platforms.
• The Spreadsheet Page. A site by John Walkenbach, a popular author of articles and books and shareware developer of the Power Utility Pak for Excel.
• Cheat sheets for Excel  by : Univ of Washington ; Carlton College ; Univ of Maryland
• Excel Tutorials on the web that are text based.
• Excel screen movie tutorials by: Atomic Learning (subscription fee but excellent); Lynda.com (subscription fee). Screen movies are highly recommended. They have many advantages over reading text, in large part because using a computer is graphics and process based, some that text has a much hard time communicating than a screen movie.
• Software Illustrated MapLand, software that creates drag-and-drop geopolitical maps right in your Microsoft Excel spreadsheet.
• Live Access to Climate Data. "The PMEL/TMAP "Live Access to Climate Data" system allows you to brows and extract data from a large library of gridded climate data. You can obtain color graphics, spreadsheets, and formatted data files."

Spreadsheet Training

Use pre-made spreadsheets to solve problems

Using completed spreadsheets does not always require changing anything in the spreadsheet. It merely requires the ability to read the spreadsheet and to know how to reveal information hidden in the columns and rows of cells in the spreadsheet. For example, a cell may contain just a number. But a cell may also display a number which is the result of a calculation hidden under the displayed data. Merely clicking the cell shows the calculation in the editing window and the result of the calculation in the selected cell.

Begin the study of spreadsheets by learning how to navigate around a new blank spreadsheet, an empty spreadsheet space that forms a grid of cells in rows and columns. Move among the cells: using the arrow keys; the page up and down keys; the mouse pointer and the name box towards the upper left corner of the screen which jumps to any coordinates that you type in, such as A1, C4, or H24. Next open a pre-made spreadsheet in the spreadsheet folder you are given and notice what part of the spreadsheet changes when the cursor selects or moves into cells that contain calculations (formulas). Spreadsheet cells can contain labels (such as words), values (numbers) and formulas (calculations that always begin with an equal sign (=)). These formulas may contain both numbers and cell coordinates. The coordinates for a cell act as variables that provide the numbers for calculations in an equation. When studying pre-made spreadsheets, throughly explore the spreadsheet by clicking in its cells to determine where the formulas are. Study the formulas to determine what they do. Only with this background exploration completed should you begin to answer questions related to the spreadsheet.

Explore and Study Existing Spreadsheets

Complete this assignment. Use the Sample Questions and spreadsheets page to find a series of questions that require the use of a variety of completed spreadsheets. This work will be easier to carry out if this sample question page is printed to paper instead of read from the screen. These  spreadsheet questions model the kind of questions asked during the midterm and final exams. They are modeled on the kinds of questions asked on the 8th grade computer literacy exams given across North Carolina. These questions can be completed using either Microsoft Excel or Clarisworks on both Macintosh and Windows operating systems. Further training on both Excel and Clarisworks (also known as Appleworks) can be found below. The spreadsheets you need for this exercise will also be distributed in class or are available for download from campus servers or the web page.

Additional spreadsheet training that supports and complements classroom instruction is also available.

Excel for Macintosh and Windows Platforms

You can select online training for Microsoft's Excel, but this requires an account username and password. You or your institution can arrange similar online training for this application by contacting CBT (Computer Based Training). See also the Help menu command in Excel.

Clarisworks Spreadsheet for Macintosh and Windows Platforms

Passwords are not currently required for this Clarisworks tutorial.

 One of the best first steps with any application is to explore the help resources which comes with it. The more you work with these help pages the more you will know their organization for those times when less used details cannot be remembered. Finding the spreadsheet help tutorials (movie) is not difficult. Return to these tutorials to study new spreadsheets that are presented below.

Edit existing spreadsheets.

By changing the correct cells in a spreadsheet, formulas hidden with the spreadsheet change their calculations. In order to complete the Sample Questions assignment, you will need to change data in selected cells of the spreadsheet or in some cases enter some small formulas in unused cells. Figuring out which cell to change and in what way is part of the activity of the sample questions page.
 
1. Use the mouse to click within any cell of existing data and a border appears around the chosen cell.
2. Move to the edit window and begin typing. You can add, modify, delete or make other changes and/or additions to the data as needed.

#0. Introduction to Spreadsheets in Kindergarten and Primary Grades

Kindergarten Computer Literacy Competencies

2.3 Identify items by different attributes using manipulatives and/or software. (SS)
3.1 Group items by different attributes using manipulatives and/or software. (SS) 

Grade 1

3.1 Group items by different attributes using manipulatives and/or software. (SS) 
3.2 Gather, organize, and display data. (SS) 

•  Using technology at home and school 
•  Gathering, organizing, and displaying data 

2.6 Use a graphing program to enter data and graph the results. (SS)

3.1 Collect, sort, and organize information to display as a graph or chart. (SS) 

3.2 Interpret data on charts/graphs and make predictions. (SS) 

• Using technology in the community 
• Using electronic database to locate information 
• Building word processing skills 
• Collecting, sorting, and displaying data

Second grade competencies specifically indicate the use of a spreadsheet or other graphing program. An easy introduction to the use of spreadsheets for second grades might be the creation of a survey of the number of pets in the class. For example, create a quick practice spreadsheet and graph by estimating the number of classmates with pets: dogs, cats and fish. Enter the title pet survey on a cell in a spreadsheet. Next, under the title enter the information in pairs, the type of pet followed by a number. It is not necessary to total the numbers. There is plenty to cover here without also introducing addition. Next, highlight all the cells with data and click the chart wizard symbol and make a bar or column chart of some kind. It is instructive to also do this same survey using felt squares or play blocks or other manipulatives for second graders to see the relationship between the more abstract nature of the computer and other more concrete ways to express this.

What are some other introductory activities that might be used in kindergarten and first grade?

Create Spreadsheets from scratch

 The third step in your skill development is to create your own spreadsheets from scratch, that is, from the beginning. Preparing for spreadsheet development means making decisions about organization of labels and text and a sequence in which a problem should be addressed. To stimulate class discussion with your students, begin with a dialog about what data would be needed to solve a problem. Then discuss where one could go to find the necessary data. As this data is collected, it needs to fit into some pattern of organization with labels for all numbers and formulas that make the spreadsheet easy to comprehend. 

#1. Comparison Spreadsheet.






• Complete this assignment. One of the most basic uses of a spreadsheet is as a table editor. The skills emphasized here are those of writing and reading a table of information, skills which are also important text literacy skills. Learning computer technology can create its own anxieties; better to not add them to math anxiety when first learning spreadsheets. This assignment introduces the navigation and structure of spreadsheets and data entry without requiring any mathematical calculation, functions or formulas. Use such skills to create a comparison spreadsheet of your own related to your own project theme(s). A comparison spreadsheet is simply one in which two or more things are compared, based on a few factors or subheadings. This requires entering comparison data in rows and columns. The data for this example exercise comes from a useful Internet link available in the CROP site: the CIA Factbook. Find your own source(s) of information appropriate to the themes established on your unit plan of instruction. Use authentic information and cite your source in the spreadsheet.

 

Use Internet Explorer or Netscape to view these example Movie Explanations of the assignment. The videoclips are examples of procedures, not the exact procedures that need to be followed for this assignment. You of course are welcome to practice by creating a spreadsheet just like the one in the videoclips. However, the CROP pages continue to be updated since the videoclips were created. The CIA Factbook link shown in the videoclip is no longer in its prior location but can still be found elsewhere.
 

• Search for Comparative Data (movie). This data will be copied and inserted into the spreadsheet.
• Insert Comparative Data into the cells of a Spreadsheet (movie).

But the important step is to think through how to make your own comparison spreadsheet, not just copy the example. Use the procedural knowledge of the above two videoclips to make just such a comparative spreadsheet related to your curriculum project. Search the web  to find your own accurate and current sources of information. That is, focus on your own topic and create your own spreadsheet that is related to the theme of your curriculum project. Think of different columns and rows of information that will stimulate comparisons; type in actual data. Save this file to your disk in the normal spreadsheet format. Save this file into your Web folder, not the spreadsheet folder.

Next, prepare your table to be a web page. Current spreadsheets have a way to automatically turn a spreadsheet into a web page table. Open this spreadsheet in Excel if you have not already done so and save it to your disk as an HTML file. Also save this file to your web folder, not your spreadsheet folder. In later versions of Excel, click File in the menu bar, find Save As Web Page or Save As HTML file and select it to create the web page. Now this file can be linked from appropriate places in your web site. In older versions of Excel, these special commands do not appear directly under the File menu. Instead, click Save As, and then from the pulldown marker there for selecting other file formats, select the HTML or Web format. There will be other spreadsheet applications that can also save a spreadsheet as a web page.

Link the two versions of this spreadsheet data to your unit plan (bottom of section VII.) and upload the updated unit plan, and the Excel file and HTML file versions of the data to your web site. Test your links.

Excel does not produce an web page that shows grid lines for the rows and columns. Fortunately, there is an easy work around within web page editors, such as Netscape Composer. Excel builds a table and puts the data from the spreadsheet into the cells of the table, a simple procedure; but Excel turns off the command to make lines in the table visible. I don't know why it defaults to this or whether there is a way to change this default. It is not always essential to show grid lines.  If they are needed to make the reading of the spreadsheet data acceptable, then use them.
 

Use Excel to create a spreadsheet and then a web page. If gridlines are needed, have Composer open the web page version of the file, then follow these steps:
1. Click the screen cursor so that it is blinking somewhere inside one of the cells of the table that appears.
2. Click Format in the menu bar and select Table Info (this command will vary slightly across platforms and versions).
3. Make sure that the Table tab is the active tab.
4. Look for a place to put a checkmark for Border Line Width and/or a place to set the width of the line from 0 to 1 or greater. The lines should now be visible for every cell that has data. If the cell is empty, no lines will appear. To preserve the appearance of a grid put a period or some other character in empty cells. Close this dialog box and solid lines will appear in the place of dotted lines.
5. Upload and test the web page. If lines do not appear, go back to Table Info and check the settings to see if the changes were properly made.

#2. Gradebook Spreadsheet.

Movie Explanation of the assignment:
 

1. Create a gradebook (movie).
2. Find an average (movie). [To replicate a formula in Excel, the Fill commands are found under Edit or by using the tiny black square found in bottom right hand corner of the cell containing the formula. In Clarisworks they are found under Calculate.]
3. Use Functions (movie).

The spreadsheet should look something like the screen shot to your left. It is not a requirement to use color, but note how color can be used to make the spreadsheet more readable. To complete this spreadsheet, in the appropriate places you will need to use these functions: average, min, max, stdev, count.  See a full sized finished example as to how this might look. (But use your own names and different grade data!)

How does univariate, bivariate and multivariate analysis apply to spreadsheets? Analyzing a single column or range of data is a form of univariate analysis. Some of the more common questions you can ask about this range of data include: the range (lowest number to highest number); mean; median; and mode. If analyzing one column is univariate analysis, then what do bivariate and multivariate mean?

Once you have worked through this example, think of how this spreadsheet should be modified to better fit the evaluation required for your curriculum project. Not all evaluation is numerical, for example a column might include a letter, word or phrase. Sometimes an evaluation would include both. Modify the spreadsheet to fit your professional needs, but continue to keep the class data analysis part on each evaluation that uses numerical values. Save the file in its standard Excel format.

Next, prepare your modified gradebook to be a web page. Current spreadsheets have a way to automatically turn a spreadsheet into a web page table. Open this spreadsheet in Excel if you have not already done so and save it to your disk as an HTML file. Save it into your Web folder, not the spreadsheet folder. In later versions of Excel, click File in the menu bar, find Save As Web Page or Save As HTML file and select it to create the web page. 

Now this file can be linked from appropriate places in your web site. In older versions of Excel, these special commands do not appear directly under the File menu. Instead, click Save As, and then from the pulldown marker there for selecting other file formats, select the HTML or Web format. There will be other spreadsheet applications that can also save a spreadsheet as a web page.

Link the two versions of this spreadsheet data to your unit plan (section IX.) and upload the updated unit plan, and the Excel file and HTML file versions of the data to your web site. Test your links.

Link this spreadsheet created gradebook web page to your unit plan and upload the updated unit plan and the HTML spreadsheet table file  to your web site.

#3. Build a Complexity-scope Graph:

Using May's logistics equation (using Absolute and Relative spreadsheet values)

• Complete this assignment. 

#4. GIS - Moving Beyond The Limits of Spreadsheet Graphing

One of the best examples of this kind of application is provided by the free GIS system from ESRI for educators. This superlative K-12 tool is called ArcVoyager. When downloaded from the ESRI site, it comes with a number of datasets and images. The education page at their web site also provides a number of fine examples of student activities that will introduce students at different grade levels to the higher order thinking of GIS activity. 

The mapping department of our campus library makes heavy use of this resource. If you have any mapping questions, need some coaching on the use of ArcVoyager, have interest in developing maps for some aspect of your teaching using even more sophisticated computer mapping tools, or wish to learn more advanced GIS tools, then please visit the map mezzanine of Hunter Library. Consult with Dr. Anita Oser, Head of the Map Department. Do not be confused by a second free GIS program called ArcExplorer. It does not come with any of the K-12 maps and data sets nor the Guide that directs initial learning activities. That said, ArcExplorer is a fine program with most features in common with ArcVoyager, but learn to use ArcVoyager first.

The download link is also provided in the left column frame of this chapter. Using what you learn from the classroom presentation, download this program and explore its features for your professional use.

Download ArcVoyager Special Edition installer for Windows or Mac (37 MB) Once it is installed, find it in your list of programs and start up ArcVoyager and it will simultaneously start up ArcVoyager Guide.  Though it is highly recommended that this be installed on your own computer, this program is already installed on computers in K268 and can also be downloaded and installed on the computers in other public labs where it is not present. When these public lab computer workstations restart, this installation will be lost and must then be reinstalled to work on it again another day. It can also be downloaded and the installation program put on a storage device large enough to hold its 37 megabytes of data such as ZIP disks, CDs and USB drives. Given its large file size, it is strongly suggested that a computer with high speed Internet access be used to download the program. Next, copy this install program to a USB drive or burn to a CD to take the program home and install there instead of attempting to download this from a dial-up modem. To speed installation when home, first copy the install icon to your own computer and double click the icon to Unzip the application. A folder of files then be made available. Look for the file titled SETUP.exe and double click. This will walk through the the setup screens of the install process.

Use the directions in ArcVoyager Guide. 

The ArcVoyager Guide is a separate window that opens next to the actual GIS window.  This Guide provides tutorial information. For now, skip over the section on "Exploring Key Concepts: Teach Me" and begin with  "Viewing World Snapshots: Show Me." Note the right arrow symbol inside the red circle. Click this right arrow symbol to move through the instructional pages. Click the links on these instructional pages. Scroll down each page to complete it.

For this chapter, just complete the reading and exploring of ArcVoyager Guide's three pages that teach the use of World Atlas.

• Your assignment is to explore the different map views provided in the ArcVoyager Guide and then print out one of the map views in the World Atlas and bring it to class. It might look something like this example. Click within the "thumbnail" example picture below to see the full size. 
• 
• To do this:  When this program opens the help Guide also opens. Slide this Guide to one side. It opens to its table of Contents page.

  1.In this Guide section, click the link text shown here in quotation marks. “Viewing World Snapshots: Show Me This section is a world atlas displaying maps on nearly twenty different topics. The Atlas gets you using a limited number of standard ArcView tools as well as a few special ones.”

  2.On the ShowMe page, click “How to Use the Atlas “

  3. At the top of the Show Me How to Use the Atlas screen, click this link.

   “Click magnifying glass to open project” then follow the directions for adjusting and changing maps. Pick any variation of what is possible there and Print it following the directions there on how to print.

Of course, unless you have a color printer, the printout will only show black and white. Color printing is provided to students at cost as long as an email message is sent to the Print Shop (located above the women's soccer field) indicating that you did intend to print the image and will come pay to pay the fifty cents (half page or less) to a dollar (full page) for the color print. The image that is printed out does not have to be related to the theme of your unit plan. However, do be thinking of how this map composing tool might be useful to the development of instructional materials for your unit plan. In the Windows operating system, this example was captured using CTRL Printscreen, pasted into the application called Paint, saved scaled to 25 percent and 100 percent and saved as a jpeg format files, then linked to this web page. This use of the image capture skills (e.g., Printscreen key and the application Paint on Windows) will be covered in more detail in the chapter on image composition and desktop publishing.

 
• Optional:
• Getting Started with GIS thinking & ArcVoyager
• Additional ArcExplorer Activities
• Ideas for Reviewing GIS software for K-12 Education
• ESRI's Educators Online Discussion Conferences  http://forums.esri.com/Forums.asp?c=41

 

Lesson Plans

One of the most basic ways to introduce spreadsheets is to use them in place of the chalkboard or overhead projector when doing story problems. Within the cells enter the numbers and label them, then create a formula that carries out the required calculations. To create and or use a spreadsheet also emphasizes many higher order thinking skills. Analysis is certainly one of them. But many thinking skills can be easily exercised with spreadsheets, such as organizing, comparing, inferring and evaluating. Note that organizing is one of the higher order thinking skills tested on the state's 4th and eight grade exams. The use and creation of spreadsheets is required by the state's Computer Literacy competencies for preservice teachers and their faculty and inservice teachers.

 With the approval by the State Board of Education of new standards for computer literacy skills in the spring of 1998, spreadsheets are currently first officially introduced in second grade through K-12 Computer/Technology Skill competency goal 3.1. Prior to 1998 these skills were first officially introduced in sixth grade. However, activities undertaken in ealier grade levels than second grade help prepare for spreadsheet use, such as the creation of bar charts and graphs in kindergarten. Such charts involve students making comparisons between different columns or rows of data, a common spreadsheet activity.
 
 

• Study two of the several model lessons which are available for students in older grades. Use such models to determine how you can incorporate spreadsheets into your unit plan project. Read through at least two of these lesson plans:

 Sixth Grade Computer Skills

• Sixth Grade - Baseball Line-up Lesson Plan 
• Sixth Grade - Nutrient Counting Lesson Plan 
• Sixth Grade - Class Picnic Budget Lesson Plan . 
Seventh Grade Computer Skills
• Seventh Grade - The Heat Tracking Activity Lesson Plan, 
• Seventh Grade - Batter's Up Lesson Plan 
• Seventh Grade - Grade Master Activity Lesson Plan
• Seventh Grade - Class of "Characters" Lesson Plan
Eighth Grade Computer Skills
• Eighth Grade - Astronomy Mission Lesson Plan 
• Eighth Grade - SOS: Save our Shrimping Industry Lesson Plan 

Other Examples of Lessons and Resources for Using Spreadsheets

Fast Food Fun http://www.wmburgweb.com/Resources/Lesson/index.htm

Lesson Planet on Spreadsheets http://www.lessonplanet.com/search/Teacher_Resources/Technology/Spreadsheets

Math Forum's Web Units - Graphing http://mathforum.org/alejandre/spreadsheet.html

The ABC List of Things to Graph http://www.forsyth.k12.ga.us/kadkins/graphing_abc.htm

Template Gallery: (for Windows only) http://www.officeupdate.microsoft.com/templategallery/

Other Uses of Spreadsheets

Other uses of spreadsheets and graphing by educators include preparation of: tables (data in rows and columns); story problems; analysis of standardized test results; survey results by teacher or student; collection and analysis of science experiment data; social studies comparison tables; comparison of your classroom budget, club, book order or special group budget; field trip expenses; student financial management; personal investment, saving, checking, expense and tax return accounts; loan repayments; retirement planning; and exploratory data analysis.