Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Advanced Excel Crash Course
Downloadable Files
Excel Case Studies - Please Download
eBook - Become an Excel Ninja.
001 Handpicked Ninja Shortcuts
Introduction
0101 How to use this section? (1:16)
0102 The "ONE" Universal shortcut & a faster Copy-Paste technique (3:22)
0103 AutoFill selected cell(s) with pre-defined formula/text/no. (1:15)
0104 Navigating across Worksheets; Data Table selection & manoeuvring (4:32)
0105 Adding/Deleting/Selecting Rows/Columns (3:06)
0106 For "Formula" One Speedstars (6:18)
0107 Formatting cells (4:36)
0108 Using Quick Access Toolbar (QAT) (4:43)
Formula auditing magic (4:51)
Excel Shortcut Quiz #1
002 Formula Warm-Up
0201 MAX() in Financial Modeling (2:43)
0202 LARGE() & SMALL() - for bidding/auction pricing strategies (2:37)
0203 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 2 (3:42)
0204 Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2 (6:36)
0205 COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS() (3:19)
0206 SUMPRODUCT() for weighted average computation (3:33)
0207 POWER() vs. Caret sign ( ^ ) (2:38)
Excel Formula Warm-Up Quiz #2
003 Formatting Tricks
0301 Formatting Tricks incl. Special Custom Formats (5:16)
0302 Format Painter - "Uninterrupted Click-ability" (1:55)
0303 Defining Cell Style - for "one-click" universal change (4:00)
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:46)
0307 Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month (1:11)
0308 Paste Special - Transpose vs. TRANSPOSE() (4:17)
Excel Formatting Tricks Quiz #3
004 Cell Referencing ($) - Relative & Absolute
0401 Cell Referencing ($) - 1 of 2 (3:01)
0402 Cell Referencing ($) - 2 of 2 (3:19)
Cell Referencing using - Practice 1 (4:22)
Cell Referencing using - Practice 2 (4:34)
Cell Referencing using - Special Trick (2:48)
Excel Cell Referencing Quiz #4
005 Go To - Special "magic wand"
0501 Go To (Special) - Formulas with Errors (2:39)
0502 Go To (Special) - Formulas without Errors (2:01)
0503 Go To (Special) w. Ctrl+Enter (2:07)
0504 Go To (Special) - Fill Intermittent Blank Cells - Case 1 (5:58)
0505 Go To (Special) - Fill Intermittent Blank Cells - Case 2 (2:03)
0506 Go To (Special) - Visible Cells w. Shortcut (2:23)
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:23)
0602 Custom Sort (5:16)
0603 Sort Trick - add alternate blank rows in-between existing rows (2:57)
0604 Horizontal Sorting (Left to Right) - auto-realigning 100s of columns (3:28)
0605 Filter - Basics (3:36)
0606 Filter - Choosing the dataset correctly (1:25)
0607 Filter analysis w. shortcuts (2:51)
0608 Filter - SUBTOTAL() for calculations (5:04)
0609 Filter - SUBTOTAL() 9 vs. 109 series (2:39)
0610 Filter - Applying 2 or more Filters simultaneously on the same sheet (2:18)
0611 Filter - Color Filter & Text Filter (1:12)
0612 Advanced Filter - Basics (4:28)
0613 Advanced Filter - Differential Criteria (4:09)
0614 Advanced Filter - More parameters (3:08)
Excel Sort, Filter & Advanced Filter Quiz #6
007 Working with Dates
0701 Dates - Important Concepts 1 of 2 (3:15)
0702 Dates - Important Concepts 2 of 2 (4:04)
0703 Date Formulas - DAY(), MONTH(), YEAR(), DATE() (2:14)
0704 Date Formulas - TEXT() (3:05)
0705 Date Formulas - WEEKDAY(), WORKDAY(), NETWORKDAYS() (3:51)
0706 Date Formulas - WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays (4:51)
0707 Date Formulas - NETWORKDAY.INTL() for no. of business days calculations w. custom weekends/holidays (1:43)
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:40)
0710 Date Formulas - EDATE() for Financial Modeling, Budgets, Due Dates (3:54)
Excel Date Quiz #7_2
008 Data (Cell Input) Validation
0801 Data Validation - Drop Down List using hard coded text & pre-defined range (2:36)
0802 Data Validation - Drop Down List using named range (3:50)
0803 Data Validation - Numbers w. Error Alert and Input Message (1:36)
0804 Data Validation - Dates w. Error Alert and Circle Invalid Data (3:51)
0805 Data Validation - Text Length w. Error Alert (1:35)
0806 Data Validation - Custom w. formula logic (2:00)
Excel Data Validation Quiz #8
009 Setting up work area view
0901 Grouping/UnGrouping Columns and Rows - 1 of 2 (2:57)
0902 Grouping/UnGrouping Columns and Rows - 2 of 2
0903 Grouping Trick: Changing placement of Grouping Button
0904 Cell Gridlines: Turning On/Off (0:49)
0905 Trick - Hide/Unhide Rows and Columns (5:31)
Excel View (Freeze Panes) Quiz #9
Review our Course
010 Pivot Table
1001 Pivot Table - Why should you learn? (3:57)
1002 Pivot Table - Pre-requisites (2:24)
1003 Pivot Table - How to create one? (2:39)
1004 Pivot Table - Exploring Pivot Table grid (Fields) (3:20)
1005 Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc. (5:04)
1006 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total (2:21)
1007 Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total (4:48)
Excel Pivot Table Quiz #10_2
Excel Pivot Table Quiz #10_1
1008 Pivot Table - Grouping - Numbers (3:37)
1009 Pivot Table - Grouping - Dates [Years, Months etc.] (4:07)
1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source (6:17)
1012 Pivot Table - Auto Refresh (0:40)
1013 Pivot Table - Pivot Chart Shortcut and Sparklines (2:11)
1014 Pivot Table - Drill Down option (0:59)
1015 Pivot Table - Report Filter - Generating 100s of reports in few seconds (3:38)
1016 Pivot Table - Slicer vs. Report Filter (2:32)
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:08)
1019 Pivot Table - Practice - Closing Stock Analysis (6:58)
Excel Pivot Table Quiz #10_4
011 Lookups (1D, 2D, 3D, Reverse) & Conditional Calculations (Sum, Average, Count, Special Running T
1106 MATCH() match_type: -1 vs. 0 vs. 1 (3:45)
1107 2-D Lookup (Vertical + Horizontal) - VLOOKUP w. MATCH (5:51)
1108 2-D Lookup - VLOOKUP w. MATCH - Framework (1:18)
1109 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #1 (3:47)
1110 2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2 (2:19)
1111 2-D Lookup - VLOOKUP w. MATCH - Practice Exercise (6:11)
1112 2-D Lookup (Horizontal + Vertical) - HLOOKUP w. MATCH (6:47)
1113 INDIRECT() - Basics (4:17)
1114 INDIRECT() w. Range Naming - Applications (5:59)
1115 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question (4:17)
1116 3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer (7:09)
1117 Reverse Lookup - Question (3:26)
1118 Reverse Lookup - INDEX() w. MATCH() - 1 of 2 (8:12)
1119 Reverse Lookup - INDEX() w. MATCH() - 2 of 2 (6:05)
1120 SUMIFS(): Conditional Summation (1 criteria) (5:02)
1121 SUMIFS(): Conditional Summation (2 criteria) (4:15)
1122 SUMIFS(): Conditional Summation (3 criteria) w. date range (5:58)
1123 SUMIFS(): Condition based Selective Cumulative Running Total (5:40)
1124 COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No. (6:08)
1125 AVERAGEIFS() - to be updated (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
New Lecture
Review our Course
New Lecture
Fuzzy Lookup
012 Data Cleaning / "Sanitizing" & Text Formulas
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:28)
1207 Joining data strings using CONCATENATE, & - to be updated (4:55)
1208 Find & Replace – Using Wildcard character ( * ) (3:07)
1209 Find & Replace – Using Wildcard character ( ? ) (2:22)
1210 Find & Replace – Neutralising Wildcard characters to remove them from data (2:37)
1211 Find & Replace – Word vs. Excel (6:04)
1212 Find & Replace – Cell Format (4:29)
1215 Text to Columns – Cleaning up numbers w. trailing minus sign (2:32)
1216 Text to Columns – Cleaning up numbers w. leading "Dr/Cr" text (2:01)
1217 Text to Columns – Correcting invalid Dates (DMY) (3:23)
1218 Text to Columns – Correcting invalid Dates (YMD) (3:17)
1219 Text Formulas – LEFT(), RIGHT() & SEARCH() - 1 of 2 (4:17)
1220 Text Formulas – LEFT(), RIGHT() & SEARCH() - 2 of 2 (2:25)
1221 Text Formulas – MID() & LEN() (3:41)
1222 Text Formulas – REPLACE() & SUBSTITUTE()
1223 FlashFill for Data Cleaning in Excel
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
014 Conditional Formatting
1405 Conditional Formatting: Formula based (Colored rows based in user input) (4:10)
1406 Conditional Formatting: Formula based w. AND() - 2-way input Coloring (5:20)
1407 Conditional Formatting: Formula based w. COUNTIFS() - Highlighting inputs if from restricted list (4:12)
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:06)
1502 What IF Analysis – Limitations of Spin Bar, Scroll Bar (4:01)
Excel What-If Analysis Quiz #15_1
1503 What IF Analysis – Scenario Managers - to be updated
1504 What IF Analysis – Goal Seek (4:31)
Excel What-If Analysis Quiz #15_2
1505 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1 Output (8:20)
1506 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - with PMT for Loan EMI (5:20)
1507 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Question (2:38)
1508 What IF Analysis – Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Answer (10:06)
0501 Go To (Special) - Formulas with Errors
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock