Spreadsheets, Graphing and GIS
This page addresses several aspects of spreadsheet use and the plotting of data in various image-based formats. The in-document links below jump down the page to their section:
Hands on Activities
Spreadsheets to post on Web site and link from Unit Plan
Spreadsheet to post in File Cabinet
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.
In addition to the basic spreadsheet activities below, two important other extensions to desktop computing spreadsheets will be introduced as optional extra credit activities, GIS and Google Docs. GIS (Geographic Information Systems) software extends the concept of columns of data and graphing to themes and maps and Google Docs extends spreadsheet capacity to collaborative sharing over the Net.
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.
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.
Selecting spreadsheets involves determining criteria, reading reviews and exploring Web sites that address spreadsheet use.
Use pre-made spreadsheets to solve problems
Generally, we've had experience with reading before we learn to write. Following the same pattern with spreadsheets is just good instructional technique. The first stage of reading and using completed spreadsheets does not always require changing anything in the spreadsheet. It merely requires the ability to read the spreadsheet. However, unlike text on a printed page, a spreadsheet routinely has data that is hidden from the initial view of 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 the calculation of a formula 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. To read the all the formulas at once in a spreadsheet, use the CTRL Tilde key combination, a toggle switch that flips back and forth between the formulas view and the calculations view.
#0. Introduction to Spreadsheets in Kindergarten and Primary GradesThe NC SCS computer literacy competencies indicate that spreadsheet related activities should begin in kindergarten and first grade, with actual use of a spreadsheet beginning in second grade. The symbol SS is used to indicate spreadsheet related activities.
Kindergarten Computer Literacy Competencies
2.3 Identify items by different attributes using manipulatives and/or
3.1 Group items by different attributes using manipulatives and/or software.
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)
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?
There are multiple ways to use this spreadsheet design for all content areas. For example, remember those picture puzzles where you slide things around to make the picture come out right? There are many examples on the web. to provide further work with the higher thinking skill of analysis and provide further work with the important ideas in the spreadsheet that you just made, scramble the information after it is completed and save it by a different file name but add the word scrambled, than ask students to drag the information into the correct cells and unscramble the information. That is, when the spreadsheet cursor over a cell edge turns into a hand, the cell's contents can be moved to a new empty cell location simply by dragging and dropping the information. Once taught, this method also provides a useful assessment activity to see if they understand information at a higher level than recall. The structure of information is just as important if not more important than the factoids from which larger concepts are made.
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. All content areas can and should teach the reading and creation of tables. Along the way the other content areas are helping to establish basic knowledge about the Cartesian coordinate system or plane, an important mathematic concept. 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 unit plan 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 for a social studies activity 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 a Web browser 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
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.
New, Feb 24, 5 pm. There is a slight complication if you have the latest version of Excel, 2007. In working with a problem web link this afternoon, I discovered that the 2007 version is creating a folder by the same name as the file that also must be uploaded to your web site in order for the link to work. So, if the file is comparison.xls and it is saved as a web page it will become comparison.htm with a folder titled comparison_files. Link to the .htm file but upload both .htm file and the folder.
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 to make sure that the Excel file version can be downloaded and that the HTML version opens as Web page.
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 SeaMonkey 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.
For additional cool ideas in creating your own gradebooks in Excel, see the advice to professors that the college provides (courtesy of Dr. Nickles).
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 linked to a web page. Upload it to the file cabinet. Link this file to your unit plan (section IX.). Test your links.
Using May's logistics equation (using Absolute and Relative spreadsheet values)
Google Earth is a program that displays maps of the surface of planet Earth. It requires that a client application be installed on a Mac or Win computer, but the mapping images and database of information that is applied to the map in a number of optional layers comes from online sources. It includes a measuring tool for straight-line distances between any two points on its map. The Google Earth Tutorial explores the application's basic features.
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 the 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 ArcExplorer, 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.
Visualizing Data with Robots and Graphing Calculators
There are a number of different approaches to understanding patterns in mathematical data that sometimes fall under the label of scientific visualization. Several approaches will be considered here, robotics and sensors, creating 3D graphs (in Excel and desktop and online applications) and creating maps, a two or three dimensional field on which locations and other data can be organized. One class of mapping applications is called a Geographic Information System or GIS. It represents a kind of halfway point between spreadsheets and databases, or it could be seen as combining the charting capacity of the spreadsheet with the searchability of a database. All of these approaches intend to increase the motivation, engagement and relevance of the mathematical activity in which students are involved.
The Robot Algebra Project is one component of a much larger STEM Curriculum Continuum (STEM-C2) Project. This project is part of the combined effort of Carnegie Mellon (CM), the University of Pittsburgh’s Learning Research and Development Center (LRDC), and a consortium of industry, government, foundation and education partners. The algebra occurs within the programming language that drives robotic activity. The actions of the robot based on variable data collected by the robots sensors serves as the visualization of the success or need to change the underlying mathematics. Important precourses to algebraic understanding, including fractions, ratios and proportions are also addressed in this curriculum. This project uses the Lego Robotics kits in use by STEM participants of this course along with other types of robot kits.
3D Graphing Calculators
3D graphing applications and resources can readily be found in desktop and online formats. Of course the mathematics and the 3D graphing designs are the same, the differences that standout are easy of use, quality of the graphics, and the capacity for animating (rotating) the graph. Adding real world data that address the relevance of the learning along with the highly visual nature of graphing calculators adds considerably to the motivation and engagement of students learning algebra (Holliday & Duf, 2004).
The Online 3D Function Grapher produces excellent results. Clicking the green arrows shows that the rotation is not an automatic, requiring a click for each shift. An animated movie could be made with a screen movie program like ScreenToaster, but would require numerous clicks. Note that the Web page HTML code that can be copied for any equation and as demonstrated by the links in the examples in the bottom of the page. This means a link can be put on a Web page that will open to any graphing equation. That is, the link will send the data to the application, which will instantly plot and display it.
On Mac computers, an excellent but not widely known graphing application called Grapher ships free with the computer. Open it by clicking the hard drive symbol, then the Application folder, then the Utilities folder, then click on the Grapher application. Grapher is a graphing calculator with numerous capabilities for both 2D & 3D graphs. The 2D features include classic (linear-linear), polar coordinates, linear-logarithmic, log-log, and polar log. The 3D graphs include standard system, cylindrical system, and spherical system graphs. The screen shot on the right of the application's Example list shows eleven 2D and seven 3D examples. The Grapher - Getting Started tutorial by Rafid Hoda is an excellent introductory tutorial that shows how to find the application and many of its 2D uses and features. See also the description at Grapher Guide.
For a deeper 3D exploration, select any of the 3D examples from its pull down menu. One challenge might be to reproduce the animation below using Grapher: r = 4sin(10φ+T) for values of φ ≤ (pi/2) Where T animates between 0 and 2pi. Other movie demos: r = a(1 - cos(phi). Any 3D graph can be manually rotated and repositioned by clicking and dragging on the image. There is also a command to create a movie of a continuous animated rotation that can include zoom features. The animation below was created by the Grapher animation command.
A free 3D graphing application does not come built-in to the basic installation on a Win computer. Those needing one might consider downloading the currently free MathMechanixs program. For a brief demonstration, watch the screen movie below. Web site links below are available for searching for something better or different.
Cross-platform 3D graphing programs also exist. The Graphing Calculator 3D exists in an online version, and in downloadable versions for Mac, Win and Linux. The options include a freeware download version, and a standard and pro version for which there is a cost.
To find more information on 3D graphing and to be aware of new developments that are constantly emerging, periodically explore these useful search terms: 3D graphing; 3D graphing calculator; 3D graphing calculator online; 3D graphing Excel; 3D graphing software; 3D graphing program; 3D graphing online.
Mac and Win
Excel runs on both Windows and Macintosh computers. Excel does some special tricks with category labels to create 3D charts. Any category chart which contains equidistant values will display properly, whereas distortions will appear if not. See the 3D Surface page that plots z=(x-10)3 + (y-100)2 (note that the exponents are not displaying in the proper elevated format) for an example. Some two dozen more examples of Excel graphing capabilities along with pages that explain how to make them are available at the same site.
Graphing Calculator 3D
A freeware application is also available for download called Graphing Calculator 3D.which works across all major platforms including Linux and Unix. It is available as an online application, a free download to all platforms and in commercial standard and pro editions. It can also import data files from Excel.
Learn how spreadsheets work in an online setting at Google Docs. If you have a login to blogger.com for Blog posting, or a login for any other Google service such as email, then you use the same login for Google Docs which contains word processing, spreadsheet, and presentation applications. Go to Google.com and using the pull down menu to reach Docs or go there directly, http://docs.google.com and play the movie on the Google Docs login screen (2:50 length).
Next, open up the 2007 or 2008 version of Micosoft Excel, then the Comparison spreadsheet and using the same name, resave this spreadsheet to the older format, Excel 97-2004 (2003) so that the file ends in .xls.
Explore ways to Share the spreadsheet online.
Tutorials. Further training for Google Docs Spreadsheet can be found in many places including:
It should be noted that both Apple and Microsoft announced in 2009 that they would also be providing online access to common Office type applications through their own Web sites.
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. 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.
It takes hundreds of hours to be reading ready for books when children first come to school. It is equally important to get a high number of hours in with other applications including spreadsheets so they are digital literacy ready, let alone ready to carry out more advanced work. The odds of this happening at home are far less than with book reading so it is important for teachers to frequently integrate spreadsheet use in every content area from 2nd grade onward. Reading spreadsheets, not necessarily editing or creating spreadsheets, needs to be a significant part of this activity.
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
Address of this Web Page: http://www.ceap.wcu.edu/Houghton/EDELCompEduc/Themes/spreadsheets/spreadsheets.html