Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Advanced Excel Ninja
Level 1 (A)
Introduction + Downloadable files (7:26)
Warm-up - Key Shortcuts (10:06)
Formula Tricks - SUM, AUTOSUM, MAX, MIN, AVERAGE (13:55)
Formula Tricks - SUMPRODUCT, POWER, ROUND, MROUND (12:26)
Formula Tricks - COUNT vs. COUNTA (1:53)
Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc. (11:32)
Auto fill options - Variations & Settings (7:48)
Paste Special - Value, Transpose; Formula TRANSPOSE() (6:53)
Range Selection; GO TO - Special (11:09)
Course Review
Level 1 (B)
Multi-level Sort, Custom Sort, Color Sort (10:52)
Filter & SUBTOTAL() formula (17:04)
Advanced Filter I (Differential Filter Criteria) (9:28)
Advanced Filter II (Differential Filter Criteria) (5:03)
Cell Referencing using $ - Introduction (3:23)
Cell Referencing using $ - Practice #1 (5:39)
Cell Referencing using $ - Practice #2 (4:22)
Cell Referencing using $ - Practice #3 (4:34)
Cell Referencing using $ - Special Trick (2:48)
Date Concepts and Format (10:13)
Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS (17:43)
Date Cleaning techniques using Text-to-Columns (10:13)
Dashboard I: Grouping, Data Validation (List), Cell-Range Naming (19:31)
Dashboard II: Form Controls Buttons (7:17)
Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes (5:31)
Level 1 (C)
VLOOKUP Introduction - Where/Why/How to use (5:49)
VLOOKUP - Pre-requisites and Common Pitfalls (6:45)
VLOOKUP - True (1) vs. False (0) (10:37)
VLOOKUP Practice with IFERROR (10:41)
HLOOKUP (3:29)
Using MATCH with VLOOKUP/HLOOKUP - 2x2 matrix lookup (12:43)
VLOOKUP with MATCH - Practice #1 (5:31)
VLOOKUP with MATCH - Practice #2 (5:58)
HLOOKUP with MATCH - Practice #3 (7:42)
SUMIFS - Introduction (5:32)
SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS (15:11)
COUNTIFS for duplicate detection; Remove Duplicate (6:42)
Course Review
Level 2 (A)
Data Validation (Numbers, Dates, Text length) (10:23)
INDEX with MATCH – Reverse 2-way Lookup (14:56)
INDEX with MATCH - Practice #1 (3:34)
INDEX with MATCH - Version 2 (5:26)
Pivot Table #1 (Payroll) (13:25)
Pivot Table #2 (Payroll) (6:44)
Pivot Table #3 (Payroll) (13:35)
Pivot Table - Practice (Inventory) (11:56)
SubTotal - Automatic row-wise subtotal #1 (13:57)
SubTotal - Automatic row-wise subtotal #2 (7:13)
SubTotal - Automatic row-wise subtotal #3 (7:01)
Level 2 (B)
Using CONCATENATE, & - to join data strings (11:29)
Text-to-Columns - Delimited (6:00)
Text-to-Columns - Fixed Width (6:32)
Text-to-Columns - Tricks (4:07)
Find & Replace – Advanced (10:14)
Text Formulas I – UPPER, PROPER, LOWER, TRIM, T, N, REPT (13:01)
Text Formulas II – LEFT-RIGHT-MID, LEN, SEARCH (10:40)
Text Formulas III – SEARCH vs. FIND (5:47)
Text Formulas IV – REPLACE, SUBSTITUTE (7:13)
Level 2 (C)
Logical formulas I - IF, Nested IFs (6:48)
Logical formulas II - AND, OR with IF (9:01)
Logical formulas III - more case studies (9:30)
ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR (9:01)
Conditional Formatting I (Blanks, Errors, Values, Duplicates) (10:50)
Conditional Formatting II (Formula-based) (11:37)
Conditional Formatting III (Data Bars, Color Scales, Icon Sets) (6:52)
Level 2 (D)
What IF Analysis – Scenario Manager (6:59)
What IF Analysis – Goal Seek (8:11)
What IF Analysis – Data Tables (12:28)
What IF Analysis – Using Form Control Buttons (8:19)
Level 3
Tables - Concept and Applications (5:08)
3-D Data Consolidation from same/different Workbooks #1 (7:51)
3-D Data Consolidation from same/different Workbooks #2 (8:12)
Formula Auditing techniques (7:31)
File Security & Password Protection #1 (6:56)
File Security & Password Protection #2 (7:49)
Printing (17:17)
Comments - Picture and Text (8:35)
Split Windows, Viewing multiple Windows (5:02)
Hyperlinking (4:56)
Level 4
Charts – Basic Concepts and Elements (8:13)
Basic Charts – Bar, Column, Pie (5:44)
Special Charts: Thermometer Charts (6:06)
Special Charts: Multi-axis charts (6:57)
Special Charts: Exploded Pie charts (7:21)
Chart tips-n-tricks #1 (6:41)
Chart tips-n-tricks #2 (5:54)
Level 5
Macros – Overview, Developer tab, Settings (10:39)
Macros – Recording, Running; Using Buttons to run Macros (10:22)
Level 6
INDIRECT() (8:12)
OFFSET() with MATCH() (15:08)
EXTRA - Advanced Tricks
Pivot Table Trick - Generating 600+ reports in <1 min (6:47)
3D Data Lookup using VLookup, Match, Indirect, Naming (7:53)
Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock