-- Not Just for Math Anymore
Spreadsheets
Across the Curriculum
 
An Online Tutorial
 
Introduction
This tutorial will, hopefully, dispel many common spreadsheet myths-- that Spreadsheets are just for math, hard to use, only for older students, etc.

This tutorial has three sections: Rationale, Design and Skills. The presentation order and sections used can be varied based on the participants' existing spreadsheet skills and the nature of the inservice.

Learn how to use student "built" and/or "pre-built" spreadsheets for best integration in various subjects and grade levels. See how Spreadsheets can assist students with alternative learning styles and assist students with special needs. Integration of Spreadsheets in lessons provide "real life" experiences and reinforce a wide variety of skills (interpreting charts, visual and mathematical comparisons, classification and sorting.)

This tutorial is both platform and vendor neutral and will be applicable to users of all varieties and versions of Spreadsheets Software.
Objectives
Rationale- Participants will
  • Discover why Spreadsheets (SS) are an important teaching tool
  • Explorer how SS are highly adaptable to instructional needs
  • Discuss the advantages/disadvantages of pre-built vs. student-built SS
  • Discuss the scalability of SS lessons

Design- Participants will
  • Explorer the concept of reverse engineering in SS lesson design
  • Discuss the uses of automated response SS
  • Investigate the use of SS as tools for measurement/conversion
  • Investigate use of SS as a tool for categorization/sorting/ordering/filtering
  • Explore use of SS as problem solving tool
  • Discuss the use of SS in modeling the concepts of commerce and  input vs. output scenarios
  • Investigate the use of SS as a charting/graphing/diagramming tool
Skills- Participants will
  • Investigate the use of cells, columns, rows and ranges
  • Discover naming conventions used for cells and ranges
  • Compare and contrast normal and absolute cell references
  • Discuss use of multiple worksheets
  • Investigate variety of methods for data entry including typing, copying, moving and auto completing
  • Explore variety of methods of formatting data
  • Learn how to set up pages for printing and how to use print preview and page setup
  • Learn how to create and use simple formulas
  • Investigate the use of compound formulas and functions
  • Explore the wide variety of functions available in SS
  • Investigate the use of functions and combinations of multiple functions
  • Learn where to find and how to use a wide variety of menu commands
  • Learn how to edit and save worksheets
  • Discuss the differences between keyboard delete, menu delete & clear
  • Discover variety of tools including auto-save, sheet protect and spell check
  • Discuss the concept and use of macros
  • Explore data commands including sorting, filtering (auto & advanced) and text to tables (parsing)
  • Discuss the needs to combine (concatenate) and/or separate (parse) data into individual or multiple cells.
  • Explorer view and window commands including page break preview, splitting and freezing panes
Materials
Handout booklet
In addition to the information, exercises and links on this page, participants are encouraged to print a hard copy of the companion booklet: Not Just for Math Anymore-- Spreadsheets Across the Curriculum. The booklet is provided free of charge as a PDF file and users are permitted to print one copy for personal use. If you need to print more copies or use copies in a classroom or inservice, please contact the author via e-mail for permission (mo_luscre @ mogadore.net).

To print the booklet follow these instructions:
  1. Download and open the file: Booklet as  PDF (v7.02.7)
  2. Print all the pages on legal sized paper (8½ x 14"). Depending on your printer use method a or b.
    • a. print two-sided pages using a printer with duplex tray capability
    • b. print single-sided pages then use a copier with duplex capability to create two sided copies.
  3. To assemble- arrange pages in order
  4. Fold all pages in half resulting in an 8½ x 7 booklet
  5. Saddle staple along the fold

Sample Spreadsheets-
 Presentation-
Other Resources
Getting Started
Begin by reading the booklet, pausing to follow the exercises. The exercises and related links are presented below for easier access. Often the online exercises will have additional material not presented in the booklet, be sure to read this more in depth material to get the full benefit of this tutorial.
Exercises & Links
Links to Exercises in Workbook
Follow-up- Questions, Comments  and/or Suggestions
Please feel free to contact the author with any problems or questions you may have. I value your comments and suggestions, these often result in revisions that make the tutorial better for future users. I can be contacted via e-mail at mo_luscre @ mogadore.net



Cats Click Links
Below to
Return
Mogadore Local Schools Web Page
Mogadore HS/JHS Portal Page
OHS Elementary Portal Page

All pages of the web site http://www.mogadore.net are copyrighted (1999-2007)
 by Anthony A. Luscre (Webmaster) and/or the Mogadore Local School District and all rights are reserved.
Please contact the webmaster for permission to use original materials--
mo_luscre @ neonet.k12.oh.us. Click here for legal disclaimer




Exercises

Pre-Exercise Tasks

A. Have installed or install a spreadsheet program on your computer- Microsoft Excel, Open Office
Calc, Lotus 123, etc. (Open Office is a Open Source, free Office Suite available for download at:
B. Please download* the documents used in these exercises before beginning:
* If you have difficulty downloading these documents, right click on each one and choose "Save Link Target As" or "Save Target As" depending on the browser that you are using

C. Open the downloaded spreadsheets. You may need to adjust font size, column width, colors, etc.
depending on the specific SS program you are using and/or your computer's screen resolution settings (the sample SS were originally created with Microsoft Excel and with the screen resolution set at 1024 x 728. This web page was also designed for optimum screen resolution of 1024 x 768 or greater, with a minimum of 800 x 600)
 #
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
1
 Pre-Built vs. Student Built Spreadsheets-

Magic Money Counter & Invoice-

A. Open SS_inservice.xls and choose worksheet labeled- "Money Counter" 

B. Change number of each coin type and observe changing total and pie charts

C. Observe how sthe preadsheet is built.

D. Write a short story problem for 6th to 8th graders describing building a money counting SS.

E. Build a spreadsheet from following story problem-- 

"Create a SS that will provide an automated invoice. Cases of Apples cost $17.00, Oranges $18.33 and Bananas $10.00. Customers get a 5% discount on any item, when they buy 10 or more cases of that item. SS should total costs for each type of fruit, calculate any discounts and provide a final total due."

F. Compare your spreadsheet from step E. with the sample worksheet labeled- "Invoice"
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
2
 What's the Weather Like Today?

A. Create a new SS that can track daily weather.

B. Design charts that will change as data is added each day.

C. Enter weather data for a month and observe changing charts

D. Describe three examples of how this SS project can be adapted for use in other subject areas and grade levels.           
 1._________________________________________________
 2._________________________________________________
 3._________________________________________________
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
3 Categories of Use

Give one or more example(s) of possible lesson(s) for your class for each of the SS categories at the left:

Spreadsheet Categories
 Possible Lesson
1. Calculation

2. Analytical/Logical
3. Statistical
4. Categorization/Grouping
5. Sorting/Ordering
6. Look-up/Matching/ Identification
7. Charting/Graphing/ Diagramming
8. Classroom/Personal Management
 N/A

Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
4
 Reverse Engineering

A. Using the example Apples.pdf, or preferably, a similar chart from one of your textbooks, reverse engineer the chart into a spreadsheet.

B. From your spreadsheet, create a chart similar in appearance to your original source's chart.

C. Change data using different scenarios and observe how your chart's appearance varies from the original chart.

D. Create the story problem(s) that might be used to change spreadsheet data.
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
5
 Self Grading Test

A. Create a simple spreadsheet where a student will input answers to three problems.
Each answer cell should have a related feedback cell.
The feedback cell will indicate whether the answer is correct or not.

B. Add a "final score" section to your SS that gives the student an indication of how many correct
answers they gave (out of 3 possible).

C. Provide a "Pass/Try Again" response. "Pass" if student gets 2 or more correct out of the 3 possible
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
6
 Sorting the Acidic & the Basic

A. Open SS_inservice.xls and choose worksheet labeled- "pH"

B. Enter names of new liquids to be tested in the first column, below the existing data.

C. Test the supplied liquids (or use measurements provided by your instructor) and match litmus paper to correct column.

D. Place an "X" in appropriate column for each new liquid
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
7
 Who is On First?

A. Open SS_inservice.xls and choose worksheet labeled-" baseball "

B. Sort by batting average (change the sort order from ascending to descending to put the highest
 average  first)

C. Sort by number of home runs. What happens if there is a tie between two or more  players?

D. Do a multiple parameter sort. Use home runs as the first criteria and the number of hits as the
 second criteria.

E. Auto Filter to show only players with no triples
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
8
 Which Animal Group Do I Belong To? &
Romeo, Romeo, wherefore art thou Romeo?

A. Open SS_inservice.xls and choose worksheet labeled- "animals"

B. Enter names of two new animals to be tested in the first column, below the existing data

D. Place an "Y", "N" OR "S" (for some)  in appropriate column for each new animals characteristics

E. Auto Filter to show only animals that lay eggs

F. What do you need to also Auto Filter to show only birds?

G. Open SS_inservice.xls and experiment with the "R & J" (Romeo & Juliet) worksheeet

H. Create your own SS, similar to (Romeo & Juliet), for a book (characters) or lesson you teach
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
9
 Building Your Dream House

A. Use the house.xls spreadsheet

B. Use the worksheet tab labeled "Items" to copy and paste your selections onto each room on worksheet tab "Main"
 (important- start your copying from column B and paste starting in column B)

C. Repeat for all other rooms.

D. Observe how the chart on worksheet tab "Graphs" changes with addition of each new room.

E. Create a pie chart of total amount spent on each different room type.
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
10
 Input/Output

A. Creat a spreadsheet that represents an Input/Output scenario.

B. Experiment with how imposing specific parameters changes the results
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
11
 Visual

A. Open spreadsheet- school.xls

B. Exam how the list of rooms & teachers is transformed into a building layout map

C. Using data from any of the other exercises, create charts & graphs to visually represent information
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
12
 Data Entry

A. Open SS Cells worksheet

B. Practice Data Entry & Manipulation including copying, pasting, paste special, auto complete and drag & drop
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
13
 Conditional Formatting

A. Open SS_inservice.xls and choose worksheet labeled- "Formatting"

B. Experiment with changing formatting for:    
    Numbers
    Alignment
    Borders & Shading

C. Open "Conditional" worksheet

D. Change value in cell B2, what happens to cell B3?

E. Examine conditional formatting of cell B3. Highlight the cell then go to top menu. Select Format...
Conditional Formatting
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17
 18 Bonus
14
 Formulas & Functions

A. Open SS_inservice.xls and choose worksheet labeled- "form & funcs"

B. Exam how simple math formulas work, including multiple & mixed formulas and  parentheses

C. Experiment with a variety of functions, including

Common Functions include:
SUM
AVERAGE
MAX
COUNT
IF Statement
CONCATENATION
VLOOKUP
COUNTIF

D. Combine two or more functions to do multiple operations from one cell
Exercises # 1
2
 3
 4
 5
 6
 7
 8  9

10
 11
 12
 13
 14
 15
 16
 17 18 Bonus
15
 Matching & Lookup Table

A. Open SS_inservice.xls and choose worksheet labeled- "vlookup"

B. Use the data to set up a lookup table

C. Name that range.

D. Use VLOOKUP function to find information on the key values