 Teacher resources and professional development across the curriculum

Teacher professional development and classroom resources across the curriculum  MENU          Session 5, Part A:
Linear Relationships in Patterns

In This Part: Finding the Pattern | Spreadsheet Tutorial | Using a Spreadsheet

Because of technology, spreadsheets have progressed from a handwritten accounting practice to a powerful tool in mathematics and modeling. A spreadsheet is a grid of cells, where each cell has a column label and a row label.

This section of Part A is designed for those who don't have much, or any, experience using spreadsheets. If you are familiar with spreadsheets and spreadsheet software, please go on to the next section, "Using a Spreadsheet."

 • Cells • Formulas • Dynamic Change • Filling Cells with Formulas • Closed-Form and Recursive Rules • Graphs  Cells back to top For the purposes of this tutorial, the column heading for a cell will be lettered, starting with A, and the row heading for a cell will be numbered, starting with 1. For example, the cell in the second column and third row would be cell B3. It is a common error to reverse these labels. While a cell can only contain one piece of information, there are several types of information it can hold. To enter information into a cell, simply select that cell by clicking on it. A box will surround the selected cell at any time, and you can move to any surrounding cell by using the keyboard's arrow keys. A cell can contain text, such as a word or a title, or numbers in many different formats (whole number, decimal, or dollars and cents, among others). A cell can also contain a mathematical computation; this is typically done by placing an equal sign ("=") in the cell followed by the computation, like this: When performing computations, the equal sign is very important; without it, a spreadsheet program may interpret the computation as text. Computers use certain characters for each operation, as follows: Addition: use +. Typing "=6+7" in a cell will display the result, 13. Subtraction: use -. Typing "=8-3" in a cell will display the result, 5. Multiplication: use * (shift-8). Typing "=5*6" will display 30. Do not use the letter x for multiplication on a spreadsheet. Division: use / (forward slash, to right of period). Typing "=12/3" will display 4. Exponentiation: use ^ (shift-6). Typing "=4^3" will display 64. Important: Do not type the quotation marks used in the examples! They are not part of any computation or formula. If you type quotation marks, the spreadsheet program will automatically interpret what you type as text, and will not perform any calculations. Even though the result of the calculation is displayed, the spreadsheet remembers the details of the calculation. If you click on a cell, you can see the details of the calculation on screen.  Formulas back to top The power of a spreadsheet lies in its use of formulas. Each cell in a spreadsheet is able to contain its own formula. A formula in one cell can perform a calculation using values placed or calculated in other cells. A demonstration here will be helpful in understanding this. Suppose you wanted to make a list of consecutive numbers. You could simply enter the value of each number in the cells, or you could create a formula to accomplish the same goal. First, type the value 1 in cell A1. In cell A2, type "=A1+1" instead of typing the value. The use of A1 is a reference to cell A1, and the calculation will use whatever value was stored in cell A1. Since that value was 1, cell A2 will display "2". This can be continued in any other cell of the spreadsheet. Continue the list by entering into cell A3 "=A2+1", and entering into cell A4 "=A3+1". You should now see the numbers 1, 2, 3, and 4 listed in these cells.   Dynamic Change back to top Most spreadsheet programs include a feature that updates all values in the spreadsheet whenever one is changed. In the last example, cell A2 was defined to be 1 more than cell A1, cell A3 was defined as 1 more than cell A2, and cell A4 was defined as 1 more than cell A3. Change the value in cell A1 to 23 and observe what happens. Once a formula is entered, many values can be tested easily and quickly using this feature. Users can create a spreadsheet to model population growth, then instantly judge the long-term effects of a change. At home, this feature allows quick updating to a budget or a retirement plan. In many professions, the ability to use spreadsheets has become as important as the ability to use word processing software.  Filling Cells with Formulas back to top In the above example, you typed the same kind of formula several times. Cell A2: "=A1+1" Cell A3: "=A2+1" Cell A4: "=A3+1" All of these formulas can be interpreted as "Take the value in the cell above, and add 1." Nearly all spreadsheet programs allow the user to fill a formula over a group of cells, and the formula will adjust to the new location. To do this, the user must first highlight (select) a group of cells. Click on cell A4 and hold down the mouse button. Now, drag the mouse down to cell A10. The cells from A5 to A10 should become darkened, while cell A4 remains selected. Now, find the command "Fill Down" and select it. In a menu-driven spreadsheet, the Fill command is probably located in the Edit menu. The pattern you started in cells A1 through A4 should be continued through cell A10. More importantly, the formulas used in cells A2 through A4 should be continued, so that changing the value in cell A1 will change the entire spreadsheet. Change cell A1 back to the number 1 before proceeding.  Closed-Form and Recursive Rules back to top In Session 2, Part A we first encountered tables which could be described by several different rules. A closed-form rule is a rule that describes how to take an input and directly determine an output. For example, the rule "take the input, multiply by 4, then add 2" is a closed-form rule. In a spreadsheet, a closed-form rule can be expressed in two columns: the left-hand column of inputs, and the right-hand column of outputs. The rule just described could be expressed in a spreadsheet by entering the following formula into cell B1: "=A1*4+2" This formula takes the value in A1, multiplies it by 4, and then adds 2. To produce the information in other cells, fill down the formula from B1 to B10. In B2 you should have "=A2*4+2", and so on. A recursive rule is a rule that describes how to proceed from one input to the next. A recursive rule for the table of outputs we just created might be "The first output is 6; to get any other output, take the last output and add 4." Think about how you could enter this rule into a spreadsheet in column C before revealing the solution tip below. Tip: Enter the number 6 in cell C1, then enter "=C1+4" in cell C2. Finally, fill down the formula from cell C2 to C10. Important: Do not fill down from cell C1, since it does not contain a formula! If you filled down from cell C1, each entry in the column would be 6. Closed-form and recursive rules can work in tandem. There are many situations where one type of rule more easily describes a situation. We will be using both types of rules, so make sure you are comfortable using them both.  Graphs back to top While different spreadsheets create charts and graphs in their own way, most have an automatic graphing tool included. To create a graph, first highlight the cells you wish to use in the graph. On your spreadsheet, highlight cells A1 through B10 by clicking on A1, holding down the mouse button while you move the mouse, and releasing on cell B10. Then find the graphing tool, which may be an icon at the top of the screen that looks like a chart. Selecting this will bring up a window asking what type of chart you would like to use; this choice will depend on the situation, but for this example, select a line graph. Be sure to select a graph that places the first column of information on the horizontal axis and the second column on the vertical axis. If you have done this correctly, the graph should be a straight line containing 10 points. Most graphing tools allow you to preview the graph before it is finalized, and you may need to try several options before the graph looks the way you want it to. If you have trouble with this feature, you should consult the help function or user manuals for your spreadsheet program.   Session 5: Index | Notes | Solutions | Video

© Annenberg Foundation 2017. All rights reserved. Legal Policy