Transcript Document
What is a Spreadsheet? • A spreadsheet consists of the following items – A worksheet divided into rows and columns • 256 vertical columns & 65,535 horizontal rows • Columns identified by letters (A, B, C, and so on) • Rows are numbered (1, 2, 3, …) – Cells An intersection of a row and column • Identified by their column letters followed by their row numbers – Numbers – Labels – Formulas Anatomy of the Window Title bar Workbook name Menu bar Ribbon Formula bar Active cell Column headings Row heading Gridlines Scroll bars Status Bar Current sheet Sheet tabs Entering Information into a cell • Select cell where you want to type data • Type a number, label, or formula • Do any of the following – – – – Press Enter Click Green Check Mark next to formula bar Press an arrow key to select a different cell Click a different cell Excel Concepts • Cell reference how we refer to a specific cell. – A10 or Data!A10 or [Book2.xls]Sheet1!A10 or C:\MyData\ [Book2.xls]Sheet1!A10 • Range A rectangular group of one or more cells – Referenced by top left cell separated from bottom right reference by a colon. More Concepts • Selection – Range of cells, highlighted – Fill handle • Formatting – The manner in which a cell entry is displayed can be changed by formatting the cell. – Font, background, number of decimals, scientific notation, horizontal and vertical positioning, borders Examples • Filling in a Series of Numbers or a list of dates • Entering and Copying a Formula – All formulas begin with (=) – 3 + 4 * 2 11 or 14? – Refer to values in cells • Copy vs. Move formula Arithmetic Operator Order • • • • Negation (-) Exponentiation (^) Multiplication and division (*,/) Addition and subtraction (+,-) Formatting your worksheet • Manually formatting your cells – – – – – – Number Alignment Font Boarder Patterns Protection More Formatting • Conditional Formatting • Removing Formatting – Eraser too (Under editing)Clear Formats • Adjusting column widths and row heights – With the mouse Common Formula Calculations Operator What it does Example Result + Addition =5+3.4 8.4 - Subtraction =54.2-2.1 52.1 * Multiplication =1.2*4 4.8 / Division =25/5 5 % Percentage =42% 0.42 ^ Exponentiation =4^3 64 = Equal =6=7 False > Greater than =7>2 True < Less than =9<8 False >= Greater than or equal to =45>=3 True <= Less than or equal to =40<=2 False <> Not equal to =5<>7 True & Text concatenation =“Bo the ” & “Cat” Bo the Cat Using cell references in formulas • Referencing a single cell • =B5+B6 • AutoSum Σ – Sum, Average, Count, Max, Min • Referencing two or more cells – Contiguous range =SUM(D3:D5) – Noncontiguous range SUM(D3,G5,X7) Copying formulas • Excel changes the formula cell reference automatically when for each row or column – Ctrl+C then Ctrl+V • Editing a formula • Ctrl+` to show all formulas • Absolute, Relative, & Mixed References – $A$1, $A1, A$1, A1 Naming cells and ranges • Name box • Jumping to a named cell (downwardpointing arrow) • Acts as an absolute reference Picking a Function to Use • Entering a Function Directly – Example =Sin(A1), =Pi(), =Degrees(A1), =Radians(A1) • Insert function button – Function Palette • Entering Formulas by Pointing Finding where a formula gets its data • Formula Auditing toolbar – Tracing Precendents – Tracing Dependents – Adding Comments Printing a Worksheet • Using Print Preview • Printing part of a worksheet – Highlight cells that you want to print – Page LayoutPrint AreaSet Print Area • Page Setup – Under print preview • Portrait, Landscape, Fit to … – Margins – Header/Footer Paste Special • RAND() • Paste Special Modifying Worksheets • Insert/Delete rows and columns • Link worksheets w/ formulas • Split a worksheet (under view tab) Formulas • Editing a formula • Ctrl+` to show all formulas • Absolute, Relative, & Mixed References – $A$1, $A1, A$1, A1 – Using named cells in formulas (names are absolute references) Naming Cells using labels • InsertNameCreate • Names are not case sensitive Picking a Function to Use • Insert function button – Function Palette • Function Arguments – – – – Commas separate each argument =Pi() =COS(Pi()) =SQRT(A2/2) =ROUND(A2,2) =SUM(A1:A10) or =SUM(A1:A10,B3,B4) • Entering Formulas by Pointing Trig Functions • Trig functions (always in radians) – RADIANS, DEGREES, SIN, COS, ATAN, … • Exponential Functions – EXP, LN, LOG • Rounding Functions – ABS, CEILING, EVEN, FLOOR, INT, MROUND, ODD, ROUND, ROUNDDOWN, ROUNDUP, TRUNC Errors in Formulas • ###### Column is too narrow • #DIV/0! Formula divides a number by zero. Black cells have a value of zero • #NAME? Formula containes a function name or cell that Excel does not recognize • #REF! Formula refers to a cell that is not valid • #VALUE! Formula refers to a cell that Excel cannot use in calculation • Circular Reference Formula refers to the cell containing the formula