Spreadsheet
Basics
|
What is a Spreadsheet? A spreadsheet is a powerful tool for gathering and storing data, manipulating it and presenting it in various ways. It is primarily designed to handle numeric data. A spreadsheet file consists of a grid of rows and columns. At the intersections of rows and columns are cells into which the data are entered. |
 |
||||||||
|
Parts of a Spreadsheet The diagram at the right illustrates the component parts of a spreadsheet document's worksheet. A spreadsheet document may have several worksheets, and the information from one worksheet can be referenced in another. For example, you might have a spreadsheet to track weather information for an entire month. It could contain 5 worksheets labeled, Week 1, Week 2, Week 3, Week 4, and Summary. The worksheets are indicated by tabs across the bottom of the spreadsheet window. When you create a new spreadsheet file, you automatically have a spreadsheet with 3 worksheets, labeled Sheet 1, Sheet 2, and Sheet 3. You can rename these tabs by clicking on the tab itself and typing a new name.
|
|
||||||||
|
Why Use a Spreadsheet in the Classroom? Spreadsheets are are used to manipulate mathematical data, analyze the data and to draw conclusions by using mathematical formulas that direct the spreadsheet to carry out math or logical processing. Such processing, done manually, would be time-consuming and error prone. Many spreadsheet functions are built-in, such as the sum and average functions, but users can also create their own formulas by expressing in mathematical terms the functions they want calculated. Additionally, some of the more difficult concepts, such as mean and median, can be reinforced when data is viewed on a spreadsheet. |
 This example shows a worksheet that contains data for a bag of Skittles. The actual spreadsheet has been enhanced with colors in the row and column, as well as in the text used for the labels of the colors. This is simply a tool to record the raw data. |
||||||||
|
A spreadsheet is relatively easy to use once you have mastered the concept of spreadsheets. For students in grades 4 and above, the use of Excel to manipulate date in a spreadsheet, or to create and enter data independently is a realistic goal. A spreadsheet is often correlated with a hands-on inquiry activity or a project-based learning activity. Because the medium is digital, students who use the spreadsheet to solve a numerical problem can easily manipulate solution sets and predict outcomes. But there are also software packages which can be used to perform mathematical functions. These include programs such as Tom Snyder's Graph Club or Tom Snyder's Graph Master which provide ready-made lessons for teaching spreadsheets. |
|||||||||
|
Teachers will find the best ideas for integrating spreadsheets in to the curriculum, in their own program of study—gathering, organizing and analyzing numerical data are essential literacy components at every grade level. Students at all grade levels like to count, measure and time. Additionally, data sets to support most K-12 studies are freely available in the library media center and online. |
|||||||||
|
Using Spreadsheets |
|||||||||
|
|
|||||||||
|
Enter Text Text is entered into a spreadsheet cell by clicking the mouse into the cell and simply typing. You can change the font color or the cell color or both to get a special effect in the appearance of your worksheet. When you wish to do a calculation in a spreadsheet cell you always begin by pressing the Equals Sign on the keyboard. If you start entering text, the spreadsheet will assume you are entering text. Text will flow across the row from left to right. If you have text in column A it will span across columns B, C, D etc. To have the text "wrap within the cell, click on the cell, choose Cells from the Format menu, and click on the Alignment Tab. Check the box labeled Wrap Text. |
 |
|
A number can be typed directly into any cell. It can then be referenced by its cell address. For example, in the spreadsheet example below cell B4 contains the value 10. Cell B9 contains the value 8. With these values already entered, it is easy to create formulas to perform mathematical functions on the information contained within the cells. |
|
 |
To begin the formula in this example, move the mouse to cell H4 and click. Press the equals sign on the keyboard and then move the mouse to cell B4 and click. You will see the equals sign and B4 contained within the cell labeled H4. |
 |
Next press the plus sign on your keyboard and then move the mouse to cell B9 and click.
This additional information is now added to cell H4 and s\you should be able to see how the formula is being constructed within that cell. |
 |
Finally, press the Enter key or click on the green check mark on the data entry bar at the top edge of the worksheet and the program will automatically calculate the result. |
|
Adding Columns of Numbers Excel has a wonderful feature for adding a series of numbers. Notice in Figure A that you have a column of contiguous numbers. To add these quickly, click on the Sigma symbol on the Toolbar - a Greek letter resembling a backward 3. This will SUM the set of contiguous numbers, and will do this on a vertical or horizontal set of numbers. In Figure B you see what the column of numbers looks like when you have clicked on Sigma. If the highlighted numbers are what you wish to add together, simply press enter, or click on the green arrow on the data entry bar. |
|
|
|
|
|
|
|
|
Calculating Averages An AVERAGE, much like a SUM, is considered to be a FUNCTION in a spreadsheet file. It performs a special task quickly and can return the result to a specified location. |
|
|
To add the function to average scores in this example, move the mouse to cell G5 and click. |
 |
|
Move the mouse to the data entry bar and click on the equals sign to the immediate right of the green check mark. You'll see a gray box appear on the left with the word SUM and a down facing black triangle Click on that triangle and scroll through the list of FUNCTIONS to the word AVERAGE. (See Figure A below) When you release the mouse button a gray box will appear in the upper left-hand corner of your screen. You can click on this box and drag it so that you are able to see the data in your worksheet. (See Figure B below) Notice that the values in the text box labeled Number1 show the range of cells from B5 to F5. You should also notice that values for each member of this set are listed to the right of the text box. Simply click on the OK button and your scores will automatically be averaged! |
|
|
|
|
|
Once the average is entered into cell G5 you will need to copy it into the other cells in that column. Click on cell G5 and notice the tiny black square in the lower right hand corner. Place your cursor on this block and drag straight down to cell G9. When you release the mouse, each cell in the column will fill will the average of the numbers on its row. Pretty cool! |
 |
|
IF-THEN Statements An IF-THEN statement allows you to apply a rule to a specific cell if certain criteria within the worksheet are met. For example, if you are keeping track of student grades in a gradebook, you may wish to convert values to letter grades for reporting purposes. |
|
|
|
In this example, a student has earned an average of 98 in Math for the current semester. This is shown in cell D4. The worksheet has compared this value to a set of rules, and has returned the letter A to cell D5. The formula for making this conversion is located within cell D5. It is written as follows: =IF(D4>=90, "A", IF(D4>=80, "B", IF(D4>=70,"C",IF(D4>60,"D","F")))) |
Let's take a closer look at this formula: |
|
|
=IF(D4>=90, "A", IF(D4>=80, "B", IF(D4>=70,"C",IF(D4>60,"D","F")))) The first thing you see is the equals sign indicating this is a calculation. Next you see the word IF ... so far so good. The next part, (D4>=90, "A", takes the value in cell D4, which right now happens to be 98, and compares it to 90. It decides if the value in D4 is greater than or equal to 90...and if it is, THEN it places the letter A into cell D5 It is important to notice the quotation marks around the letter A. This is critical, because without them, the worksheet would think that the A was a variable that needs to be processed. You could just as easily have written the word Fantastic with the quoatation marks. If the value in cell D4 does not meet the criteria in this first comparison, it then moves along to the next criteria which reads, IF(D4>=80, "B", This process continues until the value in cell D4 meets the criteria of one of the sections of the formula in cell D5. Notice that each time the word IF is used in the formula it is followed by an open parenthesis. The entire formula contains 4 open parentheses, and thus it requires 4 closed parentheses at the end of the formula. One thing not included in this formula is the consideration of rounding up the final averages. If a student ends the semester with an 89.6 average, this would still be returned as a B because 89.6 is not equal to or greater than 90. You could reset your criteria in the IF THEN statement to allow for this if you wanted to do so. |
|
Excel is a powerful tool for collaboration. In a networked classroom, it is possible to designate one worksheet (or Excel document) as Shared. Others students on networked computers, anywhere, can then add data to it! This is an exciting way to introduce remote collaboration as early as elementary school.