AppleWorks 6 Spreadsheet


Table of Contents


Definition
New Spreadsheet Document
Basic Parts of the Window
Button Bar
Moving Around
Entering Data
Enter Headings
Enter Student Information
Edit Data
Formatting Data
Format Headings
Specify Formats for Numbers, Dates, or Times
Adjust the Column Width and Row Height
Sorting
Inserting/Deleting Columns and Rows
Printing
Formulas and Functions
Charts
Instructional Applications

Definition

A spreadsheet allows the user to organize data in rows and columns. Each cell in the spreadsheet can contain words, numbers, and/or formulas. A formula enables the spreadsheet to perform calculations with the numbers or text in other cells. The AppleWorks Spreadsheet can also be used to create color charts and graphs.


New Spreadsheet Document

To create a new spreadsheet file, launch AppleWorks. Click on Spreadsheet in the Starting Points window.


If AppleWorks is already launched, choose New...Spreadsheet from the File menu.




Exercise 1:
Open a new spreadsheet. Practice adding a button to the Button bar.

Basic Parts of the Window
A spreadsheet document is a grid of rows and columns. The intersection of a row and a column is called a cell. Each cell has a unique address which consists of the column letter and the row number, for example, A1.


  1. The Menu bar contains commands that are used to operate AppleWorks Spreadsheet. It contains many commands that are used in other applications (i.e., cut, copy, print, etc.), but it also contains commands that are unique to AppleWorks Spreadsheet.

  2. The Button bar provides quick access to common commands found in the menus. The Button bar can be customized to include the commands you use most often.

  3. The Active Cell Address indicates the address of the currently selected cell.

  4. The Active Cell is the selected cell. It is identified by a highlighted border. The address of a cell is denoted by the column heading followed by the row heading.


  5. Data for the active cell is entered in the Entry bar. Cell data that has been previously entered is edited in the Entry bar.

  6. The Column Heading indicates the column portion of the cell address. Column headings are always letters.

  7. The Row Heading indicated the row portion of the cell address. Row headings are always numbers.


Button Bar

The Button bar gives you quick access to common commands. The Button bar can be customized to include the tasks you use most often.




To customize the Button bar, choose Preferences ...Button Bar from the AppleWorks menu.



NOTE: To delete a button from the Button bar, simply drag that button to the trash.

NOTE: You can hold the Control key down and click on a button on the Button bar to remove the button or access the Cutomize Button Bar window.

Exercise 2:
Add a custom button to your spreadsheet Button bar. Remove the button you added from the Button bar.

Save your spreadsheet.
Moving Around

You can move around in the spreadsheet by various means. The arrow keys can be used to move left, right, up, and down within the boundaries of the spreadsheet grid. The arrow keys move the active cell one cell at a time. You can hold an arrow key down and get a continuous one-cell movement until you let up. You can similarly use the [PageUp] and [PageDown] keys to move the active cursor up or down one screen at a time.

Another way of moving the active cell is to point to a cell with the mouse and click. The active cell will move to where you clicked with the mouse. Click on cell C5 and the active cell position will change.

You can also use the vertical and horizontal scroll bars to look at other parts of the spreadsheet.

When the scroll bars are used, the active cell will remain where it was last selected and will disappear if the window of view moves beyond the view of the active cell. After using the scroll bars, you have to click on a cell in the new display to actually change the active cell.


Entering Data

To enter data in a cell, click on the cell to make it active. Type in the data. The information will be displayed in the Entry bar.

Exercise 3:
Make a student class list that includes the following information:

Last name
First name
Address
Phone #
Gender
Transportation

Enter Headings


Enter Student Information
NOTE: Tab enters text and moves the active cell to right one.
Return enters text and moves the active cell down one.
Enter enters text and keeps the active cell the same.

Your spreadsheet should be similar to the one below.



Edit Data

To edit the data already entered in a cell, do any of the following:

  1. Click on the cell to make it active. Begin typing new text. Press tab, return, or enter to put the new text in the active cell.

  2. Click in the data entry box and add, delete or change text as you would in a word processing document. Press tab, return, or enter.

  3. If you are in the process of entering information in the Entry bar, click on the red X next to the Entry bar to cancel the input.

Exercise 4:
Use the editing features described in the section above to edit data in your spreadsheet. Practice changing the names and/or addresses of at least two students.

Save your spreadsheet.
Formatting Data

Cell data can be formatted using the Button bar and/or the Format menu.

Format Headings:
 Specify Formats for Numbers, Dates, or Times
Select the cells you wish to format.
Choose Number from the Format menu.

To format a number:



To format a date:


To format a time:

Adjust the Column Width and Row Height:
Adjusting column width may be necessary to view all of the data in a cell. There are several ways to adjust the column width.

  1. Click on the letter of the column you wish to adjust to select that column. Choose Column Width from the Format menu.


    You can select multiple columns and set them all to the same width.

  2. Put the cursor on the line between column headings. When the cursor changes into a column adjuster guide (), click hold and drag the column to the desired width. You can select multiple columns by highlighting the column letters and adjust all of them at the same time.

  3. Put the cursor on the line between column headings. When the cursor changes into a column adjuster guide (), double-click. The column will be adjusted to fit the longest entry in the column. You can select multiple columns by highlighting the column letters and adjust all of them at the same time by double-clicking on the last selected column.

Row heights can be adjusted in the same way column widths are adjusted.

Exercise 5:
Use the formatting features described to format the data in your spreadsheet.

Save your spreadsheet.


Sorting

To sort information, you first need to select all of the information that is to be sorted. Do not include blank rows or headings. In order to select a block of data, click in the upper left cell of the block. Hold down the Shift key, and click in the lower right cell of the block. With the correct data selected, choose Sort from the Calculate menu.

Use the options in the Sort dialog box to define the details of the sort.



Check your data carefully! If the sort worked incorrectly, choose Undo from the Edit menu immediately.

Exercise 6:
Select the block of information to sort. Click in cell A2. Hold down the Shift key and click in cell F8.



Sort your students by their last names. Select Sort from the Calculate menu. Choose the options displayed in the picture below.


The results should appear similar to the example below.


Try sorting your students by first name.


Sort your data by gender and then by last name. Select the appropriate data. (Cells A2 through F8) Choose Sort from the Calculate menu. Select the options as displayed below.


Try to produce a list of students sorted by method of transportation and then by last name.

Save your spreadsheet.
Inserting/Deleting Columns and Rows

Insert a Column
To insert a column in a spreadsheet, click on the letter of the column that you want to the right of the inserted column. Clicking on the letter of the column will select the entire column. Choose Insert Cells from the Format menu. A new column will be inserted to the left of the selected column.

Insert a Row
To insert a row in a spreadsheet, click on the number of the row that you want below the inserted row. Clicking on the number of the row will select the entire row. Choose Insert Cells from the Format menu. A new row will be inserted above of the selected row.

Delete a Column or Row
To delete a column or row, click on the letter of the column or the number of the row to select the entire column or row. Select Delete Cells . . . from the Format menu.


Exercise 7:
Sometimes you might want to assign students a “gradebook” number. In order to do this, we need to create a new column. Insert a column to the left of column A in your spreadsheet using the directions above. Enter student “gradebook” numbers in this column. (Example: 1,2,3,etc.)




Enter “Number” in cell A1. Enter 1,2,3,4,5,6,and 7 in cells A2 through A8 respectively. Adjust column A to fit the longest entry. The spreadsheet now looks like the sample below.


Save your spreadsheet.


Printing

To print your spreadsheet, choose Print from the File menu. Then click Print. If you do not want to print the row headings, column headings, and/or cell grid, follow the directions below.


To change the page orientation and/or scale, choose Page Setup from the File menu.


To print part of a spreadsheet, select the cells you wish to print. Choose
Print Area . . . from the Options menu.



To set the spreadsheet to print all cells with data again, choose Print Area . . . from the Options menu.



Exercise 8:
Select cells A1 through D8. (Don’t forget about the Shift key.) Set the print area for this block of cells. To see how the spreadsheet will look when printed, select Print from the File menu.


The print display will open in Acrobat Reader. When you have finished looking at the preview, choose Quit from the Acrobat menu.

Save your spreadsheet.

NOTE: Preview works in OS X only!


Formulas and Functions

A formula is a set of instructions for calculating a value in a spreadsheet cell. All formulas begin with an “=” sign and contain data with rules to process that data. The data can include numbers or the name(s) of cell(s). The rules for processing the data can include arithmetic operators (+, _, *, /) and functions. A function is a predefined formula that performs a specific calculation. Functions consist of the name followed by the arguments surrounded by parentheses.

Exercise 9:
Open a new spreadsheet document. After dividing M&M’s among members in your group, set up a spreadsheet with the number of M&M colors for each person in your group. Format your headings and data to look the way you want.
Your spreadsheet should be similar to the one below.


Enter the number of each color of M&M’s that every group member has. Save your file.


Type an “=” in the cell. We want to calculate the total of this color of M&M’s. Click one time on each cell that contains numbers of M&M’s of this color. In this example, it would be B2, B3, B4, B5, and B6. Notice how “+” signs are automatically put between the cells. Addition is the default operation. If you want to multiply the entries, you would have to type in the multiplication sign (*) between entries.

The formula should look like this:

=B2+B3+B4+B5+B6

Press return. The results of the formula should appear in the selected cell. (B7 in this example)

Enter a similar formula to add the values in column C.


Click in the cell that contains the total of M&M’s in column C. (C7 in this example) Choose Copy from the Edit menu. Click in the cell that should contain the total of values in column D. (D7 in this example) Choose Paste from the Edit menu. Notice that the formula in column D changed to add values in that column. Paste the same formula in column E. Notice that the formula changed again to add values in column E. This is called a Relative Reference. The values in the formula change to fit the column or row of the formula.

Paste the formula into the cell at the end of each column of values.

Your spreadsheet should look similar to the one below.


Now let’s use a function to calculate the totals in each row. Delete all values in the totals row. Click in the total cell in column B. (B7 in this example) Type “=”.



When the Insert Function dialog box appears, follow the directions below.



The following formula should appear in the Entry bar.

=SUM(number1,number2,…)

Highlight everything between the parentheses, number1,number2,..., and press the Delete key. Now on your spreadsheet, highlight the cells you want to add. (B2 through B6 in this example) Then press Return. The formula in the Entry bar should be similar to the following.

=SUM(B2..B6)

The results of the formula will be displayed in the selected cell. (B7 in this example)

Use copy and paste to duplicate the formula in all “color” columns.

Use the methods described previously to calculate the total number of M&M’s for each person. Hint: In this example, the first formula would go in cell H2.

Save your spreadsheet.
Charts

Charts can be used to graphically illustrate your data for better understanding. To make a chart, you select the data you wish to chart and then click on the Make Chart button in the Button bar. Select the type of chart you want.


Click on the Axes button.



Click on the Series tab.



Click on the Labels tab.




Click on the General tab.


NOTE: To display the Chart Options window, double-click on the chart.

NOTE: You can resize the chart using any of the chart handles.

Exercise 10:
Use the M&M data to make a chart and experiment with the various charting options.



Follow the instructions in the Chart section to create the M&M chart. An example of a bar chart is shown below.


Change the data in one of the cells on your spreadsheet. Notice how the chart changes dynamically.

Save your spreadsheet.


Instructional Applications

Use the links below to find lessons that use a spreadsheet as a tool for learning.

Magic Squares
Language Arts, Mathematics Grades 2 and 3
http://www.nzmaths.co.nz/Statistics/SSTutorial/MagicSquares.htm

Representing and Interpreting Data
Mathematics, Science, Social Studies Grades 3, 4, and 5
http://standards.nctm.org/document/eexamples/chap5/5.5/index.htm

Collecting and Examining Weather Data
Mathematics, Science Grades 3, 4, and 5
http://standards.nctm.org/document/eexamples/chap5/5.5/index.htm

Spreadsheets and Databases in Social Studies
Social Studies Grade 4
http://www2.ncsu.edu/midtech/lambert/TIME/ss5.html

Comparing State Size to Population
Mathematics, Social Studies Grade 4
http://www.mpsaz.org/resource/intermediate/population_5mathsocst.html

The Record Shop
Language Arts, Mathematics Grades 4, 5, and 6
http://www.nzmaths.co.nz/Statistics/SSTutorial/TheRecordShop.htm
Multiplicaiton Investigation
Language Arts, Mathematics Grades 4, 5, and 6
http://www.nzmaths.co.nz/Statistics/SSTutorial/MultipationInvestigation.htm

Using Data and Statistics
Mathematics, Science, Social Studies Grade 5
http://www.mathleague.com/help/data/data.htm

Let’s Go Camping
Mathematics Grades 5 and 6
http://www.mpsaz.org/resource/intermediate/camping_5math.html

Play Ball
Mathematics, Physical Education Grades 5 and 6
http://www.fi.edu/qa98/me4/me4.html

Class Picnic Activity
Language Arts, Mathematics Grade 6
http://www.dpi.state.nc.us/curriculum/computer.skills/lssnplns/SSlesson.G6.2.3.1.html

Count the Calories
Health, Science Grade 6, 7, and 8
http://www.dpi.state.nc.us/curriculum/computer.skills/lssnplns/SSlesson.G7.3.2.2.html

Grade Master Activity
Mathematics Grades 6, 7, and 8
http://www.dpi.state.nc.us/Curriculum/computer.skills/lssnplns/SSLesson.G7.2.3.3.html

The Heat Tracking Activity
Language Arts, Mathematics, Science Grade 7
http://www.dpi.state.nc.us/curriculum/computer.skills/lssnplns/SSlesson.G7.2.3.1.html

Four 4’s
Mathematics Grades 7 and 8
http://www.mpsaz.org/resource/jrhigh/4fours_7-8math.html

Astronomy Mission
Language Arts, Mathematics, Science Grade 8
http://www.dpi.state.nc.us/curriculum/computer.skills/lssnplns/SSlesson.G8.3.2.1.html

SOS: Save Our Shrimp Insustry
Language Arts, Social Studies, Mathematics, Science Grade 8
http://www.dpi.state.nc.us/curriculum/computer.skills/lssnplns/SSlesson.G8.3.2.1b.html


Algebraic Problem Solving Using Spreadsheets
Mathematics - Algebra Grades 8 - 10
http://mathforum.org/workshops/sum98/participants/sinclair/problem/intro.html


What Are the Odds?
Mathematics - Probability Grades 9 - 12
http://www.microsoft.com/education/default.asp?ID=WhatOdds

Accessing and Investigating Data on the World Wide Web
Mathematics, Social Studies Grades 9 - 12
http://standards.nctm.org/document/eexamples/chap5/5.4//index.htm

Investigating Functions Using Spreadsheets
Mathematics Grades 9 - 12
http://mathforum.org/workshops/sum98/participants/sinclair/function.html

Aviation Collision Spreadsheets
Science Grades 9 - 12
http://nasaexplores.com/show_912_teacher_st.php?id=021223125038