Level 1 (A) - Warm Up, Case Study Files + eBook (zipped folder)
- Introduction + Downloadable files
- Warm-up - Key Shortcuts
- Formula Tricks - SUM, AUTOSUM, MAX, MIN, AVERAGE
- Formula Tricks - SUMPRODUCT, POWER, ROUND, MROUND
- Formula Tricks - COUNT vs. COUNTA
- Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc.
- Auto fill options - Variations & Settings
- Paste Special - Value, Transpose; Formula TRANSPOSE()
- Range Selection; GO TO - Special
Level 1 (B) - Sort, Filter, Dates, Dashboard tricks
- Multi-level Sort, Custom Sort, Color Sort
- Filter & SUBTOTAL() formula
- Advanced Filter I (Differential Filter Criteria)
- Advanced Filter II (Differential Filter Criteria)
- Cell Referencing using $ - Introduction
- Cell Referencing using $ - Practice #1
- Cell Referencing using $ - Practice #2
- Cell Referencing using $ - Practice #3
- Cell Referencing using $ - Special Trick
- Date Concepts and Format
- Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS
- Date Cleaning techniques using Text-to-Columns
- Dashboard I: Grouping, Data Validation (List), Cell-Range Naming
- Dashboard II: Form Controls Buttons
- Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes
Level 1 (C) - Lookups
- VLOOKUP Introduction - Where/Why/How to use
- VLOOKUP - Pre-requisites and Common Pitfalls
- VLOOKUP - True (1) vs. False (0)
- VLOOKUP Practice with IFERROR
- HLOOKUP
- Using MATCH with VLOOKUP/HLOOKUP - 2x2 matrix lookup
- VLOOKUP with MATCH - Practice #1
- VLOOKUP with MATCH - Practice #2
- HLOOKUP with MATCH - Practice #3
- SUMIFS - Introduction
- SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS
- COUNTIFS for duplicate detection; Remove Duplicate
Level 2 (A) - Reverse Lookup & Pivot Table
- Data Validation (Numbers, Dates, Text length)
- INDEX with MATCH – Reverse 2-way Lookup
- INDEX with MATCH - Practice #1
- INDEX with MATCH - Version 2
- Pivot Table #1 (Payroll)
- Pivot Table #2 (Payroll)
- Pivot Table #3 (Payroll)
- Pivot Table - Practice (Inventory)
- SubTotal - Automatic row-wise subtotal #1
- SubTotal - Automatic row-wise subtotal #2
- SubTotal - Automatic row-wise subtotal #3
Level 2 (B) - Data Cleansing functions
- Using CONCATENATE, & - to join data strings
- Text-to-Columns - Delimited
- Text-to-Columns - Fixed Width
- Text-to-Columns - Tricks
- Find & Replace – Advanced
- Text Formulas I – UPPER, PROPER, LOWER, TRIM, T, N, REPT
- Text Formulas II – LEFT-RIGHT-MID, LEN, SEARCH
- Text Formulas III – SEARCH vs. FIND
- Text Formulas IV – REPLACE, SUBSTITUTE
Level 2 (C) - Logical Formulas and Conditional Formatting
- Logical formulas I - IF, Nested IFs
- Logical formulas II - AND, OR with IF
- Logical formulas III - more case studies
- ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR
- Conditional Formatting I (Blanks, Errors, Values, Duplicates)
- Conditional Formatting II (Formula-based)
- Conditional Formatting III (Data Bars, Color Scales, Icon Sets)
Level 2 (D) - What If Analysis
- What IF Analysis – Scenario Manager
- What IF Analysis – Goal Seek
- What IF Analysis – Data Tables
- What IF Analysis – Using Form Control Buttons
Level 3 - 3D Data Consolidation, Print tricks, Password, Formula Audit etc
- Tables - Concept and Applications
- 3-D Data Consolidation from same/different Workbooks #1
- 3-D Data Consolidation from same/different Workbooks #2
- Formula Auditing techniques
- File Security & Password Protection #1
- File Security & Password Protection #2
- Printing
- Comments - Picture and Text
- Split Windows, Viewing multiple Windows
- Hyperlinking
Level 4 - Charts
- Charts – Basic Concepts and Elements
- Basic Charts – Bar, Column, Pie
- Special Charts: Thermometer Charts
- Special Charts: Multi-axis charts
- Special Charts: Exploded Pie charts
- Chart tips-n-tricks #1
- Chart tips-n-tricks #2
Level 5 - Macros Basics
- Macros – Overview, Developer tab, Settings
- Macros – Recording, Running; Using Buttons to run Macros
Level 6 - INDIRECT, OFFSET, MATCH
- INDIRECT()
- OFFSET() with MATCH()
Bonus - Advanced Tricks
