An Introduction to Spreadsheets

1. An Introduction to Spreadsheets

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

3. Objectives • 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

4. What is a Spreadsheet?

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

6. 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.

7. 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.

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

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

10. 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.

11. Columns: Vertical Top to Bottom Labeled with a Letter

12. Rows: Horizontal Left to Right Labeled with a Number

13. Cells: The intersection of a row and a column is called a CELL.

14. 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.

15. In the spreadsheet below, cell A1 contains the number 3 and cell c4 contains the number 2. Use the spreadsheet below to answer the questions. • How many columns are contained in the spreadsheet? • How many rows are contained in the spreadsheet? • What are the contents of the following cells? A. B2 ___ B. C4 ___ C. D1 ___ D. A1 ___ • Which cells have the value of 3 in them? • How many cells are contained in a spreadsheet that has 3 columns and 4 rows?

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

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

18. Values • Are numbers used in calculations • Are right justified • Represent quantity Examples: 23, 43, 34.56, 20.33, 10

19. Formulas • 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: (A4:A9) or (B4:G4) • Follow the order of mathematical operators – parentheses, exponents, multiplication and division, addition and subtraction

20. 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. • Is the content of cell C5 a value or a label? • Is the content of cell C3 a value or a label? • What do the values in cells B7, C7, D7, and E7 represent? • What do the values in column F represent? Be specific!

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

22. Answer the following Problems: Use the values displayed in the spreadsheet to find each new value. Please Excuse My Dear Aunt Sally + means ADD - means SUBTRACT * means MULTIPLY / means DIVIDE • A1 + B1 = ___________ • A3 + B2 *C1 = _______ • B1 – (B2 + B4) = ______ • B3 * 1 = _____________ • (D3 / C4) / B3 = _______ • 13 + B3 * C2 = _______ • D1 – D3 = ___________ • C1 – C2 + 12 = _______ • (C1 – B1) + D1 = ______ • D3 / B3 = ____________ • A1 * D1 / D2 = _______ • A1 * (D1 / D2) = ______ 13. (A1 +A2 +A3) / D4 * C4 = ______ 14. (D1 + C2 + B3) / (A1 * B1) = ____

23. 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.