Advanced Excel – Module 3

Module 3 – 3 hours

1. Count and Sum: Countif, Count Blank/Nonblank Cells, Count Characters, Count Cells with Text,
   Sum, Running Total, Sumif, Sumproduct
2. Logical: If, Comparison Operators, Or, Roll the Dice, Ifs, Contains Specific Text, Switch, If Cell is
   Blank
3. Cell References: Copy Exact Formula, 3D-reference, External References, Hyperlinks, Percent
   Change
4. Date & Time: DateDif, Today’s Date, Calculate Age, Time Difference, Weekdays, Days until
   Birthday, Time Sheet, Last Day of the Month, Holidays, Quarter, Day of the Year
5. Text: Separate Strings, Number of Instances, Number of Words, Text to Columns, Find, Search,
6. Text: Lower/Upper Case, Remove Spaces, Compare Text, Substitute vs Replace, Text,
   Concatenate Strings, Substring
7. Lookup & Reference: Vlookup, Tax Rates, Index and Match, Two-way Lookup, Offset, Casesensitive Lookup,
8. Lookup & Reference: Left Lookup, Locate Maximum Value, Indirect, Two-column Lookup,
   Closest Match, Compare Two Columns
9. Financial: PMT, Loans with Different Durations, Investment or Annuity, Compound Interest
10. Financial: CAGR, Loan Amortization Schedule, NPV, IRR, Depreciation
11. Statistical: Average, Negative Numbers to Zero, Random Numbers, Rank, Percentiles and
    Quartiles,
12. Statistical: Box and Whisker Plot, AverageIf, Forecast and Trend, Absolute Value,
13. Statistical: MaxIfs and MinIfs, Weighted Average, Standard Deviation, Frequency
14. Round: Chop off Decimals, Nearest Multiple, Even and Odd
15. Formula Errors: IfError, IsError, Aggregate, Circular Reference, Formula Auditing, Floating Point
    Errors
16. Array Formulas: Count Errors, Count Unique Values, Count with Or Criteria, Sum Every Nth Row,
17. Array Formulas: Sum Largest Numbers, Sum Range with Errors, Sum with Or Criteria, Most
    Frequently Occurring Word, System of Linear Equations