 Introduction 

  Program Introduction 
Preview 
 Section 01  Excel Shortcuts 

  Best Excel Shortcuts  Part 1 of 4 
Preview 
  Best Excel Shortcuts  Part 2 of 4 

  Best Excel Shortcuts  Part 3 of 4 

  Best Excel Shortcuts  Part 4 of 4 

  How to create custom shortcuts using Quick Access Toolbar (QAT)? 

 Section 02  Essentials for Beginners 

  Three easy ways to do SUM or total of numbers 
Preview 
  Basics of MAX & MIN formula 

  Use of MAX formula in Finance (Tax & Billing) 

  Use of SMALL & LARGE formula in Finance 

  Using MEDIAN formula to remove outliers 

  Using Paste Special (Value, Transpose) for daily work 

  Paste Special (Transpose) vs TRANSPOSE Formula 

  Using COUNTBLANK to count blank cells 

  Difference between COUNT vs. COUNTA 

  Using Status Bar for Quick Calculations 

  Using SUMPRODUCT Function in Finance & Costing 

  Easiest way to calculate YoY Growth Percentage 

  What is POWER formula 

  How to calculate the maturity amount of Fixed Deposit 

  Basics of ROUND formula 

  Basics of ROUNDUP & ROUNDDOWN formula 

  Uses of ROUNDUP & ROUNDDOWN formula in Finance 

  Basics of MROUND formula 

  Advanced Trick of Round Formula in Finance 

  Using Autofill feature to create automatic list of Dates 

  Using Autofill feature to create automatic list of Numbers 

  Course Feedback 

 Section 03  Essential Formatting Tricks 

  7 ways to use Format Cells 
Preview 
  How to hide a text without coloring the cell white? 

  What is  Numbers stored as Text? 

  How to write full Cheque numbers with missing zeroes? 

  What is Superscript inside Format Cells? 

  Basics of formatting tabular datasets 

  How to format lengthy headings using Wrap Text & Alignment? 

  How is Format Painter used to copy & paste formats? 

  How to use Format Painter multiple times? 

  How to remove all formatting in one click? 

  Working with Tables 

  How to color multiple sheet tabs? 

  How to use Freeze Panes for Rows & Columns at the same time? 

  Grouping 

  Gridlines 

  How to create a simple dropdown list with Data Validation? 

  How to remove a dropdown list? 

  How to activate the Developer Tab in 20072019 in Excel? 

  How to create an interactive Spin Button for an Excel Dashboard? 

  How to create an interactive Scroll Bar for an Excel Dashboard? 

  7 Tricks of Comments  Basic to Expert 

  4 Tricks of Hyperlinking  Basic to Expert 

  How to hide a sheet so that no one can Unhide easily? 

  Course Feedback 

 Section 04  Sort & Filter 

  How to Sort numbers from largest to smallest? 

  How to Sort text alphabetically (AZ)? 

  How to Sort dates oldest to newest? 

  How to Sort rows by cell color? 

  Best Practice before you sort any dataset 

  How to do 2level Sorting? 

  The hidden trick of Horizontal Sorting 

  The hidden trick of Sorting using a custom list 

  The hidden trick to add a blank row between every row using Sort 

  Basics of Filter & Calculating SUM on the filter list 

  The most common mistake Filter users make while choosing datasets 

  Importance of SUBTOTAL formula in a Filtered dataset 

  SUBTOTAL formula's confusing parameters  Mystery Solved 

  Number Filter option in Filter 

  How to navigate a Filtered dataset using Shortcuts? 

  How is Advanced Filter different than General Filter? 

  The hidden trick to apply separate Filters on two datasets of the same worksheet? 

  The hidden trick to paste values on a Filtered dataset 

  How to use Slicer on a Filtered dataset? 

  Course Feedback 

 Section 05  Cell Reference ($) 

  Use of Cell Referencing ($) in formulas  Explained 

 Section 06  Dates 

  Why does a Date in Excel looks like a random number? 

  How to calculate the duration using two dates with timestamp? 

  Date formulas  DAY, MONTH, YEAR & DATE  Part 1 

  Date formulas  DAY, MONTH, YEAR & DATE  Part 2 

  Date formulas  TEXT & WEEKDAY  Part 1 

  Date formulas  TEXT & WEEKDAY  Part 2 

  Why is EDATE referred as Expiry Date? 

  Why is EOMONTH referred as End of Month? 

  Date formulas  TODAY & NOW with Shortcuts 

  The hidden trick to rectify dates format using Text to Columns 

  How to use WORKDAY.INTL formula to calculate project deadline? 

  How to use NETWORKDAYS.INTL to calculate working days between two dates? 

  The hidden formula of DATEDIF 

  How to find the 1st, 3rd and 5th Saturday of any month 

  Course Feedback 

 Section 07  Logical Functions 

  Basics of Logical Formula  Part 1 

  Basics of Logical Formula  Part 2 

  Basics of Logical Formula  Part 3 

  Quick Recap of Logical Formulas with advanced exercises 

  Using multiple Logical formulas  IF, AND, OR in an advanced exercise 

  Logical Check Formulas for Text, Numbers, & Errors including IFERROR 

  Course Feedback 

 Section 08  Data Cleaning 

  How to remove duplicate values? 

  Using UPPER, PROPER, & LOWER formulas to change the case of the text 

  Using TRIM formula to remove excess spaces in a sentence 

  Using VALUE formula to convert numbers stored as text to a number format 

  Using LEN formula to calculate the number of characters in a cell 

  Basics of RIGHT, LEFT & MID formulas 

  SEARCH vs FIND 

  SEARCH & MID 

  How to find and delete all the error values at once? 

  Shortcut to make best use of Go To (Special) technique 

  How to fill intermittent blank cells using Go To (Special) technique? 

  Other uses of Ctrl G  Formula, Constants, Visible cells 

  Using Text to Column feature to split data values  Part 1 (Delimited) 

  Using Text to Column feature to split data values  Part 2 (Fixed Width) 

  When is Text to Column feature used to change the Data Format (General, Text)? 

  How to convert a series of email IDs to a vertical list for Attendance? 

  How can Text to Columns help in writing correct SUM & VLOOKUP formulas? 

  Using CONCATENATE and Ampersand (&) to join values 

  Find & Replace  Part 1 (with Wildcard Character) 

  Find & Replace  Part 2 (MS Word vs MS Excel) 

  Quick Recap of Advanced Find & Replace 

  SUBSTITUTE  the formula version of Find & Replace 

  FlashFill  Basics to Advanced 

  Project  FlashFill, T2C, SUBSTITUTE  Bank Data 

  Project  Electricity Consumption  Method #1 

  Project  Electricity Consumption  Method #2 

  Course Feedback 

 Section 09  Pivot Table 

  Pivot Table (AZ) Part 1 

  Pivot Table (AZ) Part 2 

  Expertlevel Pivot Table Trick using Power Query  Split to Rows 

  Expertlevel Pivot Table Trick using Power Query  Unpivot Column 

  Extra Files 

 Section 10  Lookup Formulas (AZ) 

  Basics of VLOOKUP for first time users  Why do we need it? 

  Basics of VLOOKUP for first time users  fix Table Array using $ 

  Basics of VLOOKUP for first time users  TRUE vs FALSE  Part 1 

  Basics of VLOOKUP for first time users  TRUE vs FALSE  Part 2 

  Common mistakes of VLOOKUP users  Why do we get N/A? 

  How to do interworksheet VLOOKUP? 

  Vlookup vs. Hlookup  Simplified 

  Using VLOOKUp with TRUE for doing Debtors Ageing Analysis 

  Using VLOOKUp with TRUE for doing Score Grading 

  Using VLOOKUp with TRUE for doing Food Quality Grading 

  Using VLOOKUp with TRUE for calculating correct tax rate as per applicable date 

  Basics of MATCH formula  Part 1 

  Basics of MATCH formula  Part 2 

  How to do 2D Lookup using VLOOKUP & MATCH  Example 1 

  How to do 2D Lookup using VLOOKUP & MATCH  Example 2 

  How to do interworksheet 2D Lookup using VLOOKUP & MATCH?  Example 1 

  How to do interworksheet 2D Lookup using VLOOKUP & MATCH?  Example 2 

  LOOKUP vs. VLOOKUP vs. HLOOKUP vs. MATCH 

  How to do Reverse Lookup using INDEX & MATCH formula  Part 1 

  How to do Reverse Lookup using INDEX & MATCH formula  Part 2 

  Exercise  Using INDEX & MATCH formula to do Reverse Lookup 

  Fuzzy Lookup can do what VLOOKUP can't  Lookup for data with different spelling 

  Basics of INDIRECT formula 

  Create a Dashboard from 25+ worksheets using INDIRECT formula 

  Use VLOOKUP & INDIRECT formula to pull data from multiple sheets 

  How to do 3D Lookup using VLOOKUP, MATCH, INDIRECT & name ranges? 

  Basics  Using OFFSET formula to create Dynamic Ranges 

  Using OFFSET to create dynamic calculations  Part 1 (Intermediate) 

  Using OFFSET to create dynamic calculations  Part 2 (Advanced) 

 Section 11  Conditional Aggregation 

  Basics of COUNTIF formula 

  Basics of SUMIF formula 

  Basics of AVERAGEIF formula 

  COUNTIFS & SUMIFS  Part 1 (Intermediate) 

  COUNTIFS & SUMIFS  Part 2 (Advanced) 

  Exercise  Using SUMIFS & COUNTIFS with 3 criteria 

  Exercise  Using SUMIFS to find sum of values between two dates 

  Using COUNTIFS to find duplicates and reconcile two lists 

  The hidden trick of COUNTIFS to calculate running count based on the ID value 

  The hidden trick of COUNTIFS to do VLOOKUP for duplicate values 

  The hidden trick of SUMIFS to calculate running sum total based on the ID value 

 Section 12  Conditional Formatting 

  Conditional Formatting  Color cells based on errors, blanks, duplicates, userdefined value 

  Conditional Formatting  Color cells using Data Bars, Icon Sets, Color Scales 

  Create a Dashboard for comparing Actual vs Budget using Conditional Formatting 

  Using Conditional Formatting for changing the cell color based on list value chosen 

  Formulabased Conditional Formatting  color the entire row based on userselected value 

  Formulabased Conditional Formatting  color the cell that matches two user criterias 

  Formulabased Conditional Formatting  Highlight cells that have prohibited values 

 Section 13  What If Analysis 

  Basics of Data Table feature of WhatIf Analysis 

  Using Data Table feature of WhatIf Analysis with INDIRECT formula 

  Advanced use case of Data Table feature for Loan EMI payments 

  Limitations of using Scroll Bar & Spin Button 

  Basics  Using Goal Seek to backcalculate 

  Basics  Scenario Manager for Best, Basics  Scenario Manager for Best, Worst & Base Case 

 Section 14  Report Consolidation 

  The hidden trick of Consolidation using SUM for multiple sheets 

  How to use CONSOLIDATE feature of Excel?  Part 1 

  How to use CONSOLIDATE feature of Excel?  Part 2 

  How to use SUBTOTAL feature of Excel using one criteria? 

  How to use SUBTOTAL feature of Excel using two criteria? 

  The hidden trick of SUBTOTAL feature & Go To (Special) to merge cells into blocks 

 Section 15  Printing 

  Top 10 Printing Tricks & Settings 

 Section 16  Workbook & Worksheet Security 

  Level of Password Security  File vs. Sheet vs. Workbook Structure 

  How to hide a sheet so that no one can Unhide easily? 

  How to remove “Protect sheet” Password with Notepad? 

  How to remove remove “Protect workbook” Password with Notepad? 

 Section 17  Special Tricks 

  The hidden trick to Compare two Excel files 

  The hidden trick to join cell values without using CONCATENATE 

  The hidden trick to activate Data Entry Form 

  How to Copy Text from Image  OneNote Tutorial 

  PDF to Word Converter  Without Software 

 Section 18  Advanced Excel Projects for Finance 

  Convert "Dr" & "Cr" suffix of Tally dayasets into positive & negative values 

  Cleanup complex Bank Statement sheet of Borrowers 

  Analyse payments made to Suppliers using FlashFill, VLOOKUP & Pivot Table 

  Extract multiple voucher nos. from a cell to create a list 

  Create a Frequency Distribution of sales ticket size using Pivot Table? 

  Calculate Stock Revaluation writeoff amount using VLOOKUP with TRUE and MATCH 

  Form 26AS  TDS  What is Form 26AS? 

  PAN Assessee Status  using MID & VLOOKUP 

 Section 19  Advanced Excel Projects for Sales 

  Calculate Sales Commission using advanced SUMIFS and VLOOKUP with TRUE 

  Allocate sales value of a project to the individual team members 

  Sales Visualization  without Charts 

 Section 20  Office 365 Updates 

  UNIQUE 

  Concat & TextJoin 

  Project  TextJoin & IF 

  IFS & SWITCH 

  MAXIFS & MINIFS 

  Filter 

  SORT 

  SORTBY 

  Sequence 

  LET 

 Section 21  Basics of Macro 

  Why do we learn Excel VBA Macros? 

  How to learn Excel VBA Macros? 

  How to enable Developer Tab in Excel? 

  Basics of Developer Tab in Excel 

  Important Macro Security Settings in Excel 

  How to save Macros in Excel for future use? 

  How to create a Macros in Excel? 

  How to run a Macro in all open Workbooks? 

  Using a Button to run a Macros in Excel 

  Running a Macro in different ways in Excel 

  Understanding VBA Workspace in Excel (Basics) 

  Watch a Macro being recorded 

  Searching Google for VBA Codes  using the 3 magic words 

  Searching Google for VBA Codes  in Blogs vs Forum 

 Excel Mastery eBooks 

  Excel Mastery eBook 1 

  Excel Mastery eBook 2 

  Excel Mastery eBook 3 

  Excel Mastery eBook 4 

  Excel Mastery eBook 5 
