Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Excel Training Presentation By Quadri Atharu

reelquadry001
October 05, 2024
100

Excel Training Presentation By Quadri Atharu

reelquadry001

October 05, 2024
Tweet

Transcript

  1. Corporate Blunders That Cost Billions • In 2012, JP Morgan

    suffered a $6 billion business loss due to an Excel copy and paste error. • In 2008, Barclays Capital had to spend millions on worthless contracts due to an Excel formatting error • In 2005, Kodak suffered a loss of $11 million in severance benefits due to a typo in Excel • In 2010, MI5 tapped the wrong phones due to Excel error
  2. Cell References Types of cell references in Excel include: -

    Relative References (e.g., A1): Adjust when copied. It makes the cells reference free. It gives the fill function freedom to continue the order without restrictions. - Absolute References (e.g., $A$1): Remain constant when copied. The reference is absolutely locked. - Mixed References (e.g., A$1 or $A1): One part is fixed. Example: If you copy the formula =A1 + B1 from cell C1 to C2, it becomes =A2 + B2 (relative).
  3. Range and Arrays A Range is a selection of cells,

    defined by their address. For instance, A1:B2 includes four cells: A1, A2, B1, and B2. An Array is a collection of values. Example: {1, 2, 3} can be used in an array formula like =SUM({1, 2, 3}), which calculates to 6.
  4. Named Ranges A Named Range allows you to refer to

    a specific range by name instead of cell references. This improves readability and makes formulas easier to manage. Example: Name the range A1:A10 as 'Sales'. You can then use =SUM(Sales) instead of =SUM(A1:A10), making your formulas clearer.
  5. Tables Excel Tables are structured ranges that allow for easy

    data management. Tables support features like sorting, filtering, and dynamic referencing. Example: Convert the range A1:D10 to a table. You can reference the column 'Sales' as =SUM(Table1[Sales]) for easier calculations.
  6. Formulas - Components A formula consists of several components: -

    Operands: Values or cell references involved in the calculation. - Operators: Symbols that define the type of calculation (e.g., +, -, *, /). - Functions: Predefined calculations that take arguments. Example: In the formula =A1 + B1, A1 and B1 are operands, and + is the operator.
  7. Formulas - Components A formula consists of several components: -

    Operands: Values or cell references involved in the calculation. - Operators: Symbols that define the type of calculation (e.g., +, -, *, /). - Functions: Predefined calculations that take arguments. Example: In the formula =A1 + B1, A1 and B1 are operands, and + is the operator.
  8. Functions - Components, Types Functions can be categorized into types:

    - Statistical: Functions like AVERAGE and COUNT for data analysis. - Text: Functions such as CONCATENATE and TEXT for manipulating text strings. - Logical: Functions like IF, AND, OR for conditional operations. - Lookup/Reference: Functions like VLOOKUP and INDEX/MATCH for data retrieval. Example: =AVERAGE(A1:A10) calculates the average of the values in cells A1 through A10.
  9. IF, OR and AND Logical functions enable decision-making in Excel.

    - IF: Tests a condition and returns one value if TRUE and another if FALSE. Example: =IF(A1>10, 'Yes', 'No'). - OR: Returns TRUE if any condition is TRUE. Example: =OR(A1>10, B1<5). - AND: Returns TRUE only if all conditions are TRUE. Example: =AND(A1>10, B1<5). You can use these together, such as in: =IF(AND(A1>10, B1<5), 'Both', 'Not Both').
  10. Lookup Functions Lookup functions are essential for retrieving data from

    tables. - VLOOKUP: Searches for a value in the first column and returns a value from a specified column. Example: =VLOOKUP(A1, B1:D10, 2, FALSE) finds A1 in the first column of the range B1:D10 and returns the corresponding value from the second column. - HLOOKUP: Similar to VLOOKUP but searches horizontally. Example: =HLOOKUP(A1, A1:D3, 2, FALSE). - INDEX: Returns the value of a cell in a specified row and column. Example: =INDEX(A1:B10, 2, 1) returns the value in row 2, column 1. - MATCH: Returns the relative position of an item in an array. Example: =MATCH('apple', A1:A10, 0) finds the position of 'apple' within A1:A10.