Introduction to Electronic Spreadsheets Using Microsoft Excel 1

Introduction to Electronic Spreadsheets Using Microsoft Excel 1

What is a Spreadsheet? l l A software/program used to assist people in performing tasks that require the use of mathematical calculations and operations. A spreadsheet is designed around a table system of rows and columns. Major part of any spreadsheet is numbers or values. Different from a word processing program where the major part is text. 2

Who Uses a Spreadsheet? l l l l Anyone responsible for providing numerical data in any form. Accountants—preparing financial documents Teachers—reporting grades and averages Managers—reporting sales, profits, & losses Tax Preparers—reports for tax purposes Payroll—employee reports Anyone needing mathematical answers 3

Edit Line Column Labels Cell Indicator Menus Various Menu Toolbars Row Labels Horizontal Scroll Bar Vertical Scroll Bar 4

Parts of a Spreadsheet RANGE 1. 2. 3. 4. Rows Columns Cells Ranges ROWS COLUMNS CELL 5

1. Rows l l A row is a horizontal section of a spreadsheet. Rows run from left to right. Rows are labeled with numbers. (1 2 3 4 5) There are 1, 048, 576 rows in an Excel Spreadsheet. Row Labels 6

2. Columns l l A column is a vertical section of a spreadsheet. Columns run from top to bottom Columns are labeled with letters. (A B C D) There are 16, 385 columns in an Excel spreadsheet. Column Labels 7

Rows & Columns (cont’d) l l On your screen you only see columns A – K Columns go from A to XFD, once at column Z, labeling begins again with AA, AB, AC. On your screen you only see rows 1 – 25. Rows go up through 1, 048, 576. 8

3. Cells l l A cell in where a row and a column meet or intersect. (the intersection of a row and a column. The smallest unit of the spreadsheet. The actual area where data is entered into the spreadsheet. Labeled with a letter and a number. (A 1 B 7 D 12 AB 978 IV 892) A 1 C 2 E 3 G 4 I 2 9

EXCEL Spreadsheet--- A 1048576 HUGE XFD 1048576 10

How Many Cells In a Spreadsheet? ? ? ROWS X COLUMNS 1, 048, 576 X 16, 385 17, 180, 917, 760 11

4. Ranges l l A range is any ADJACENT set of cells in a spreadsheet. Adjacent means all of the cells are either side by side or up and down from one another. Ranges are labeled as follows: First Cell in Range : Last Cell in Range Example: A 1: A 8 D 1: D 12 A 1: F 1 G 12: Z 12 12

Examples of Ranges Range of Cells A 1 through A 9 A 1: A 9 Range of Cells G 5 through J 9 G 5: J 9 Range of Cells C 2 through G 2 C 2: G 2 13

More Ranges l All Cells in Row 1 l A 1: XFD 1 l All Cells in Row 8 l A 8: XFD 8 l All Cells in Column C l C 1: C 1048576 l All Cells in Column IV l IV 1: IV 1048576 14

Types of Data in a Spreadsheet 1. 2. 3. Labels Values or Numbers Formulas 15

1. LABELS l l l A label is any cell that has something other than numbers in it. Labels include cells that have letters and/or special symbols. Labels cannot have mathematical operations performed on them. Usually labels are used to identify the numbers in the spreadsheet. Many times labels are column headings. Example: Student Names in a Teacher Grade book Spreadsheet would be labels. 16

Examples of Labels 17

2. VALUES l l l A value is any cell that contains only numbers Numbers can include the digits 0 -9, a period for a decimal point, and a dash for a negative sign. Values can have mathematical operations performed on them. By far the most important part of the spreadsheet Values are your “givens”—the numbers you already know. 18

Examples of Values 19

3. FORMULAS l l The reason you are doing the spreadsheet in the first place. The “Unknowns”. The values you are trying to find or answer. Example: In a teacher’s grade book, the average is the unknown. This is why the teacher does the grade book in the first place. The formulas give us our answers within the spreadsheet. 20

Examples of Formulas The User Did NOT Type These Numbers, The Spreadsheet Calculated them from Formulas Entered by the User. 21

Text, Values, and Formulas Labels Value s Formula s 22

FORMULAS (cont’d) l RULE: You must begin EVERY formula with an equals sign = Examples: =A 1+B 2 =B 27/3 =C 5 -D 11 =B 54+B 55+B 56 23

Mathematical Formulas l When programming formulas in a spreadsheet, you must follow your typical arithmetic rules learned since elementary school. l Please Excuse My Dear Aunt Sally (order of operations when solving a mathematical expression) 24

Please Excuse My Dear Aunt Sally • • • P E M D A S Items in Parenthesis Exponents Multiplication Division Addition Subtraction 25

Order of Operations (cont’d) = 10 + 5 + 15 / 5 Answer 18 = (10 + 5 + 15) / 5 Answer 6 = 10 + (5 + 15) / 5 Answer 14 26

Arithmetic Operators + * / ^ Addition =A 1+B 2 Subtraction =A 1 -B 2 Multiplication =A 1*B 2 Division =A 1/B 2 Exponentiation =A 1^B 2 27

Exponentiation (power) 3 2 Means 2 times 2 in a spreadsheet it would be typed as 2^3 4 5 4^5 and 2 10 2^10 28

29