Transcript Document
Spreadsheets Sections Introduction Spreadsheet Basics Teaching with Spreadsheets Integrating Spreadsheets into Instruction Introduction A spreadsheet program is used to organize, summarize and analyze data, especially numeric data. Spreadsheets are based on the format of a bookkeeper’s ledger, organizing data into rows and columns. Formulas and functions are used to instruct the computer to “do the math.” Introduction Research studies have demonstrated that spreadsheets are useful in education for… Reinforcing mathematical skills Developing advanced skills such as simulations and modeling Graphing Practicing inference and other critical thinking skills Organizing and analyzing numeric and nonnumeric data. Spreadsheet Basics A spreadsheet looks like this: Data is organized into rows (labeled with numbers) and columns (labeled with letters). Spreadsheet Basics Each cell (box) in a spreadsheet has a unique name, usually the column letter followed by the row name. For example, a cell at the intersection of column B and row 7, would be referred to as B7. Spreadsheet Basics Cells can contain four types of information: label, number, formula, and function. A label is any word(s) or numbers that will not be used in calculations. Examples of labels are the word Name, a Social Security number, an address, and a birth date. Spreadsheet Basics A number is any numeric data that you enter that will be used in a calculation. For example, if you have just graded a test, you could enter all the test scores in a spreadsheet. Spreadsheet Basics Formulas and functions result in numbers, but are not entered as numbers. Formulas and functions tell the program how to “do the math.” In most spreadsheet programs, an equal sign (=) is entered at the beginning of a formula or function. Formulas and functions refer to data by including cell names (e.g. =C8+Z17). Spreadsheet Basics Formulas use the names of all cells that are affected by the calculation. The calculation symbols used in formulas are… + for addition - for subtraction * for multiplication / for division ^ for powers (as in 32) Spreadsheet Basics In the spreadsheet below, the formula =C3+D3 would add the numbers 442 and 403. The answer to the formula’s calculation will appear as a number in the cell where the formula is typed. Spreadsheet Basics In the spreadsheet below, the formula =C3+D3+E3+F3 was typed in cell G3, resulting in 298. Spreadsheet Basics Instead of =C3+D3+E3+F3, cell G3 could have contained a function. Functions are built-in shortcuts for certain calculations. Functions use words such as SUM and AVERAGE with a “range” of cells. The equivalent function for the formula =C3+D3+E3+F3 is =SUM(C3:F3) Spreadsheet Basics In the spreadsheet below, averages are shown for the Midterm and Final. In cell C9, the formula =(C3+C4+C5+C6)/4 OR the function =AVERAGE(C3:C6) could be used. Spreadsheet Basics Some other common functions (besides SUM and AVERAGE): =MAX( ) find the largest number =MIN( ) find the smallest number =COUNT( ) count the number of entries Spreadsheet programs have many more functions available, including statistics and common business calculations. Spreadsheet Basics Formulas and functions can be copied and pasted (or “filled”), which facilitates repeated calculation formats. For example, if the formula =C2+D2 is placed in a cell, you may want =C3+D3, =C4+D4, etc. in the rows below. Highlight the first formula, drag down the column, then choose Copy & Paste or Fill Down to duplicate. Spreadsheet Basics Numbers (including the results of calculations) can be displayed in many formats (usually available through the Format menu), for example: General (displayed as a calculator would) Currency (displayed a money, with/without $ Fixed (displayed with a given number of decimal places) Percent (multiplied by 100, shows %) Spreadsheet Basics Other formatting attributes include: Alignment (left, right or center) Bold, Italic, underline Fonts and text sizes Variable column widths Headers and/or footers Text color Borders Spreadsheet Basics Spreadsheet programs can be used to quickly create graphs and charts. In most spreadsheet programs, you can create graphs and charts by… Highlighting the data to be used. Choosing Make Chart or Insert Chart from a menu (often the Insert menu). Choosing the appropriate type of chart and labeling. Sample Charts and Graphs Line Chart Pie Chart Bar Chart Teaching with SS When using spreadsheets with young students, fill it in completely and let them change numbers. With older students, it is a good idea to begin with a template, a partially completed sheet with the formulas or functions in place. A students fill in data, they can discover how the calculations work. Teaching with SS If you are going to ask students to make a spreadsheet from scratch (after practicing with a template), be sure to make a plan on paper before beginning. Students will often need assistance in creating formulas and functions. Try to keep the data organization and calculations as simple as possible. Teaching with SS Some ideas for using spreadsheets in class activities: Personal budgets Tracking weather-related data Managing a class project budget Counting plants, bugs, etc. for science Making charts/graphs from data gathered Making future predictions/trends from current data Questions to Ponder Can spreadsheets be used in all curricular areas? Think of some examples. How can spreadsheets be used to support critical thinking skills? Should children create their own formulas and functions or should they always use a template? Does grade level matter? Should spreadsheets be used to create graphs or should children draw them?