ISTM 210 Excel Lesson 1 and 2 PDF

Preview

Summary

Notes regarding the excel topics taught during lessons 1 and 2...

Description

Lesson 1: 1.1: Workbook: An Excel file that stores all of the information, previous calculations and analyses that you may have already completed. A workbook must contain at least one worksheet. Worksheet: Contains the actual data and calculation within a workbook, and is organized into a collection of cells arranged in the form of a table; collection of cells -Workbook contains worksheets Cells: store individual pieces of data or calculations -File tab is different: has to do with working with the entire workbook 1.2: “Referencing a Cell”: using the value stored in another cell of a worksheet -Can also reference a range of cells (ex: A1:C2) -Can copy highlighted cell data and paste into another cell -Can establish a pattern with numbers in 2 cells and then continue pattern using “Fill” under editing group -- series -- autofill the cells -Excel can figure out the pattern Fill Feature: allows you to automatically populate cells in a worksheet “Freeze Panes”: allows you to lock rows and columns so that they don’t disappear when you navigate through the data table -Retain the info you want to see on screen -Highlight cell directly below and to the right of freeze (under “view) 1.3: “Format Painter”: Used to apply the format from one cell to another 1.4: -Sorting and Filtering

Lesson 2: 2.1: References and Calculations -Calculations: -Always start with equal sign -Can type in cell or formula bar *Built in functions and formulas -Referencing: -Can type in address or click cell you want to reference -Calc updates when you change the referenced cell -Can reuse formula from one cell to another (past to another) -Excel reuses formula but changes the cells referenced (reletters) Relative Cell Reference: updates row/column depending on how you copy and paste Absolute Cell Reference: allows you to keep column or row (or both) constant when you copy and paste -Keep column constant: $ in front (Because column = letter portion) -Keep row constant: $ before number (Because row = number portion) Note: Excel may be the most powerful calculator you will ever use

Note: “#Value!” means something is wrong Note: When copying column to column it doesn’t matter if you make it relative or absolute 2.2: Summary Statistics Functions Function Wizard: (1) allows you to enter or select the inputs (called arguments) for the function, (2) provides a description of the function and each argument, (3) lists the function result based on the inputs you enter (Fx- next to equation bar) Argument: Info Excel needs to perform a function -Colon to select range (ex: A1:A7 -- would be 7 arguments) Ex: FUNC(argument, argument,...) “SUM function”: calculates the sum of the numbers in two columns COUNT function: counts the number of cells in a range of cells that contain numbers COUNTA function: counts the number of cells in a range of cells that are not blank AVERAGE function: calculates the simple avg of a set of numbers MAX function: returns the largest value in a set of numbers MIN function: returns the smallest number in a set of numbers 2.3: Financial Functions Time Value of Money: underlying concept behind financial functions in Excel -Must remember to divide annual rate by 12 to get monthly rate -Note: optional arguments are shown with brackets around them -Show more precision to get more decimals RATE: (nper,pmt,pv,[...]) calculate the int rate earned for an investment given the number of payments made as part of the investment, payment amount, and current value of the investment EFFECT: (nominal_rate,npery) calc the (real/effective int rate) annual percentage rate for an interest rate given the number of times per year int is charged (accounting for compounding int) NPER: (rate,pmt,pv,[...]) calc the number of payments that will be made to pay off a loan given the int rate, pymt amt and original loan amt PV: (rate,nper,pmt,[...]) calc the current value (accounting for compounding interest) of an investment given the int rate, number of payments to be made and the amt of the payment FV: (rate,nper,pmt,[...]) calc the future value of an investment given the int rate, number of payments and payment amt 2.4: Formula Auditing -Idea behind formula auditing = people make mistakes -Allow us to look at work and track down mistakes Trace Precedents: arrows Trace Dependents: arrows to cells affected by highlighted cell “Show formulas”: toggle; shows formulas entered...