Autoplay
Autocomplete
HTML5
Flash
Player
Speed
Previous Lecture
Complete and continue
Excel Turboboost
Free Videos
Top 7 Pivot Table Tricks (11:20)
Find & Replace Tricks (4:50)
Flash Fill - Basic to Intermediate (6:27)
Go To Special (5:01)
VLOOKUP with TRUE (6:41)
Why 2D Lookup (4:02)
10-min Advanced Excel Tricks (10:28)
11-min Excel Macros for Non-Programmers (11:48)
8-min Advanced PowerPoint Tricks (8:02)
Free Downloadables
Excel Ninja Shortcuts eBook
Email Subject Lines - Best Practices
Bain and Co. Writing Style Compilation
Advanced Excel Ninja [ Part of Beginner - Advanced Excel Course ]
Introduction π
How to use this section? π
The "ONE" Universal shortcut & a faster Copy-Paste technique π
AutoFill selected cell(s) with pre-defined formula/text/no. π
Navigating across Worksheets; Data Table selection & manoeuvring π
Adding/Deleting/Selecting Rows/Columns π
Formatting cells π
Using Quick Access Toolbar (QAT) π
For "Formula" One Speedstars π
Formula auditing magic π
MAX() in Financial Modeling π
LARGE() & SMALL() - for bidding/auction pricing strategies π
Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 1 of 2 π
Advanced ROUND() Tricks with ROUNDUP() & ROUNDDOWN() - 2 of 2 π
COUNT() vs. COUNTA() vs. COUNTBLANK() vs. COUNTIFS() π
SUMPRODUCT() for weighted average computation π
POWER() vs. Caret sign ( ^ ) π
Formatting Tricks incl. Special Custom Formats π
Format Painter - "Uninterrupted Click-ability" π
Defining Cell Style - for "one-click" universal change π
Transferring (Merging) Cell Style from one workbook to another π
Cell drag-n-drop Auto Fill Options + Activation Setting π
Cell drag-n-drop Auto Fill Options - Yr-end, Month-end, AlphaNumeric, Fill - Justify π
Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month π
Paste Special - Transpose vs. TRANSPOSE() π
Cell Referencing ($) - 1 of 2 π
Cell Referencing ($) - 2 of 2 π
Go To (Special) - Formulas with Errors π
Go To (Special) - Formulas without Errors π
Go To (Special) w. Ctrl+Enter π
Go To (Special) - Fill Intermittent Blank Cells - Case 2 π
Go To (Special) - Fill Intermittent Blank Cells - Case 1 π
Go To (Special) - Visible Cells w. Shortcut π
Vertical Sort - 1-level & 2-level π
Custom Sort π
Sort Trick - add alternate blank rows in-between existing rows π
Horizontal Sorting (Left to Right) - auto-realigning 100s of columns π
Filter - Basics π
Filter - Choosing the dataset correctly π
Filter analysis w. shortcuts π
Filter - SUBTOTAL() 9 vs. 109 series π
Filter - SUBTOTAL() for calculations π
Filter - Applying 2 or more Filters simultaneously on the same sheet π
Filter - Color Filter & Text Filter π
Advanced Filter - Basics π
Advanced Filter - More parameters π
Advanced Filter - Differential Criteria π
Dates - Important Concepts 1 of 2 π
Dates - Important Concepts 2 of 2 π
Date Formulas - DAY(), MONTH(), YEAR(), DATE() π
Date Formulas - TEXT() π
Date Formulas - WEEKDAY(), WORKDAY(), NETWORKDAYS() π
Date Formulas - NETWORKDAYS.INTL() for no. of business days calculations w. custom weekends/holidays π
Date Formulas - WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays π
Date Formulas - TODAY() and NOW() w. Shortcut π
Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates π
Data Validation - Drop Down List using hard coded text & pre-defined range π
Date Formulas - EDATE() for Financial Modeling, Budgets, Due Datesπ
Data Validation - Drop Down List using named range π
Data Validation - Numbers w. Error Alert and Input Message π
Data Validation - Text Length w. Error Alert π
Data Validation - Dates w. Error Alert and Circle Invalid Data π
Data Validation - Custom w. formula logic π
Grouping/UnGrouping Columns and Rows - 1 of 2 π
Grouping/UnGrouping Columns and Rows - 2 of 2 π
Grouping Trick: Changing placement of Grouping Button π
Cell Gridlines: Turning On/Off π
Trick - Hide/Unhide Rows and Columns and Freeze panes π
Pivot Table - Why should you learn? π
Pivot Table - How to create one? π
Pivot Table - Pre-requisites π
Pivot Table - Exploring Pivot Table grid (Fields) π
Pivot Table - Value Field Settings - "Summarize Values By" - Sum, Average, Count, Max etc. π
Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Grand Total π
Pivot Table - Value Field Settings - "Show Values As" - Percentage (%) of Column/Row Total π
Pivot Table - Grouping - Numbers π
Pivot Table - Refresh vs. Refresh All, Change Data Source and creating Dynamic Table Data Source π
Pivot Table - Grouping - Text π
Pivot Table - Grouping - Dates [Years, Months etc.] π
Pivot Table - Auto Refresh π
Pivot Table - Pivot Chart Shortcut and Sparklines π
Pivot Table - Report Filter - Generating 100s of reports in few seconds π
Pivot Table - Drill Down option π
Pivot Table - Slicer vs. Report Filter π
Pivot Table - Practice - Sales Data Analysis - 1 of 2I (%, Grouping, Chart, Report Filter Pages) π
Pivot Table - Practice - Sales Data Analysis - 2 of 2 (Slicer) π
Pivot Table - Practice - Closing Stock Analysis π
VLOOKUP() for Starters π
VLOOKUP w. TRUE vs. FALSE π
HLOOKUP() vs. VLOOKUP() π
VLOOKUP w. TRUE - applications
SUMIFS(): Conditional Summation (3 criteria) w. date range π
SUMIFS(): Conditional Summation (2 criteria) π
SUMIFS(): Conditional Summation (1 criteria) π
Reverse Lookup - INDEX() w. MATCH() - 2 of 2 π
Reverse Lookup - INDEX() w. MATCH() - 1 of 2 π
MATCH() - Basics π
Reverse Lookup - Question π
3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Answer π
3-D Lookup - VLOOKUP() w. MATCH() w. INDIRECT() - Question π
INDIRECT() w. Range Naming - Applications π
INDIRECT() - Basics π
MATCH() match_type: -1 vs. 0 vs. 1 π
2-D Lookup (Horizontal + Vertical) - HLOOKUP w. MATCH π
2-D Lookup - VLOOKUP w. MATCH - Practice Exercise π
2-D Lookup - VLOOKUP w. MATCH - Common Mistake #2 π
2-D Lookup - VLOOKUP w. MATCH - Common Mistake #1 π
2-D Lookup - VLOOKUP w. MATCH - Framework π
2-D Lookup (Vertical + Horizontal) - VLOOKUP w. MATCH π
SUMIFS(): Condition based Selective Cumulative Running Total π
COUNTIFS() - Single/Multiple Criteria: Duplicate Count, Instance No. π
OFFSET() with MATCH() π
Text Formulas β Change Case using UPPER(), PROPER() & LOWER() π
Text Formulas β Remove Leading, Trailing & excess Intermittent spaces using TRIM() π
Text Formulas β Using LEN() & TRIM() together π
Text Formulas β T(), N() & VALUE() π
Text Formulas β REPT() for artifical Column Chart in a Cell π
Text Formulas β REPT() & LEN() for adding leading zeroes before incomplete Cheque Nos. π
Joining data strings using CONCATENATE, & - to be updated π
Find & Replace β Using Wildcard character ( ? ) π
Find & Replace β Using Wildcard character ( * ) π
Find & Replace β Neutralising Wildcard characters to remove them from data π
Find & Replace β Word vs. Excel π
Find & Replace β Cell Format π
Text to Columns β Basics (Delimited) π
Text to Columns β Basics (Fixed Width) + Hidden Trick π
Text to Columns β Cleaning up numbers w. trailing minus sign π
Text to Columns β Cleaning up numbers w. leading "Dr/Cr" text π
Text to Columns β Correcting invalid Dates (DMY) π
Text to Columns β Correcting invalid Dates (YMD) π
Text Formulas β LEFT(), RIGHT() & SEARCH() - 2 of 2 π
Text Formulas β LEFT(), RIGHT() & SEARCH() - 1 of 2 π
Text Formulas β MID() & LEN() π
Text Formulas β REPLACE() & SUBSTITUTE() π
Logical formulas: ISNUMBER(), ISTEXT(), ISBLANK(), ISERROR(), ISFORMULA() π
Logical formulas: Basics of IF() and Nested IF() π
Conditional Formatting: Actual vs. Budget Comparison through Arrows π
Logical formulas: Combining IF() w. AND() & OR() π
Logical formulas: AND(), OR() π
Conditional Formatting: Data Bars, Color Scales, Icon Sets π
Conditional Formatting: w. Drop-down list based Input π
Conditional Formatting: Blanks, Errors, Values, Duplicates π
Conditional Formatting: Formula based (Colored rows based in user input) π
Conditional Formatting: Formula based w. AND() - 2-way input Coloring π
Conditional Formatting: Formula based w. COUNTIFS() - Highlighting inputs if from restricted list π
What IF Analysis β Using Form Control Buttons from Developer Tab (Spin Bar, Scroll Bar) π
What IF Analysis β Limitations of Spin Bar, Scroll Bar π
What IF Analysis β Data Tables Basics (Sensitivity Analysis) - 2 Inputs & 1 Output π
What IF Analysis β Goal Seek π
What IF Analysis β Scenario Managers π
What IF Analysis β Data Tables Basics (Sensitivity Analysis) - with PMT for Loan EMI π
What IF Analysis β Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Question π
What IF Analysis β Data Tables Basics (Sensitivity Analysis) - 2 Inputs & multiple Output - Answer π
Subtotal - Creating a 1-level SubTotal on a Sorted list π
Subtotal - Deleting a SubTotal π
Subtotal - Creating a 2-level SubTotal on a Sorted list π
Subtotal - Coloring the Subtotal rows with different Color π
A Subtotal - Trick to Merge blocks of cells with similar names π
Consolidate - 2 Dimensions π
Consolidate - 3 Dimensions π
Cell level Security π
Cell level Security Challenge π
File level Security π
Sheet level Security π
Printing - Rows to Repeat at Top (Print Titles) π
Printing - Gridlines π
Printing - Custom Header & Footer π
Printing - Page Order - Vertical vs. Horizontal π
Printing - Tricks for Financial Analysts - Check underlying formulas π
Printing - Print Entire Workbook π
Printing - Tricks for Financial Analysts - Cell Comments π
Comments - Shortcuts, Inserting Picture in Comment Box π
Split Windows, Viewing multiple Windows - For working with different workbooks, worksheets & scattered cell ranges simultaneously π
Hyperlinking (Ctrl+K) π
Excel VBA Tutorials [Part of Excel VBA Course]
Why learn Macros? π
Activate "Developer" tab π
Saving a Macro-enabled WorkBook (XLSM) π
Macro Security Settings π
Developer tab - Essential Buttons π
Head & Tail of a Macros (Sub Procedure & User Defined Function) π
"Click Here" button to run a Macros π
Macros - Viewing the code π
Macros - Record & Run π
Macro-free vs. Macro-enabled Workbook π
Visual Basic Editor (VBE) π
Personal Macro WB π
Ways to Run a Macros π
Using Google to locate useful VBA codes - 1 π
Using VBE and Macro Recorder to customise Macro code π
Using Google to locate useful VBA codes - 2 π
Objects, Properties, Values, Methods and Parameters 1 π
Objects, Properties, Values, Methods and Parameters 2 π
Code Color & Activating Edit Toolbar w. Comments π
Edit Toolbar - Indent, Bookmark π
Code writing shortcuts incl. Intellisense π
Immediate Window 01 π
Testing the Code π
Range Selection - Method 1 π
Range Selection - Method 2 & 3 π
Advantage of Range Selection - Method 2 & 3 π
Range Selection (Rows & Columns) - Method 4 π
Assigning values to selected range of cells π
Exercise 1 - Assigning values to selected range of cells π
Exercise 2- Assigning values to selected range of cells π
Multiple Range Selection - Method 5 π
Multiple Range Selection - Method 6 π
RangeSelectionWays - Others π
Offset - Basics π
Using Relative Reference in Macros π
Navigating between a Range of cells - Part 1 π
Navigating between a Range of cells - Part 2 π
Navigating between a Range of cells - Part 3 π
Exercise - Finding Last Row No. π
Exercise - Finding Last Column No. π
Exercise - Finding Next Row No. π
Variable Decl 01 π
Variable Decl 02 π
Pvt vs Public 01 π
Pvt vs Public 02 π
Option Explicit π
Difference - DIM vs. SET π
"For...Next" π
"If...Else...End If" nested within "For...Next" π
"Doβ¦While" π
"Doβ¦Until" π
"For Each...Next" π
"If...End If" nested within "For Each...Next" π
Exercise - "Do While" and Loop Counter π
"Withβ¦End With" - Example 1 π
"Selectβ¦Case" - Demonstration π
"Withβ¦End With" - Example 2 π
"Selectβ¦Case" - Explanation π
Speed up the code running - 1 π
Speed up the code running - 2 π
Speed up the code running - 3 π
Events - Workbook_Open and Worksheet_Activate π
Introduction π
Exercise 1 - Tracking Workbook Opens π
Exercise 2 - Tracking Workbook Save π
Event - Worksheet_BeforeDoubleClick π
Event - Worksheet_Change π
Event - Worksheet_Calculate π
Pivot Table - Refresh - WorkSheetActivate π
Input Box - 1 π
Introduction π
Input Box - 2 π
Input Box - 4 π
Input Box - 3 π
MsgBox - 1 π
Introduction π
MsgBox - 2 π
MsgBox - 3 π
MsgBox - 4 π
Form Controls vs ActiveX Controls π
MouseMove Event w. Appln Statusbar π
Introduction π
Exercise - Part 1 of 4 π
User Form - Basics π
Exercise - Part 2 of 4 π
Exercise - Part 3 of 4 π
Exercise - Part 4 of 4 π
Option Button - 1 π
Option Button - 2 π
Multi Page - Introduction π
Multi Page - 1 π
Multi Page - 2 π
Multi Page - 3 π
Check Box 001 π
Combo Box - Method 2 π
Combo Box - Method 1 π
UDF Intro 001 π
UDF Example 1 - ScoreResult with IF END IF π
UDF Example 2 - CellComments π
UDF Samples - Series A π
UDF Samples - Series B π
Split Workbook π
Protect - Unprotect Sheets π
Index of Hyperlinks to all Sheets π
Extract all Comments of a WBook to a new W Sheet π
Project_Automate Comment Insertion and Extraction π
Count Cells by Colors π
Combine WSheets π
Excel Dashboard Tutorials [Part Of Excel Dashboard Course]
MRTS Sales Dashboard Solution 02 π
MRTS Sales Dashboard Solution 01 π
MRTS Sales Dashboard Question π
MRTS Sales Dashboard Solution 03 π
MRTS Sales Dashboard Solution 04 π
MRTS Sales Dashboard Solution 06 π
MRTS Sales Dashboard Solution 05 π
Power Generation Tracker Question π
Power Generation Tracker Solution π
Introduction to Dashboard - Why, When how π
Design Tips and Ideal Data Formats π
Drawing Excel Charts π
Formatting cells and tables in excel π
Building Up and Down Indicators on Charts π
Trend Sparkline with variations π
Pie chart and formatting tips π
Bar Sparkline an Win and Loss Sparkline π
Two-axis charts π
Thermometer chart π
Stacked Chart π
Showing forcasting with line chart π
Bullet chart π
Spider chart π
Bubble chart π
Waterfall - New Charts in 2016 π
Format Specific Period, Data Point, Marker, XY, Positive and Negative π
Conditional Formatting in Graph π
MATCH and CHOOSE π
VLOOKUP and HLOOKUP π
Histogram Chart π
Pivot tables basics π
Indirect Function π
INDEX Function π
Pivot charts basics π
Form Control with example π
List controlled graph π
Check box controlled graph π
Chart with interactive legend π
Linking values in dashboard π
Building dashboard basic layout and formatting tips π
How to make lists in a dashboard π
Identifying of Unique items for a dashboard π
Overview of Dashboard π
Graph using Macro π
Using Dynamic functions π
Calculating values based on criteria π
Brief understanding on Dynamic Dashboards using Excel Macros π
Implementation of OFFSET for dynamic data π
Creating Trend Chart from 2 Pivot Table π
Using Macro recording for Trend Chart 01 π
Modifying the Chart Settings for better view π
Using macro to update pivot based on the Job Type π
Triggering macro with any change in sheets π
Creating Name Range using shortcut π
Use of MAXIF and MINIF with single and multiple parameters π
Create chart to represent multiple parameters effectively 01 π
Create chart to represent multiple parameters effectively 02 π
Preparing data for Spiderweb chart and change the graph setting π
Using macro to Hide and Unhide working sheets π
Inserting Trendline and formatting for better view π
Conclusion of all 3 dashboard π
Financial Modelling in Excel - Volume 1 [Part of Financial modeling course]
True vs False π
Timing Flag - Basics π
Timing Flag - Advanced Demo π
Timing Flag - Advanced Practice - Proj Completion Dt π
Timing Flag - Advanced Practice 2 - Timeline π
Timing Flag - Advanced Practice 3 - Start Flag π
Timing Flag - Advanced Practice 4 - Operation Flag π
Timing Flag - Advanced Practice 5 - Operation Days π
Timing Flag - Advanced Practice 6 - Operation Yr No π
Timing Flag - Advanced Practice 7 - Final π
Recurring Events Flag - Fixed Interval π
Recurring Events Flag - Customised Interval π
Funding Waterfall Structure - Demo with logics π
What is a Waterfall Structure π
Funding Waterfall Structure - Debt π
Funding Waterfall Structure - Equity π
Funding Waterfall Structure - Grant π
What is a Circular Reference π
What is iteration in context of Circular Reference π
Circularity Switch or Circuit Breaker - Example 1 π
Circularity Switch or Circuit Breaker - Example 2 π
MRTS Sales Dashboard Solution 06 π
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock