Exploring Excel 1 Introduction to Microsoft Excel What

Exploring Excel 1. Introduction to Microsoft Excel: What is a Spreadsheet? What else can Excel do? Calculations – Inventory Lists – Scheduling Cost Analysis – Task list Database Financial plans & reports Charts – Graphs, or a tool for decision making www. cookie. Setton. com 1

www. cookie. Setton. com 2

www. cookie. Setton. com 3

Once a chart is created, options within a command www. cookie. Setton. com 4

Excel Help | Page views HELP ? ? Normal – page layout – Page break preview www. cookie. Setton. com - Zoom + 5

Spreadsheets Objectives (1 Basics of 2) • Spreadsheet is a computerized ledger. A spreadsheet is generic term; worksheet is an Excel term • Divided into Rows and Columns • Cell References • Constants--entries that do not change A B C D……. 256 Columns Cell name 2007 – 16, 384 Active cell Numeric constant – right aligned Text constant - Left aligned 1, 2, 3 - 65, 000 Rows 2007 – 1 million Worksheets within the Workbook. Sheet tabs can be renamed by right clicking A workbook is the entire file and a worksheet is the individual page within the workbook www. cookie. Setton. com 6

My first Spreadsheet Enter data, starting in B 1, tab to C 1, D 1, E 1, F 1 and enter to add balance of information. Click into B 5 to add formula for adding column B. www. cookie. Setton. com 7

Creating a spreadsheet with formulas • • • Enter data below Click into cell B 9 to enter formula, =sum(B 4: B 8) and press enter Continue entering formulas in C 9, D 9, E 9 & F 9 Click into G 4 to enter formula, =sum(B 4: F 4) and press enter Change B 4 to 276. Worksheet recalculates automatically after change To change which cell is active, use the arrow keys, click the desired cell, or use the keyboard shortcuts from the following table. Arrows: One cell in the direction of the arrow Home: Beginning of current row Ctrl+Home: Beginning of the worksheet Ctrl+End: Bottommost, rightmost non-blank cell in sheet Page Down: Down one screenful Page Up: Up one screenful Alt+Page Down: Right one screenful Alt+Page Up: Left one screenful Enter: To beginning of next row (or beginning of data range in next row) Tab: One cell to the right Shift+Tab: One cell to the left Ctrl+Backspace You can press to bring the active cell into view if you lose track of it. www. cookie. Setton. com 8

Creating Formulas & Functions • All formulas & functions begin with an equal “ = “ sign. Formula is an equation that performs a calculation. A function is a predefined formula. • Addition: (formula) =A 2+A 3+A 4+A 5 • (function) =SUM(A 2: A 5) • Average (formula) =(A 2+A 3+A 4+A 5)/4 • (function) =AVERAGE(A 2: A 5) • Subtraction: (formula) =A 2 -A 3 ________________________________________ Range: A series of consecutive numbers. Eg. A 2: A 5 represents A 2, A 3, A 4, A 5 Hierarchy of Operations – Order of precedence of the operators is as follows: ^ (caret) exponentiation *(asterisk) multiplication /(slash) division (Multi. & Div. are performed in order reading from left to right) +(plus) addition -(minus)subtraction (Also performed in order from left to right) When parentheses ( ) surround parts of a formula, however, the operation inside the parentheses takes precedence. Tip: this sentence is a helpful mnemonic device for remembering the order. Please parentheses Excuse exponents My multiplication Dear division Aunt addition Sally subtraction www. cookie. Setton. com 9

More Formulas & Functions q q q Highest amount in a range……=MAX(A 2: A 10) Lowest amount in a range…. . =MIN(A 2: A 10) Count of values in a range …. . =COUNT(A 2: A 10) Count of any data in a range…=COUNT(A 2: A 10) IF FUNCTIONS enables you to build a logical formula. The IF function compares the value in a cell to a test that you specify, then makes a decision as to whether the value meets the test. True or false………. =IF(logical_text, if_true, value_if_false) www. cookie. Setton. com 10

1. Explore the spreadsheet; create spreadsheet, enter data, make corrections, save and close. Formulas and quick sums; create formulas, quick sum, copy & paste, use fill feature. (ss 02) 2. Merge & Center, adjust columns, formulas for average and complex formulas, format data, cell alignment, numeric formats, auto format, Insert/Delete rows & columns. Display formulas. (ss 07 -10) 3. Review (SS 11) Absolute/relative referencing (SS 15, UMC theater), enhance data, add borders(SS 14). Working with ranges & range names, use functions 4. Name range, Min & max functions, Average & Average. A function, average for grades with different values, count & count. A functions (ss 20 -23) 5. Working with large spreadsheets: freeze panes, sorting and filtering, hide & unhide columns Page set up, header & footer, print area settings, prepare for print, scaling (ss 24 -26, freeze) 6. Charts. Create inventory sheets using pictures. (ss 28 -29) Online class Lesson 3 – SS 16 – quick sum over multiple cells, . ss 16 B – moving information on another worksheet. SS 19 – more complex formulas – adding a new assumption in projections. SS 21 – statistic functions; max, min, averagea, count. SS 22 – logical function – IF (evaluating conditions). Lesson 4 – compare with IF function which is 2 options. Vlookup & Hlookup – return information based on data store in a lookup table. The function attempts to match a value in one of its arguments to values in the 1 st column of the lookup table www. cookie. Setton. com 11