Advanced Excel Tutorials – Ninja Level Program

What is Advanced Excel Ninja ?

For an experienced professional, it’s easier to work with different techniques and formulas of Advanced Excel in a “standalone” mode. E.g. Just VLookup() formula or Consolidate technique.

However, you will be able to extract the Ninja-level power from the different formulas and techniques, if you can nest different formulas together and combine various techniques. E.g. VLookup() + Match() with Indirect() and range naming will allow you to build a 3-dimensional lookup with Ninja-level data pull-up power. Now learn these tricks at your own pace through our Videos and Excel Case Studies.


Video Tutorials on Advanced Excel, PowerPoint, Macros, Word, Outlook, Dashboard, Charts etc. All videos access is just a click away. Click Here


KEY FEATURES OF THE PREMIUM ACCOUNT

  • 170+ Video Lectures (Total 11+ hrs.)
  • Watch the Videos any number of times for LIFETIME – no expiry
  • 30-day money refund policy (if unsatisfied)
  • Downloadable Case Study Files + eHandBook
  • Access from any PC & Mobile device, any location
  • Most suitable for self-paced learning, in-house training

    and development and for aspiring MS Excel Trainers


CURRICULUM

SECTION 1


LECTURE 1 – Introduction + Downloadable files
LECTURE 2 – Warm-up – Key Shortcuts
LECTURE 3 – Formula Tricks – SUM, AUTOSUM, MAX, MIN, AVERAGE
LECTURE 4 – Formula Tricks – SUMPRODUCT, POWER, ROUND, MROUND
LECTURE 5 – Formula Tricks – COUNT vs. COUNTA
LECTURE 6 – Formatting Tricks: Table Concept, Using Format Painter uninterruptedly etc.
LECTURE 7 – Auto fill options – Variations & Settings
LECTURE 8 – Paste Special – Value, Transpose; Formula TRANSPOSE()
LECTURE 9 – Range Selection; GO TO – Special

SECTION 2


LECTURE 10 – Multi-level Sort, Custom Sort, Color Sort
LECTURE 11 – Filter & SUBTOTAL() formula
LECTURE 12 – Advanced Filter I (Differential Filter Criteria)
LECTURE 13 – Advanced Filter II (Differential Filter Criteria)
LECTURE 14 – Cell Referencing using $ – Introduction
LECTURE 15 – Cell Referencing using $ – Practice #1
LECTURE 16 – Cell Referencing using $ – Practice #2
LECTURE 17 – Cell Referencing using $ – Practice #3
LECTURE 18 – Cell Referencing using $ – Special Trick
LECTURE 19 – Date Concepts and Format
LECTURE 20 – Date Formulas-DAY, MONTH, YEAR, DATE, EDATE, EOMONTH, TEXT, WORKDAY, NETWORKDAYS
LECTURE 21 – Date Cleaning techniques using Text-to-Columns
LECTURE 22 – Dashboard I: Grouping, Data Validation (List), Cell-Range Naming
LECTURE 23 – Dashboard II: Form Controls Buttons
LECTURE 24 – Dashboard III: Hide/Unhide Columns-Rows, Freeze Panes

SECTION 3


LECTURE 25 – VLOOKUP Introduction – Where/Why/How to use
LECTURE 26 – VLOOKUP – Pre-requisites and Common Pitfalls
LECTURE 27 – VLOOKUP – True (1) vs. False (0)
LECTURE 28 – VLOOKUP Practice with IFERROR
LECTURE 29 – HLOOKUP
LECTURE 30 – Using MATCH with VLOOKUP/HLOOKUP – 2×2 matrix lookup
LECTURE 31 – VLOOKUP with MATCH – Practice #1
LECTURE 32 – VLOOKUP with MATCH – Practice #2
LECTURE 33 – HLOOKUP with MATCH – Practice #3
LECTURE 34 – SUMIFS – Introduction
LECTURE 35 – SUMIF vs. SUMIFS, AVERAGEIFS, COUNTIFS
LECTURE 36 – COUNTIFS for duplicate detection; Remove Duplicate

SECTION 4


LECTURE 37 – Data Validation (Numbers, Dates, Text length)
LECTURE 38 – INDEX with MATCH – Reverse 2-way Lookup
LECTURE 39 – INDEX with MATCH – Practice #1
LECTURE 40 – INDEX with MATCH – Version 2
LECTURE 41 – Pivot Table #1 (Payroll)
LECTURE 42 – Pivot Table #2 (Payroll)
LECTURE 43 – Pivot Table #3 (Payroll)
LECTURE 44 – Pivot Table – Practice (Inventory)
LECTURE 45 – SubTotal – Automatic row-wise subtotal #1
LECTURE 46 – SubTotal – Automatic row-wise subtotal #2
LECTURE 47 – SubTotal – Automatic row-wise subtotal #3

SECTION 5


LECTURE 48 – Using CONCATENATE, & – to join data strings
LECTURE 49 – Text-to-Columns – Delimited
LECTURE 50 – Text-to-Columns – Fixed Width
LECTURE 51 – Text-to-Columns – Tricks
LECTURE 52 – Find & Replace – Advanced
LECTURE 53 – Text Formulas I – UPPER, PROPER, LOWER, TRIM, T, N, REPT
LECTURE 54 – Text Formulas II – LEFT-RIGHT-MID, LEN, SEARCH
LECTURE 55 – Text Formulas III – SEARCH vs. FIND
LECTURE 56 – Text Formulas IV – REPLACE, SUBSTITUTE

SECTION 6


LECTURE 57 – Logical formulas I – IF, Nested IFs
LECTURE 58 – Logical formulas II – AND, OR with IF
LECTURE 59 – Logical formulas III – more case studies
LECTURE 60 – ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR
LECTURE 61 – Conditional Formatting I (Blanks, Errors, Values, Duplicates)
LECTURE 62 – Conditional Formatting II (Formula-based)
LECTURE 63 – Conditional Formatting III (Data Bars, Color Scales, Icon Sets)

SECTION 7


LECTURE 64 – What IF Analysis – Scenario Manager
LECTURE 65 – What IF Analysis – Goal Seek
LECTURE 66 – What IF Analysis – Data Tables
LECTURE 67 – What IF Analysis – Using Form Control Buttons

SECTION 8


LECTURE 68 – Tables – Concept and Applications
LECTURE 69 – 3-D Data Consolidation from same/different Workbooks #1
LECTURE 70 – 3-D Data Consolidation from same/different Workbooks #2
LECTURE 71 – Formula Auditing techniques
LECTURE 72 – File Security & Password Protection #1
LECTURE 73 – File Security & Password Protection #2
LECTURE 74 – Printing
LECTURE 75 – Comments – Picture and Text
LECTURE 76 – Split Windows, Viewing multiple Windows
LECTURE 77 – Hyperlinking

SECTION 9


LECTURE 78 – Charts – Basic Concepts and Elements
LECTURE 79 – Basic Charts – Bar, Column, Pie
LECTURE 80 – Special Charts: Thermometer Charts
LECTURE 81 – Special Charts: Multi-axis charts
LECTURE 82 – Special Charts: Exploded Pie charts
LECTURE 83 – Chart tips-n-tricks #1
LECTURE 84 – Chart tips-n-tricks #2

SECTION 10


LECTURE 85 – Macros – Overview, Developer tab, Settings
LECTURE 86 – Macros – Recording, Running; Using Buttons to run Macros

SECTION 11


LECTURE 87 – INDIRECT()
LECTURE 88 – OFFSET() with MATCH()

SECTION 12


LECTURE 89 – Pivot Table Trick
LECTURE 90 – 3D Data Lookup using VLookup, Match, Indirect, Naming


Click Here to Register for our Premium Advanced Excel Online Video Tutorials