Spreadsheet— Electronic Calculator

1. Spreadsheet—Electronic Calculator

2. Building Blocks • Columns—vertical, identified by letters • Rows—horizontal, identified by number • Cell—intersection of row and column • Cell Reference/Address—Column letter/Row number which identifies cell; i.e. A6 or S93 • Range—group of highlighted/selected cells

3. c o l u m n Range Cell Row

4. Toolbars & Formula Bar Standard toolbar—shortcut commands from all menus Formatting toolbar—change appearance of spreadsheet Formula Bar—name active cell, write/edit formulas

5. Formulas—used to calculate • = introduces the formula • Operand—the values; use cell references whenever possible • Operator—the signs (+ - * /); tells the computer what to do with the operands Examples: =A6 + B6 =C1 * D1

6. Cell References in Formulas • Relative—change/adjust to location when copied in a formula; i.e. A1 • Absolute—DO NOT CHANGE when copied in a formula; i.e. $A$1 • F4 switches a highlighted cell to Absolute • Mixed—D$1 Col is relative, No. is absolute; $D1 Col is absolute, No. is relative

7. Functions—Shortcut formulas =Function name(Range of cells) =SUM(Range) adds cells for total =AVERAGE(Range) adds and divides =MAX(Range) finds highest in range =MIN(Range) finds lowest in range =COUNT(Range) counts number of filled cells

8. Functions—Shortcut formulas =SUM(Range) =AVERAGE(Range) =MAX(Range) =MIN(Range) =COUNT(Range)

9. USE YOUR SKILLS Fit to one page, add footer; DO NOT PRINT • Purple Book p. 259; Save as PLANES Col F (Total)—calculate planes available for use; Add Col G (Total Seats) and calculate Seats available on all planes. • P. 266, Act 1; Save as ALGEBRA; Use formulas/functions to solve • P. 271, Save as CANDY; functions • P. 272, Save as UTILITY; functions

10. SPREADSHEET “TALK” • Active cell—ready for data • Cell Reference Box—tells active cell/range • Formula Bar—data/formula goes here first • Labels—text—not calculated • Values—numbers used for calculation • F1--HELP • F2--Edit • F4—Switch (relative to absolute to mixed, etc • F5—Go To • ##### cell too narrow for numeric data • “ textual data—cannot calculate • Protection—locks data in cell; cannot change

11. PAYROLL FORMULAS • Gross Pay = Hrs * Hourly Rate • Taxes = Tax Rate % * Gross Pay • Tax Rate does not change—absolute • Total Deductions =Sum (Deductions) • Net Pay = Gross Pay – Deductions ******************* • Commission = Comm. Rate % * Sales • Comm. Rate does not change—absolute • Gross Pay = Base Salary + Commission

12. CHARTS GRAPHICAL REPRESENTATION OF YOUR SPREADSHEET

13. BAR CHARTShows Comparison

14. PIE CHARTRelationship to the Whole

15. LINE CHARTShows Trend

16. CHARTING RULES • Create spreadsheet • No blank columns or rows • Create Chart • Select cells to be charted • DO NOT chart parts and the totals in the same chart • Label all information in the chart (Data Labels) • Pie charts contain only one series of data • Name your charts—(Tools menu)

17. TO COPY: • OPEN EXCEL FILE • OPEN WORD FILE • IN EXCEL, CHOOSE CHART/ SPREADSHEET TO BE COPIED • CHOOSE COPY • IN WORD, GO TO EDIT, CHOOSE PASTE SPECIAL • PASTE EITHER THE OBJECT OR LINK

18. OPEN MRHS CHARTS