Microsoft Excel Advanced Master Class

Spreadsheet is the most ubiquitous pieces of  program used in workplace across the world. Learning to confidently operate the program means adding a highly valuable asset to employability portfolio and improved business processes.

In this Microsoft Excel Advanced Master Class, participants will learn how to use Excel’s advanced features and functions such as OFFSET, MAXIFS, Get & Transform (Power Query) to import files from Access Database, SQL, Web, Social Media. Further, participant will learn how to clean, transform and refine imported data and perform further analysis using Power Pivot

In-depth Course Outline

  1. Introduction to Advanced Excel Features
  2. Data Validation: Whole Number, Text Length, List
  3. Creating Lookup List using Named Range in Data Validation
  4. Working with data validation: Copying cells with data validation
  5. Advanced Conditional Formatting
  6. Highlighting rows using conditional formatting and data validation
  7. New rule: Icon set conditional formatting
  8. Logical functions: IF, AND, OR
  9. Combining functions: IF with AND, IF with OR nested function
  10. Nested IF formula
  11. Error Functions: IFNA and IFERROR
  12. Lookup Functions I: VLOOKUP, HLOOKUP (Range and Exact Matches)
  13. Lookup Functions II: CHOOSE, INDEX, MATCH, INDEX/MATCH
  14. Lookup Functions III: INDIRECT and OFFSET
  15. Combining SUMIFS, AVERAGEIFS, SUM with INDIRECT and OFFSET functions
  16. Error checking in formulas
  17. Show formulas and trace error tools
  18. Evaluating formula
  19. Trace precedent and dependent
  20. Watch window
  21. Protecting sheet, workbook and workbook structure
  22. Protecting sheet: Unlocking certain cells
  23. Protecting sheet: Allowing users to edit specific ranges
  24. Modelling function: SUMPRODUCT
  25. What-if analysis tool: Data Table (One and Two Variables)
  26. What-if analysis tools: Goal Seek and Scenario Manager
  27. Summarizing scenarios in a new sheet
  28. Optimization with Solver tool
  29. Solver and Scenario Manager’s integration
  30. Enabling Developer tab
  31. Recording Absolute and Relative References Macros
  32. Assigning Macros to buttons
  33. Saving Macros
  34. Macro workbook: Security settings
  35. VBA code window explained
  36. Designing and structuring excel sheet
  37. Advanced calculations
  38. Date Functions: MONTH, YEAR, EOMONTH, EDATE, DATE
  39. Text Function
  40. Documentation: Data Validation and Comments
  41. Creating Hyperlink
  42. Tables and structured reference
  43. ROWS, SUMIF, LARGE, SMALL Functions
  44. Introduction to Array Formula
  45. Single and multi-cells array formulas
  46. Using INDEX & MATH functions in array formula
  47. SUMPRODUCTION compared with Array Function
  48. Array Constants
  49. TRANSPOSE function
  50. Replacing blank cells with repeated values
  51. Cleaning Data: Text to Column
  52. Removing Unwanted spaces using TRIM and CLEAN Functions
  53. LEN & ISNUMBER Functions
  54. Working with Dates: EDATE, EOMONTH, WORKDAY.INTL Functions
  55. Calculating Due and Reminder Dates
  56. Building dynamic payroll system
  57. Financial Functions: FV, PV, RATE, NPER, PMT, RATE, PPMT, IPMT, IRR, NPV
  58. Building Loan Amortization Schedule
  59. Depreciation Schedule: SLN, SYD and DDB Functions
  60. New Functions: IFS, CONCAT, MINIFS, MAXIFS, SWITCH
  61. Building Dynamic Dashboard
  62. General Excel for Professionals
  63. Running Macros using specified shortcut key
  64. Get & Transform (Power Query)
  65. How to analyze big data in Power Pivot

Register Now