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

### Course Summary

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

• +

### Course Syllabus

• Level 1 (A)
• 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
• 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()
• Pivot Table Trick - Generating 600+ reports in <1 min
• 3D Data Lookup using VLookup, Match, Indirect, Naming

Course Fee:
USD 149

Self-Study

### Course Status:

Active

1 - 4 hours / week

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

Related Posts: