Advanced Excel Training Institute in Delhi


Advanced Excel

Section-1 What-If Analysis

  1. Goal Seek
  2. Data Tables (PMT Function)
  3. Solver Tool
  4. Scenario Analysis

Section-2 Data Validation

  1. Number, Date & Time Validation
  2. Dynamic Dropdown List Creation using Data Validation – Dependency List
  3. Custom validations based on a formula for a cell
  4. Text and List Validation

Section-3 Logical Analysis

  1. If Function
  2. Complex if and or functions
  3. Nested If
  4. How to Fix Errors – iferror

Section-4 Lookup Functions

  1. Vlookup / HLookup
  2. Vlookup with Helper Columns
  3. Creating Smooth User Interface Using Lookup
  4. Index and Match
  5. Reverse Lookup using Choose Function
  6. Nested VLookup
  7. Worksheet linking using Indirect

Section-5 Arrays Functions

  1. What are the Array Formulas, Use of the Array Formulas?
  2. Array with if, len, and mid functions formulas.
  3. Basic Examples of Arrays (Using ctrl+shift+enter).
  4. Advanced Use of formulas with Array.
  5. Array with Lookup functions.

Section-6 Pivot Tables

  1. Creating Simple Pivot Tables
  2. Classic Pivot table
  3. Basic and Advanced Value Field Setting
  4. Calculated Field & Calculated Items
  5. Grouping based on numbers and Dates

Section-7 Excel Dashboard

  1. Planning a Dashboard
  2. Adding Dynamic Contents to Dashboard
  3. Adding Tables and Charts to Dashboard

Section-8 Slicers and Charts

  1. Using SLICERS, Filter data with Slicers
  2. Various Charts i.e. Bar Charts / Pie Charts / Line Charts
  3. Manage Primary and Secondary Axis

Section-9 Introduction to VBA

  1. What Is VBA?
  2. Procedure and functions in VBA
  3. Recording a Macro
  4. What Can You Do with VBA?

Section-10 Variables in VBA

  1. What are Variables?
  2. Using Non-Declared Variables
  3. Using Const variables
  4. Variable Data Types

Section-11 Inputbox and Message Box Functions

  1. Customizing Msgboxes and Inputbox
  2. Reading Cell Values into Messages
  3. Various Button Groups in VBA

Section-12 If and select statements

  1. Simple If Statements
  2. Defining select case statements
  3. The Elseif Statements

Section-13 Looping in VBA

  1. Introduction to Loops and its Types
  2. Exiting from a Loop
  3. Advanced Loop Examples
  4. The Basic Dos and For Loop

Section-14 Worksheet / Workbook Operations

  1. Merge Worksheets using Macro
  2. Split worksheets using VBA filters
  3. Worksheet copiers
  4. Merge multiple excel files into one-sheet

Section-15 Mail Functions – VBA

  1. Using Outlook Namespace
  2. Outlook Configurations, MAPI
  3. Send automated mail