Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Advanced Excel Tutorials - Become an Excel Ninja
Downloadable Files
Excel Case Studies - Please Download
eBook - Become an Excel Ninja.
Updated Excel Case Study - November 2018
001 Handpicked Ninja Shortcuts
Introduction (5:12)
0101 How to use this section? (1:17)
0102 The "ONE" Universal shortcut & a faster Copy-Paste technique (3:23)
0103 AutoFill selected cell(s) with pre-defined formula/text/no. (1:16)
0104 Navigating across Worksheets; Data Table selection & manoeuvring (4:34)
0105 Adding/Deleting/Selecting Rows/Columns (3:07)
0106 For "Formula" One Speedstars (6:20)
0107 Formatting cells (4:39)
0108 Using Quick Access Toolbar (QAT) (4:45)
Formula auditing magic (4:51)
Excel Shortcut Quiz #1
002 Formula Warm-Up
0201 MAX() in Financial Modeling (2:44)
0202 LARGE() & SMALL() - for bidding/auction pricing strategies (2:38)
0203 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 2 (3:43)
0204 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2 (6:38)
0205 COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS() (3:20)
0206 SUMPRODUCT() for weighted average computation (3:34)
0207 POWER() vs. Caret sign ( ^ ) (2:39)
Excel Formula Warm-Up Quiz #2
003 Formatting Tricks
0301 Formatting Tricks incl. Special Custom Formats (5:18)
0302 Format Painter - "Uninterrupted Click-ability" (1:56)
0303 Defining Cell Style - for "one-click" universal change (4:02)
0304 Transferring (Merging) Cell Style from one workbook to another (1:15)
0305 Cell drag-n-drop Auto Fill Options + Activation Setting (1:19)
0306 Cell drag-n-drop Auto Fill Options - Yr-end, Month-end, AlphaNumeric, Fill - Justify (2:47)
0307 Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month (1:11)
0308 Paste Special - Transpose vs. TRANSPOSE() (4:19)
Excel Formatting Tricks Quiz #3
004 Cell Referencing ($) - Relative & Absolute
0401 Cell Referencing ($) - 1 of 2 (3:02)
0402 Cell Referencing ($) - 2 of 2 (3:20)
Cell Referencing using - Practice 1 (4:22)
Cell Referencing using - Practice 2 (4:34)
Cell Referencing using - Special Trick (2:49)
Excel Cell Referencing Quiz #4
005 Go To - Special "magic wand"
0501 Go To (Special) - Formulas with Errors (2:40)
0502 Go To (Special) - Formulas without Errors (2:02)
0503 Go To (Special) w. Ctrl+Enter (2:08)
0504 Go To (Special) - Fill Intermittent Blank Cells - Case 1 (6:00)
0505 Go To (Special) - Fill Intermittent Blank Cells - Case 2 (2:04)
0506 Go To (Special) - Visible Cells w. Shortcut (2:24)
Review our Course
Short Excel Go To - Special Quiz #5
006 Sort (Vertical, Horizontal, Custom), Filter & Advanced Filter
0601 Vertical Sort - 1-level & 2-level (2:24)
0602 Custom Sort (5:18)
0603 Sort Trick - add alternate blank rows in-between existing rows (2:58)
0604 Horizontal Sorting (Left to Right) - auto-realigning 100s of columns (3:29)
0605 Filter - Basics (3:37)
0606 Filter - Choosing the dataset correctly (1:25)
0607 Filter analysis w. shortcuts (2:52)
0608 Filter - SUBTOTAL() for calculations (5:06)
0609 Filter - SUBTOTAL() 9 vs. 109 series (2:40)
0610 Filter - Applying 2 or more Filters simultaneously on the same sheet (2:19)
0611 Filter - Color Filter & Text Filter (1:13)
0612 Advanced Filter - Basics (4:30)
0613 Advanced Filter - Differential Criteria (4:11)
0614 Advanced Filter - More parameters (3:09)
Excel Sort, Filter & Advanced Filter Quiz #6
007 Working with Dates
0701 Dates - Important Concepts 1 of 2 (3:16)
0702 Dates - Important Concepts 2 of 2 (4:06)
0703 Date Formulas - DAY(), MONTH(), YEAR(), DATE() (2:15)
0704 Date Formulas - TEXT() (3:06)
0705 Date Formulas - WEEKDAY(), WORKDAY(), NETWORKDAYS() (3:52)
0706 Date Formulas - WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays (4:53)
0707 Date Formulas - NETWORKDAY.INTL() for no. of business days calculations w. custom weekends/holidays (1:44)
Excel Date Quiz #7_1
0708 Date Formulas - TODAY() and NOW() w. Shortcut (1:29)
0709 Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates (3:41)
0710 Date Formulas - EDATE() for Financial Modeling, Budgets, Due Dates (3:55)
Excel Date Quiz #7_2
008 Data (Cell Input) Validation
0801 Data Validation - Drop Down List using hard coded text & pre-defined range (2:37)
0802 Data Validation - Drop Down List using named range (3:51)
0803 Data Validation - Numbers w. Error Alert and Input Message (1:36)
0804 Data Validation - Dates w. Error Alert and Circle Invalid Data (3:52)
0805 Data Validation - Text Length w. Error Alert (1:35)
0806 Data Validation - Custom w. formula logic (2:01)
Excel Data Validation Quiz #8
009 Setting up work area view
0901 Grouping/UnGrouping Columns and Rows - 1 of 2 (2:58)
0902 Grouping/UnGrouping Columns and Rows - 2 of 2 (0:40)
0903 Grouping Trick: Changing placement of Grouping Button (1:40)
0904 Cell Gridlines: Turning On/Off (0:49)
0905 - Trick - Hide or Unhide Rows and Columns and Freeze panes (5:31)
Excel View (Freeze Panes) Quiz #9
Review our Course
010 Pivot Table
1001 Pivot Table - Why should you learn? (3:58)
1002 Pivot Table - Pre-requisites (2:25)
1003 Pivot Table - How to create one? (2:40)
1004 Pivot Table - Exploring Pivot Table grid (Fields) (3:21)
1005 Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc. (5:06)
1006 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total (2:22)
1007 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total (4:50)
Excel Pivot Table Quiz #10_2
Excel Pivot Table Quiz #10_1
1008 Pivot Table - Grouping - Numbers (3:38)
1009 Pivot Table - Grouping - Dates [Years, Months etc.] (4:09)
1010 Pivot Table - Grouping - Text (3:36)
1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source (6:19)
1012 Pivot Table - Auto Refresh (0:41)
1013 Pivot Table - Pivot Chart Shortcut and Sparklines (2:13)
1014 Pivot Table - Drill Down option (0:59)
1015 Pivot Table - Report Filter - Generating 100s of reports in few seconds (3:39)
Excel Pivot Table Quiz #10_3
1016 Pivot Table - Slicer vs. Report Filter (2:33)
1017 Pivot Table - Practice - Sales Data Analysis - 1 of 2I (%, Grouping, Chart, Report Filter Pages) (7:57)
1018 Pivot Table - Practice - Sales Data Analysis - 2 of 2 (Slicer) (3:09)
1019 Pivot Table - Practice - Closing Stock Analysis (7:01)
Excel Pivot Table Quiz #10_4
011 Lookups (1D, 2D, 3D, Reverse) & Conditional Calculations (Sum, Average, Count, Special Running Total)
1101 VLOOKUP() for Starters (7:59)
1102 VLOOKUP w. TRUE vs. FALSE (7:17)
1103 VLOOKUP w. TRUE - applications (6:01)
1104 HLOOKUP() vs. VLOOKUP() (2:45)
1105 MATCH() - Basics (3:38)
1106 MATCH() match_type: -1 vs. 0 vs. 1 (3:46)
1107 2-D Lookup (Vertical + Horizontal) - VLOOKUP w. MATCH (5:54)
1108 2-D Lookup - VLOOKUP w. MATCH - Framework (1:18)
1109 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #1 (3:51)
1110 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2 (2:20)
1111 2-D Lookup - VLOOKUP w. MATCH - Practice Exercise (6:13)
1112 2-D Lookup (Horizontal + Vertical) - HLOOKUP w. MATCH (6:49)
1113 INDIRECT() - Basics (4:24)
1114 INDIRECT() w. Range Naming - Applications (6:02)
1115 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question (3:39)
1116 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer (7:11)
1117 Reverse Lookup - Question (3:27)
1118 Reverse Lookup - INDEX() w. MATCH() - 1 of 2 (8:12)
1119 Reverse Lookup - INDEX() w. MATCH() - 2 of 2 (6:07)
1120 SUMIFS(): Conditional Summation (1 criteria) (4:28)
1121 SUMIFS(): Conditional Summation (2 criteria) (4:12)
1122 SUMIFS(): Conditional Summation (3 criteria) w. date range (5:20)
1123 SUMIFS(): Condition based Selective Cumulative Running Total (5:31)
1124 COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No. (6:10)
1125 AVERAGEIFS() (1:23)
OFFSET() with MATCH() (15:08)
Excel Lookup Quiz #11_1
Excel Lookup Quiz #11_2
Excel Lookup Quiz #11_3
Excel Lookup Quiz #11_4
Excel Lookup Quiz #11_5
Excel Lookup Quiz #11_6
Excel Lookup Quiz #11_7
Review our Course
Fuzzy Lookup (1:29)
012 Data Cleaning / "Sanitizing" & Text Formulas
1201 Text Formulas – Change Case using UPPER(), PROPER() & LOWER() (1:42)
1202 Text Formulas – Remove Leading, Trailing & excess Intermittent spaces using TRIM() (1:43)
1203 Text Formulas – Using LEN() & TRIM() together (1:33)
1204 Text Formulas – T(), N() & VALUE() (3:16)
1205 Text Formulas – REPT() for artifical Column Chart in a Cell (1:14)
1206 Text Formulas – REPT() & LEN() for adding leading zeroes before incomplete Cheque Nos. (4:30)
1207 Joining data strings using CONCATENATE (4:55)
1208 Find & Replace – Using Wildcard character ( * ) (3:08)
1209 Find & Replace – Using Wildcard character ( ? ) (2:23)
1210 Find & Replace – Neutralising Wildcard characters to remove them from data (2:38)
1211 Find & Replace – Word vs. Excel (6:06)
1212 Find & Replace – Cell Format (4:31)
1213 Text to Columns – Basics (Delimited) (2:50)
1214 Text to Columns – Basics (Fixed Width) + Hidden Trick (4:06)
1215 Text to Columns – Cleaning up numbers w. trailing minus sign (2:33)
1216 Text to Columns – Cleaning up numbers w. leading "Dr/Cr" text (2:02)
1217 Text to Columns – Correcting invalid Dates (DMY) (3:24)
1218 Text to Columns – Correcting invalid Dates (YMD) (3:18)
1219 Text Formulas – LEFT(), RIGHT() & SEARCH() - 1 of 2 (4:19)
1220 Text Formulas – LEFT(), RIGHT() & SEARCH() - 2 of 2 (2:26)
1221 Text Formulas – MID() & LEN() (3:42)
1222 Text Formulas – REPLACE() & SUBSTITUTE() (7:13)
1223 FlashFill for Data Cleaning in Excel (7:21)
Excel Data Cleaning Quiz #12_1
Excel Data Cleaning Quiz #12_2
Excel Data Cleaning Quiz #12_3
Excel Data Cleaning Quiz #12_4
Excel Data Cleaning Quiz #12_5
013 Logical Formulas - Single & Combo
1301 Logical formulas: ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR(), ISFORMULA() (4:18)
1302 Logical formulas: Basics of IF() and Nested IF() (5:19)
1303 Logical formulas: AND(), OR() (5:15)
1304 Logical formulas: Combining IF() w. AND() & OR() (3:35)
Excel Logical Formula Quiz #12_1
Excel Logical Formula Quiz #12_2
014 Conditional Formatting
1401 Conditional Formatting: Actual vs. Budget Comparison through Arrows (5:47)
1402 Conditional Formatting: w. Drop-down list based Input (3:15)
1403 Conditional Formatting: Data Bars, Color Scales, Icon Sets (2:30)
1404 Conditional Formatting: Blanks, Errors, Values, Duplicates (5:03)
1405 Conditional Formatting: Formula based (Colored rows based in user input) (4:12)
1406 Conditional Formatting: Formula based w. AND() - 2-way input Coloring (5:22)
1407 Conditional Formatting: Formula based w. COUNTIFS() - Highlighting inputs if from restricted list (4:14)
Excel Conditional Formatting Quiz #14_1
Excel Conditional Formatting Quiz #14_2
015 What-If Analysis
1501 What IF Analysis – Using Form Control Buttons from Developer Tab (Spin Bar, Scroll Bar) (5:09)
1502 What IF Analysis – Limitations of Spin Bar, Scroll Bar (4:03)
Excel What-If Analysis Quiz #15_1
1503 What IF Analysis – Scenario Managers (6:59)
1504 What IF Analysis – Goal Seek (4:33)
Excel What-If Analysis Quiz #15_2
1505 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1 Output (8:22)
1506 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - with PMT for Loan EMI (5:22)
1507 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Question (2:39)
1508 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Answer (10:08)
016 Subtotal & 3D Data Consolidation
1601 Subtotal - Creating a 1-level SubTotal on a Sorted list (5:34)
1602 Subtotal - Deleting a SubTotal (1:16)
1603 Subtotal - Creating a 2-level SubTotal on a Sorted list (4:28)
1604 Subtotal - Coloring the Subtotal rows with different Color (1:44)
1606 Consolidate - 3 Dimensions (7:55)
1605 Consolidate - 2 Dimensions (4:07)
Review our Course
Excel Subtotal and Data Consolidation Quiz #16
017 Workbook & Worksheet Security
1701 Cell level Security (6:41)
1702 Cell level Security Challenge (3:02)
1703 Sheet level Security (5:38)
1704 File level Security (2:46)
Excel Workbook & Worksheet Security Quiz #17
018 Printing
1801 Printing - Rows to Repeat at Top (Print Titles) (2:19)
1802 Printing - Gridlines (1:14)
Excel Printing Quiz #18_1
1803 Printing - Custom Header & Footer (2:53)
1804 Printing - Page Order - Vertical vs. Horizontal (2:12)
Excel Printing Quiz #18_2
1805 Printing - Tricks for Financial Analysts - Check underlying formulas (1:48)
1806 Printing - Tricks for Financial Analysts - Cell Comments (1:47)
1807 Printing - Print Entire Workbook (0:59)
Excel Printing Quiz #18_3
019 Multiple Windows, Comments, Hyperlinking
1901 Comments - Shortucts, Inserting Picture in Comment Box (4:23)
1902 Split Windows, Viewing multiple Windows - For working with different workbooks, worksheets & scattered cell ranges simultaneously (7:08)
1903 Hyperlinking (Ctrl+K) (3:34)
Review our Course
Excel Multiple Windows, Comments, Hyperlinking Quiz #19
020 Excel Arrays (Added on 5th Jan 2017)
Basics of Array Formula (Ctrl Shift Enter) - Example 1 (3:16)
Basics of Array Formula (Ctrl Shift Enter) - Example 2 (1:21)
Building MAX IF and MIN IF with one condition - Example 1 (6:31)
Building MAX IF and MIN IF with one condition - Example 2 (5:32)
Array Formula using MAX() MIN() and IF() - 2 criteria (6:13)
Find the four earliest dates using SMALL() and IF() (4:14)
Calculating SUM of Top 3 numbers (2:16)
SUMPRODUCT - Add numbers from next column (8:21)
Finding Overlaping Dates using SUMPRODUCT - Part 1 of 2 (4:46)
Finding Overlaping Dates using SUMPRODUCT - Part 2 of 2 (3:23)
Extract unique values from a range in excel (2:14)
Download Practice Files for Excel Array
Advanced Charts
Download Practice Files for Advanced Charts
Charts - Basic Settings 01 (5:18)
Charts - Basic Settings 02 (6:23)
Trendline 01 (3:18)
Thermo 01 (5:36)
Thermo 02 (6:20)
2 axis - Basics with Ghostlines (4:23)
Horizontal bar 01 (4:33)
100 percent stacked bar (4:47)
Waterfall - Demo (5:09)
Waterfall - Logic (6:52)
Waterfall - 01 (6:28)
Waterfall - 02 (6:04)
Combo - BHP (7:20)
Stacked 100 column 80-20 (3:17)
Ringed doughnut (3:47)
Radar (4:57)
Gantt 01 (5:04)
Charts aesthetics 01 - 07 (7:32)
Chart Lending Company 01 (7:57)
Chart Lending Company 02 (3:52)
Tornado or Butterfly Chart (7:57)
Excel Printing Quiz #18_2
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock