Advanced Excel Ninja

What is Advanced Excel Ninja ?


Advanced Excel training participants across Mumbai, Kolkata, Bangalore, New Delhi, Gurgaon, Chennai, Hyderabad, Pune, Nasik, Ahmedabad etc. ask us – What is “Advanced Excel Ninja”?

This is what we have to say:

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.

With the aim of equipping with this Ninja-level power, we have developed 2-day workshop, which has been conducted at prestigious companies, including PwC, EY, ITC, Tata Power, Tata Sky, Birla Corporation etc.



Initiate learning in your organization today – No budget needed


Your organization team members can start learning Excel (Basic to Intermediate-level) from our free videos (50+) at our partner website – Yoda Learning Solutions.

You can continue to learn super tips-n-tricks at our Youtube channel



2-day Workshop Curriculum of Advanced Excel Ninja


Following is the tentative list of topics covered in the 2-day workshop (can be customized):

 

Warm-up


  • Essential shortcuts
  • Absolute & Relative referencing ($)
  • ROUND() – for ANY nearest multiple no.
  • Working with Dates – Concept, Correction techniques
  • Date formulas: TEXT(), EOMONTH(), EDATE(), DATE()

Data Lookup & Calculations


  • VLOOKUP() – a practical perspective
  • VLOOKUP() w. TRUE – When & Why?
  • VLOOKUP() with MATCH() – 2-D lookup
  • INDIRECT() + Range Naming for 3-D lookup
  • INDEX() and 2 MATCH() – reverse lookup
  • SUMIFS() – for multiple criteria based sum, differential cumulative running total
  • COUNTIFS() – for conditional counting, instance no.

Pivot Table for multi-variable analysis


  • Computations %, Sum, Average, Count
  • Grouping (Clustering)
  • Generating multiple reports

Logical Statements


  • IF(), Nested IFs, AND(), OR()
  • Combo Logics; IFERROR()

MIS Reporting & Dashboard Techniques


  • Automatic row-wise Subtotal
  • Activate-Deactivate Gridlines
  • Data Validation (list)
  • Cell-Range Naming
  • Grouping

Data Cleaning – I


  • LEFT(), RIGHT(), MID(); LEN()
  • TRIM(), VALUE(), SUBSTITUTE()
  • Using & (ampersand) to join cells’ data – Concatenate
  • COUNTBLANK()

Data Cleaning – II


  • Find & Replace (using wildcard character – Asterisk * )
  • Go To (Special) with Ctrl+Enter
  • Text-to-Columns (Advanced)

Macros – Application


  • Developer tab; Visual Basic Editor (VBE)
  • Record & Run a Macro
  • Using Google to locate VBA code
  • Using Sample Code Snippets -Merging 100s of workbooks/worksheets in one, Splitting worksheets into multiple workbooks, Creating index of hyperlinks to worksheets


To be able to participate in the above program, participants are expected to have prior knowledge of –

  • Basic Sort & Filter; SUM(), MAX(), MIN(), AVERAGE(), Basic VLookup
  • Freeze Panes, Add/Delete Rows/Columns/WorkSheets
  • Join our Free Video Course.

Other topics available on request: Solver Add-In, Financial Formulas, OFFSET(), CHOOSE(), Array formulas, Advanced Charts (thermometer, 2 axis, combination etc.), 3-D Data Consolidation, Multi-Variable Sensitivity Analysis with Data Table (Data Table combined with Indirect() and range naming), Formula-based Conditional Formatting, Form Controls – Scroll Bar