MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Advanced Excel Ninja

Course Summary

Using Excel for MIS Reporting, Data Cleaning, Data Analysis, Charting, Time-saver tricks


  • +

    Course Syllabus

    • Level 1 (A)
      • 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)
      • 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)
      • 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)
      • 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)
      • 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 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 – Scenario Manager
      • What IF Analysis – Goal Seek
      • What IF Analysis – Data Tables
      • What IF Analysis – Using Form Control Buttons
    • Level 3
      • 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 – 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 – Overview, Developer tab, Settings
      • Macros – Recording, Running; Using Buttons to run Macros
    • Level 6
      • INDIRECT()
      • OFFSET() with MATCH()
    • EXTRA - Advanced Tricks
      • Pivot Table Trick - Generating 600+ reports in <1 min
      • 3D Data Lookup using VLookup, Match, Indirect, Naming


Course Fee:
USD 149

Course Type:

Self-Study

Course Status:

Active

Workload:

1 - 4 hours / week

This course is listed under Development & Implementations and Data & Information Management Community

Attended this course?

Back to Top

Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top