Advanced Excel Training

Advanced Excel training participants across Mumbai, Kolkata, Bangalore, New Delhi, Gurgaon, Chennai, Hyderabad, Pune, Nasik, Ahmedabad etc.

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.

Pre -Training Video

Corporate Training

    Enquiry For Corporate Training:  +91-8080042523


    Program Name: Advanced Excel Ninja
    Software: Microsoft Excel
    Level: Advanced (L2 and L3)
    Duration: 2 days
    Document Update Date: 17-07-2019

    Introduction: This 2-days program focuses on advanced lookups and aggregation formula. It dives in advanced data cleaning tricks, data analysis, and basics of macros. Additionally, uncommon shortcuts and time saver tricks are included.

    Note: This program is ideal for learners who use Sort & Filter extensively. The program may seem fast-paced for a user who find it difficult to use Sort & Filter. It is advisable to attend both days of the program.

    Day 1: Module #1 to #4 (part); #6
    Day 2: Module #4 (part) to #5; #7 to #8

    #1:  Essential Shortcuts | 0:30 min

    → Learning pre-requisites: Paste Special – Value
    → Aim: Work faster with effective shortcuts

    → Flow:

    • Data sets Navigation, Selection & Deletion
    • Show all formulas (Ctrl ~)
    • Inserting today’s date (Ctrl 😉
    • Date format (Ctrl Shift 3)
    • Using $ effectively using (F4)

    #2: Data Cleaning | 1:15 min

    → Learning pre-requisites: Basics of Find & Replace, Text to Columns, Filter; LEFT & RIGHT functions
    → Aim: Data cleaning precedes analysis

    → Flow:

    • Using GO TO Special to detect/select cells which are blank, hard-coded, formula driven
    • Using GO TO Special with Ctrl-Enter to fill intermittent blank cells
    • Using Machine Learning in Excel to clean data sets in seconds – FlashFill (v. 2013 feature)
    • How to train the machine of FlashFill for lightning fast complex data cleaning
    • Using TEXT function to extract day from a date. E.g. Sun, Mon…
    • Using VALUE function to cleanse date, time, nos stored as text
    • Using SUBSTITUTE function – the formula version of Find and Replace
    • Using Find & Replace with wildcard character *

    #3: Data Analysis using Pivot Table | 1:00 min

    → Learning pre-requisites: Output of the data used in the Module “Data Cleaning” above
    → Aim: Using Pivot Table along with data visualization tricks to analyze data

    → Flow:

    • Basics
    • Essential Setting (Classic View)
    • Date Grouping
    • Move column field headings to right / left
    • Calculating Count, Sum, %
    • Color-scales based Conditional Formatting (Heat Map)
    • Sparklines (in-cell trendline chart)
    • Slicer (user-driven filter) – Creating them and editing the no. of columns in a Slicer
    • PivotChart Shortcut (F11)
    • Creating 100s of worksheets in seconds

    #4: Lookup Functions | 2:00 min

    → Learning pre-requisites: Basics of VLOOKUP, $ (a 5-mins. fast paced brush-up can be included)
    → Aim: Gain absolute mastery over data lookup formulas

    → Flow:

    • Overview of different forms of Lookup (use-case) – 1D exact, slabs-based, 2D, reverse, fuzzy
    • VLOOKUP with TRUE – doesn’t mean approximate match
    • VLOOKUP with TRUE vs. complex Nested IF statements
    • VLOOKUP with MATCH for 2D lookup
    • Reverse Lookup with INDEX and MATCH
    • Fuzzy Lookup for similar names with spelling variations. E.g. India Motors Pvt Ltd vs. India Motor Private Ltd

    → Case Study Practice | 1:30 min

    #5: Aggregation Functions | 0:45 min

    → Learning pre-requisites: Basics of SUMIF, COUNTIF, $ (a 5-mins. fast paced brush-up can be included)
    → Aim: Gain absolute mastery over formulas for data consolidation and aggregation

    → Flow:

    • SUMIFS with 2 criteria
    • SUMIFS with range logic (E.g. from-to date)
    • Using COUNTIFS to calculate running count based on any primary key. E.g. 1st, 2nd & 3rd-time occurrences of any key – Used for developing VLOOKUP models for duplicate entries

    #6:  Use case of important functions | 1:15 min

    → Learning pre-requisites: None
    → Aim: Understanding the use case of several Excel functions which are simple and yet powerful.

    → Flow:

    • EDATE – Calculating expiry date. E.g. 3 months contract
    • EOMONTH – Calculating 10th of next month for due dates.
    • LEFT, RIGHT, MID – Extract characters from a cell
    • TRIM, SUBSTITUTE – Cleaning data of a cell
    • PROPER, UPPER, LOWER – Change case of the text
    • IF, AND, OR – Combination
    • Joining strings
    • Text to Columns to clean dates & change format

    #7:  Leveraging Google to locate readymade Macros code and editing them to automate tasks | 3:00 min

    → Learning pre-requisites: Internet connection for Google search
    → Aim: Hands-on approach to creating customized Macros to fully / partially automate repetitive tasks

    → Flow:

    • Why learning Macros is a “MUST”
    • Record & Run a Macros
    • Linking a button to a Macros
    • Understanding the basic architecture of a VBA code (Sub, End Sub, Dim)
    • The right technique to search Google for readymade codes and choosing the best amongst them
    • Forum vs. Blogs
    • How to draft appropriate search terms
    • Sub Procedure vs. User defined function (UDF)
    • Basic DOs & DONTs while searching for VBA codes on Google
    • Exercises
    • Create an index of links to worksheets with addl. customization & Worksheet activate event
    • Split a table in multiple sheets based on filter
    • Insert rows based on cell value

    #8:  Non-Excel time saver tricks | 0:30 min

    → Learning pre-requisites: None
    → Aim: Select non-Excel tricks

    → Flow:

    • Word Macros to automate pressing Delete-Space on PDF copied text
    • Extract text from scanned images without downloading any software or uploading any files to 3rd party sites

    → Case Study Practice | 2:00 min

    #9: Wrap-Up – Link to post-training recap video (16 mins.) Post Training

    •  Trainer shares a link through which participants can sign up and get lifetime access to the post-training recap video.

    Advanced Excel Training Gurgaon, Advanced Excel Training Pune, Advanced Excel Training Chennai, Advanced Excel Training Delhi

    Request Callback

      how can we help you?

      Drop Email For Corporate Excel Training Across India

      Company Profile

      “We had endorsed services of Rishabh Pugalia last month for a learning group within the Tata Sky Customer Operations team. His feedback from the learner group has been fantabulous where they found his courseware easy to grasp and retain, trainer skills great and the overall program design relevant to their work area”

      Swati Saha Gosh
      Lead-Training, Tata Sky

      Training & Consulting

      Power BI Training

      PowerBI is a cutting edge visualization tool. The family also includes: Power Query (self-service ETL) and PowerPivot (data modelling tool)

      read more
      Alteryx Corporate Training
      Alteryx Training

      Alteryx Designer for Basic, Intermediate & Advance Alteryx users. It introduces the User Interface and works through core Alteryx capability.

      read more
      Spotfire Training For Corporates
      Spotfire Training

      TIBCO Spotfire is a visual analytics tool that designs, develops, and distributes in-memory analytics software for next generation business intelligence.

      read more
      Excel Training

      Advanced Excel Training  program focuses on advanced lookups, aggregation formula, uncommon shortcuts and time saver tricks

      read more
      Tableau Corporate Training Mumbai
      Tableau Training

      The Program focuses on the navigating through the Interface, fundamentals of creating Visualisation, and to use the data correctly.

      read more
      PMP Corporate Training
      PMP Training

      Managers attending the course would learn global best practices in project management and use the same in the management of projects

      read more
      PowerPoint Training

      Powerpoint focuses on SPEED, AESTHETICS & STORYTELLING. Pre-requisite are inserting shapes, text box, and SmartArt.

      read more
      Financial Modeling Training
      Financial Modeling Training

      The Training Program will facilitate them to thoroughly Understand sturdy Money Models and Build Robust Financial Models.

      read more
      KNIME Corporate Training
      KNIME Training

      KNIME is a platform built for powerful analytics on a GUI based workflow. You don’t have to code to be able to work using KNIME and derive insights.

      read more