4-MS-Excel.pdf

Spreadsheet ▪ Spreadsheet is a row and column arrangement of data. ▪ A spreadsheet looks much like a page from a financial journal. ▪ A modern spreadsheet file consists of multiple worksheets (sheets) that make up one workbook. Microsoft Excel ▪ Spreadsheet is used to ev...

Spreadsheet ▪ Spreadsheet is a row and column arrangement of data. ▪ A spreadsheet looks much like a page from a financial journal. ▪ A modern spreadsheet file consists of multiple worksheets (sheets) that make up one workbook. Microsoft Excel ▪ Spreadsheet is used to evaluate, calculate, manipulate, analyze and present numeric data.  What is Excel? Excel definition: a software program created by Microsoft that uses spreadsheets to organize numbers and data with formulas and functions. Excel analysis is ubiquitous around the world and used by businesses of all sizes to perform financial analysis.  What is Excel used for? Excel is typically used to organize data and perform financial analysis. It is used across all business functions and at companies from small to large. Data entry Data management Accounting Financial Analysis Charting and graphing Programming Time management Task management Financial Modeling Customer relationship management (CRM) Almost anything that needs to be organized! Features of Microsoft Office Excel Ribbon Formula Bar Active Cell Sheet Tabs Features of Microsoft Office Excel Name Box Column Heading Row Numbers Features of Microsoft Excel Worksheet – a grid composed of rows, columns, and cells. Cell – the point at which a column and a row intersect or meet./ intersection of row and column Active Cell – is the cell in which you are working currently and is surrounded by a thick border. Cell Reference or Cell Address – the name of each cell represented by the column letter and the row number. Features of Microsoft Excel QuickAccess Toolbar-gives you fast and easy access to the tools you use most often in any given Excel session Screen Tip– small, onscreen boxes that display descriptive text when you rest the pointer on a command or control. Dialog Box Launcher– arrow at the lower right corner of the ribbon tabs Features of Microsoft Excel Workbook – composed of multiple number of worksheets. Workbook contains three worksheets by default (Sheet1, Sheet2 and Sheet3). Name Box – displays the location, or "name" of a selected cell (active cell). Range – is the selected group of cells within the worksheet. Formula Bar – feature in MS Excel that allow the users to enter or edit data, a formula, or a function that will appear in a specific cell. Types of Data in a Spreadsheet Label – alphabetical text and aligns at the left side of the cell. It simply describes the data in cells and is not use in any calculations. Value – is a number entered in a cell and aligns at the right side of the cell. Usually the data that are manipulated or use in calculations. Formula – equation or expression that performs a calculation. Function – built-in formula that is a shortcut for common calculations in spreadsheet. Types of Data in a Spreadsheet Operand– identify the values to be used in the calculation, an operand can be a constant value, or a variable such as a cell reference, a range of cells, or another formula.. Constant – is a number or text value that is entered directly into a formula. Variable – is a symbol or name that represents something else, which can be a cell address, a range of cells, and so on. Calculations, Formulas & Functions in Excel ▪ One of the most powerful features in Excel is the ability to calculate numerical information using formulas. ▪ Excel can add, subtract, multiply, and divide emulating the calculator’s operation. ▪ Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents. Calculations, Formulas & Functions in Excel ▪ All formulas in Excel must begin with an equal sign (=). ▪ Commonly, you will use cell addresses or cell references to create a formula. ▪ Using cell references will ensure that the formulas are always accurate, because you can change the value of referenced cells without having to rewrite the formula. Ribbon, Formula Bar and Command options CUT, COPY & PASTE Order of Evaluation Order of Evaluation – determines the sequence of calculation when multiple operators are involved. ▪ Multiplication and Division are performed before addition and subtraction. ▪ Calculations are performed from the left side of the formula to the right side. ▪ Use the parentheses () to change the order of evaluation. Calculations enclosed in parentheses are performed first. Types of Cell References A. Relative Cell Reference ▪ By default, all cell references are relative references. ▪ When copied across multiple cells, they change based on the relative position of rows and columns. ▪ Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns. ▪ For example, if you copy the formula =A1+B1 from row 1 to row 2, ▪ the formula will become =A2+B2. 16 Relative Cell References 17 Types of Cell References B. Absolute Cell Reference ▪ Value of a cell referenced in a formula remain constant when copied. ▪ An absolute reference is designated in a formula by the addition of a dollar sign ($). ▪ $ can precede the column reference, the row reference, or both. 18 Types of Absolute Cell References 19 Absolute Cell References 20 Function ▪ Function is a predefined formula that performs calculations using specific values in a particular order ▪ Excel includes many common functions that can be useful for quickly finding the sum, average, count, maximum value, minimum value for a range of cells and etc. ▪ A function must be written a specific way, which is called the syntax. Parts of Function ▪ The basic syntax for a function is an equal sign (=), the function name (SUM, for example), and one or more arguments. ▪ Arguments contain the information you want to calculate. Working with Arguments ▪ Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. ▪ Single argument or multiple arguments can be included, depending on the syntax required for the function. ▪ Multiple arguments must be separated by a comma. Working with Arguments Using Multiple Arguments Using Single Arguments Common Used Math Functions FUNCTION DESCRIPTION SYNTAX Returns the absolute value of a number. The ABS() absolute value of a number is the number ABS(number or cell) without its sign SUM() Adds all the numbers in a range of cells. SUM(number1,number2,...) Multiplies all the numbers given as arguments PRODUCT() and returns the product. PRODUCT(number1,number2,...) Returns the integer portion of a division. Use QUOTIENT() this function when you want to discard the QUOTIENT(numerator, denominator) remainder of a division. Returns the remainder after number is MOD() divided by divisor. The result has the same MOD(number, divisor) sign as divisor. Common Used Math Functions FUNCTION DESCRIPTION SYNTAX Returns the result of a number raised to a POWER() power. POWER(number, exponent) SQRT() Returns a positive square root. SQRT(number or cell) Returns the factorial of a number. The FACT() factorial of a number is equal to 1*2*3*...* FACT(number or cell) number. Rounds a number to a specified number of ROUND() digits. ROUND(number, decimal places) Behaves like ROUND, except that it always ROUNDUP() rounds a number up. ROUNDUP(number, decimal places) Behaves like ROUND, except that it always ROUNDDOWN(number, decimal ROUNDDOWN() rounds a number up. places) Common Used Math Functions FUNCTION DESCRIPTION SYNTAX Truncates a number to an integer by TRUNC.MATH(number, number TRUNC() removing the fractional part of the number. of precision) Rounds number down, toward zero, to the FLOOR.MATH(number, FLOOR() nearest multiple of significance. significance) Returns number rounded up, away from CEILING.MATH(number, CEILING() zero, to the nearest multiple of significance significance) Returns number rounded up to the nearest EVEN() even integer. EVEN(number or cell) Returns number rounded up to the nearest ODD() odd integer. ROUNDUP(number, decimal places) Returns a random integer number between the numbers you specify. A new random RANDBETWEEN(starting value, RANDBETWEEN() integer number is returned every time the ending value) worksheet is calculated. Common Used Statistical Functions FUNCTION DESCRIPTION SYNTAX Counts the number of cells that contain COUNT() numbers and counts numbers within the list COUNT(number1, number2, …) of arguments. Counts the number of cells that are not COUNTA() empty and the values within the list of COUNTA(number1, number2, …) arguments Returns the average (arithmetic mean) of AVERAGE() the arguments. AVERAGE(number1, number2, …) Returns the median of the given numbers. MEDIAN() The median is the number in the middle of a MEDIAN(number1, number2, …) set of numbers. Returns the most frequently occurring, or MODE() repetitive, value in an array or range of data. MODE(number1, number2, …) Common Used Statistical Functions FUNCTION DESCRIPTION SYNTAX Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. Order: ▪ 0 (zero) or omitted, Microsoft Excel RANK() RANK(number, ref, order ) ranks number as if ref were a list sorted in descending order. ▪ nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order. Returns the smallest number in a set of MIN() values. MIN(number1, number2, …) Returns the largest number in a set of MAX() values. MAX(number1, number2, …) Common Used Text Functions FUNCTION DESCRIPTION SYNTAX Joins several text strings into one text CONCATENATE() string. CONCATENATE(text1, text2, … ) SEARCH and SEARCHB locate one text SEARCH() string within a second text string, and SEARCH(text to find, or return the number of the starting position of within the text, SEARCHB() the first text string from the first character starting number) of the second text string. REPLACE(old text, REPLACE replaces part of a text string, starting number, REPLACE() based on the number of characters you number of characters, specify, with a different text string. new text) Common Used Text Functions FUNCTION DESCRIPTION SYNTAX Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to SUBSTITUTE(text, replace specific text in a text string; use old_text, SUBSTITUTE() new_text, REPLACE when you want to replace any text that occurs in a specific location in a instance_num) text string. Converts all uppercase letters in a text LOWERCASE() string to lowercase. LOWERCASE(text) Converts all lowercase letters in a text string UPPERCASE() to uppercase. UPPERCASE(text) REPEAT() Repeats text a given number of times. REPEAT(text, number of times) Logical Functions FUNCTION DESCRIPTION SYNTAX Returns one value if a condition you IF() specify evaluates to TRUE and another IF(condition, value if true, value if false) value if it evaluates to FALSE. ▪ Condition is any value or expression that can be evaluated to TRUE or FALSE. ▪ For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. Comparison Operators COMPARISON OPERATOR MEANING (EXAMPLE) = (equal sign) Equal to (A1=B1) > (greater than sign) Greater than (A1>B1) < (less than sign) Less than (A1= (greater than or equal to sign) Greater than or equal to (A1>=B1)