An Introduction to Spreadsheets Menu Objectives n Spreadsheet

An Introduction to Spreadsheets

Menu Objectives n Spreadsheet n Purposes & Advantages n Layout of Spreadsheets n Data n Planning n Review n

Objectives n n n Describe how an electronic spreadsheet program works Compare the merits of an electronic spreadsheet with those of a paper spreadsheet Explain how fiscal forecasting is done with electronic spreadsheets Use a spreadsheet application List ways by which graphs can be produced with a computer

What is a Spreadsheet? A 1 2 3 4 5 6 7 8 9 B C D E F

A Spreadsheet is a calculating tool which is divided into columns and rows, to help people plan, manage and present results.

Paper Spreadsheet The first type of spreadsheet is a paper spreadsheet. You need a piece of paper divided into columns and rows, a pencil and a calculator to work the spreadsheet. Everything is done manually. All changes must be done by erasing and recalculating.

Electronic Spreadsheet With an electronic spreadsheet, the computer works as if it were a calculator and a pencil. You simply keyboard the information into the computer, and the computer does your calculations for you. Better still, if you make a mistake typing the figures, just correct the figures and the computer recalculates the total automatically.

The Spreadsheet Helps You: Calculate Numbers 2. Organize information into columns and rows 3. Create a framework of titles, labels, values, and formulas 1.

Spreadsheet Advantages n Greater speed and accuracy n The software does all the calculations n The spreadsheet can be recalculated automatically after entering new data n Forecasting can be done by entering “What if? ” information n Can create charts

Columns and Rows An electronic spreadsheet consists of columns and rows. The columns run vertically --- top to bottom. The rows run horizontally --- left to right. Each column is labeled with a letter. Each row is numbered.

Columns: Vertical Top to Bottom Labeled with a Letter A B C D E

Rows: 1 2 3 4 Horizontal Left to Right Labeled with a Number

Cells: The intersection of a row and a column is called a CELL. A B C D 1 2 3 Cell B 3 4 5 6 Cell D 6

The number of cells in a spreadsheet depends on the program you are using. A cell is identified by the column label then the row label. The electronic spreadsheet has many more cells than can fit on the screen at once. To see cells that are not on the screen, you move or scroll to the left, right, up or down with the arrow keys on the keyboard or the mouse. The cell that is selected or highlighted is called the active cell.

In the spreadsheet below, cell A 1 contains the number 3 and cell c 4 contains the number 2. Use the spreadsheet below to answer the questions. 1 2 3 4 A 3 1 2 0 B 5 1 4 1 C 7 2 6 2 D 9 3 8 3 1. How many columns are contained in the spreadsheet? 2. How many rows are contained in the spreadsheet? 3. What are the contents of the following cells? A. B 2 ___ B. C 4 ___ C. D 1 ___ D. A 1 ___ 4. Which cells have the value of 3 in them? 5. How many cells are contained in a spreadsheet that has 3 columns and 4 rows?

Data The three types of information used in a spreadsheet are: 1. LABELS 2. VALUES 3. FORMULAS Data may also be formatted using dollar signs, decimals, bold, underscore, etc. . Formatting varies with each type of information.

Labels n Are words and numbers not used in calculations. n Are left justified n Are used to describe what the values in the cells represent n May also be used for the title of the spreadsheet Examples: Test #1 2002 Totals Names

Values n Are numbers used in calculations n Are right justified n Represent quantity Examples: 23, 43, 34. 56, 20. 33, 10

Formulas n n n Are equations Stored in the computers memory – we cannot see the formula, only the result Symbols are used to let the computer know a formula is being entered (Excel uses an = symbol) Can be used with a range (Range is a partial row or column of adjacent cells). Example: (A 4: A 9) or (B 4: G 4) Follow the order of mathematical operators – parentheses, exponents, multiplication and division, addition and subtraction

Examine the spreadsheet below. This spreadsheet contains a student’s test score for four subjects, the average test score for each subject, and the overall average. The title of the spreadsheet is GRADES. A B 1 C D E F GRADES 2 3 Subject Test 1 Test 2 Test 3 Test 4 Average 4 Math 88 92 98 86 91 5 Science 83 85 86 94 87 6 English 78 86 82 90 84 7 History 89 87 83 85 86 8 9 Overall Average: 1. Is the content of cell C 5 a value or a label? 2. Is the content of cell C 3 a value or a label? 3. What do the values in cells B 7, C 7, D 7, and E 7 represent? 4. What do the values in column F represent? Be specific! 87

Formulas A formula can use the values in other cells to calculate new values. Perhaps the value in cell B 4 was 10, and the value in cell B 7 was 5. Then the formula =B 4 + B 7 would equal the value 15. The formula =B 4 -B 7 would equal 5. The computer uses the order of operations when calculating formulas. 1. Parenthesis 2. Exponents 3. Multiplication and Division Please Excuse My Dear Aunt Sally 4. Addition and Subtraction A mnemonic may help you remember the order of operations: Please Excuse My Dear Aunt Sally

Answer the following Problems: A B C D 1 3 5 7 9 2 1 1 2 3 3 2 4 6 8 4 0 1 2 3 Use the values displayed in the spreadsheet to find each new value. + means ADD - means SUBTRACT Please Excuse My Dear Aunt Sally * means MULTIPLY / means DIVIDE 1. A 1 + B 1 = ______ 7. D 1 – D 3 = ______ 2. A 3 + B 2 *C 1 = _______ 8. C 1 – C 2 + 12 = _______ 3. B 1 – (B 2 + B 4) = ______ 9. (C 1 – B 1) + D 1 = ______ 4. B 3 * 1 = _______ 10. D 3 / B 3 = ______ 5. (D 3 / C 4) / B 3 = _______ 11. A 1 * D 1 / D 2 = _______ 6. 13 + B 3 * C 2 = _______ 12. A 1 * (D 1 / D 2) = ______ 13. (A 1 +A 2 +A 3) / D 4 * C 4 = ______ 14. (D 1 + C 2 + B 3) / (A 1 * B 1) = ____

Planning A Spreadsheet **It’s a good idea to plan a spreadsheet on paper before you begin on a computer** – The first step is to determine what new data the spreadsheet is to generate. – The second step is to decide what data to include. – Third step is to decide what calculations must be performed. – Finally, the fourth step is to determine how the spreadsheet is to appear on the computer screen.