SPREADSHEET

Spreadsheet Terms

A spreadsheet is a grid with columns and rows - traditionally, an accountant's sheet.

Columns are alphabetic - designated by letters.

Rows are numeric - indicated by numbers.

Cells are intersections of columns and rows.

Information in a spreadsheet can take three forms:

Label: beginning with an alphabetic character

Value: beginning with a numeric character

Formula: a function used to calculate numbers.

Spreadsheets can be used in the classroom to:

compare fast food restaurants

compare grocery store prices

keep student grades

prepare budgets

return to table of contents

A SAMPLE SPREADSHEET: ICE CREAM FAVORITES

The purpose of this lesson is to learn to enter and manipulate spreadsheet data and formulae using Microsoft Works 3.0*.

You have had your students conduct a school-wide survey about favorite ice cream flavors. For the sake of this project, the number of flavors was limited to five, but you can replicate this using more choices, as well as many different categories.

Open MS Works and select a New spreadsheet.
The cursor should be located in cell A 1.
Type GRADES, and press the down arrow key.
The label GRADES will be stored in cell A 1.
The cursor will move to cell A 2.
Type KINDERGARTEN and press the down arrow key.
Continue to enter the labels in column A (see figure 1).
Move the cursor to B 1 and type the label CHOCOLATE.
Move the cursor to B 2 and type the value 10.
Press the down arrow key and continue to the next cell.
Enter all the labels and values in the B, C, D, E and F columns in like manner (see Figure 1).

Now the spreadsheet needs to have a way to calculate the total number for each flavor.

Place the cursor in cell A 10.
Type in the label TOTALS and press the right arrow key.
The cursor is now at cell B 10.
Type in the formula =SUM(B2:B8) and press the right arrow key. The computer will calculate the total for the items.
Type in the formulae at C 10, D 10, E 10, and F 10. Be sure to substitute the correct cell numbers. (see figure 2).

We need to make a title for the spreadsheet at this point. You will notice that when you insert a row into a spreadsheet, the program updates all information, i.e. it will automatically re-calculate the cells the values are in.

Move the cursor to cell A 1.
Go to Edit and drag down to select Insert. Repeat this procedure 2 more times. You will have inserted 3 blank rows, and the label GRADES will now be in cell A 4.
Type in the title CYBER-SCHOOL FAVORITE ICE CREAM FLAVORS and press return.

It's helpful to have totals for the grade levels, but the spreadsheet would be much more effective if it averaged numbers. To do this, you will type in formulae that will instruct the program to calculate averages for you.

Move the cursor to A 15.
Type in the label AVERAGES and press the right arrow key.
You are now in cell B 15. Type in the formula =AVERAGE(
Now move the cursor to B 5, click the mouse button, drag to B 11, and release the mouse button. This will automatically enter the cells for you.
Type an end parenthesis and press return. The computer will calculate the average number.
Move the cursor to B 15 again, then go to Edit and drag to Copy.
Move the cursor to C 15, go to Edit, and drag to Paste.
Repeat the Paste in cells D 15, E 15, and F 15. (see figure 3)

Notice that the flavor names are left justified, while the numbers underneath are right justified. This makes the data difficult to read. We need to make some cosmetic changes at this point.

Move the cursor to cell A 1.
Click the I-beam in the row at the very top of the screen under the menu bar (just in front of the word CYBER-SCHOOL), and tap the spacebar 20 or more times to center the title. You may need to do this more than once.
Highlight cells B 4 through F 4.
Go to Format, and select Format cells, select right and OK.
Highlight cells A 1 through F 1.
Go to Format, then Format cells, select Outline Selection, and OK.
Highlight cells A 4 through F 4, go to Format, then Format cells, select bottom, and OK.
Highlight cells A 13 through F 13, go to Format, then Format Cells, select top, and OK.

Let's make the averages more easily understandable to the students.

Highlight cells B 15 through F 15, go to Format, then Format Cells, select Number, then 1234.56, then type a zero (0) in the Decimal Places box, then OK.

The average is now rounded to the nearest number (see figure 4).

return to table of contents

MAKING CHARTS FROM SPREADSHEETS THE QUICK WAY

To chart the number of students who prefer chocolate:

Go to Window on the title bar and drag to select Show Tools.
These are the spreadsheet, drawing, and chart-making tools.
Select by clicking on the Cross Tool.
Click on cell A 4, hold down the mouse button, and drag to B 15.
The first two columns should be highlighted (A 4 will not show)
Go to Chart on the menu bar, click, and drag to New Chart.

A chart of those who prefer chocolate will appear.

return to table of contents

Pasting a Spreadsheet Chart into A Word Processing Document

Next, you'll cut and paste it into a word processing document.

Go to Edit, select Cut, and click OK.
Go to File, select New, Word Processor, and New.
Go to Edit, and select Paste.
Click in the center of the graph, and drag it down the page slightly.
Move the cursor to the top of the page, click, and type a title: A BAR GRAPH SHOWING THE POPULARITY OF CHOCOLATE ICE CREAM

CHALLENGE:

MAKE GRAPHS SHOWING OTHER FLAVORS

MAKE A GRAPH SHOWING KINDERGARTNERS' CHOICES OF ICE CREAM

MAKE A GRAPH SHOWING EVERYTHING ON THE SPREADSHEET

return to table of contents

SPREADSHEET EXERCISE

1. Numeric data in a spreadsheet are called

2. Alphabetic data in a spreadsheet are called

3. An instruction to perform a calculation is called a

4. The type of information in cell C4 is

5. The type of information in cell B8 is

6. The type of information in cell D13 is

7. The formula used to add values is

8. The formula used to average values is

9. How can you change the format of a cell from value to label?

10. Change the number of third graders who prefer vanilla to 18. Describe all the changes in the spreadsheet.

Type the ITEM in the first box, the CELL LOCATION in the second, the OLD NUMBER in the third, and the CHANGED NUMBER in the fourth. At the end of the exercise, you will have an opportunity to send the data to me for feedback.

11. What are two ways to enter cell locations in a formula?

Your Name:

Your email address:

return to table of contents