Advanced Excel Ninja

What is Advanced Excel “Ninja”?


Advanced Excel training participants across Mumbai, Kolkata, Bangalore, New Delhi, Gurgaon, Chennai, Hyderabad, Pune 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 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 organisation today – No budget needed


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

You can continue to learn super tips-n-tricks at our Youtube page. Click Here



2-day workshop Curriculum


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

 

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 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 (50+ videos). Click Here

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