# 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
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
38. Date Functions: MONTH, YEAR, EOMONTH, EDATE, DATE
39. Text Function
40. Documentation: Data Validation and Comments
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