Data Analytics – Excel VBA

PHP training gurgaon

Click here to visit Excel Training Institute

Advance Excel VBA Training Overview

  • Overview of Analytics – Whats is Data Analytics, Data Modeling and Data Mining
  • Overview of excel basics, worksheet optimization, and data cleaning in excel
  • How to make your report effective (Data visualization) using Custom and Conditional formatting
  • Aggregate functions (Sum, 3-D functions, average, count, countifs, sumifs etc)
  • Date and time functions( EOMonth, DateDiff, NetworkDays, Workdays, Weeknum, Today, Month, Year etc)
  • Summarize data using Pivot table and charts
  • Data cleaning and validations in Excel
  • Basic and Advanced Filters on various scenarios
  • Lookups – VLookup, Reverse Lookup, Index Match, Indirect and dependency list
  • Logical Functions – Super logical If, nested if, and, or functions, how to combine logical functions
  • Various Text Functions – How to use find, substitute, len, trim,exact, left, right, mid, Upper and Lower functions
  • What if Analysis (Goal Seek, Data Tables, EMI Calculation, Solver Tool)
  • Dashboard Designing in Excel
  • Arrays Functions (CSE Functions in Excel)
  • Data Validations tools
  • Data forecasting tools
  • VBA Macros – (How to record macros, UDF, Variable, control flow, etc)
  • VBA Macros – If else Statements
  • VBA Macros – With statements
  • VBA Macros – For, Foreach and Do until Loop
  • VBA Macros – Fiters and Reporting
  • VBA Macros – How to automate mail functions in VBA
  • VBA Macros – How to create mergers

Course Details

  • EXCEL INTRODUCTION
  • Spreadsheet overview & navigation
  • Basic concepts
  • Selection techniques
  • Shortcut keys
    • CUSTOMIZING EXCEL
  • Customizing the Ribbon
  • Using and Customizing AutoCorrect
  • Changing Excel’s Default Options
    • USING BASIC FUNCTIONS
  • Using Functions – Sum, Average, Max,Min, Count, Counta
  • Absolute, Mixed and Relative Referencing
    • FORMATTING AND PROOFING
  • Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
  • Basic conditional formatting
    • MATHEMATICAL FUNCTIONS
  • SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs
    • PROTECTING EXCEL
  • File Level Protection
  • Workbook, Worksheet Protection
    • TEXT FUNCTIONS
  • Upper, Lower, Proper Functions
  • Left, Mid, Right Functions
  • Trim, Len, Exact Functions
  • Concatenate Function
  • Find, Substitute Functions
    • DATE AND TIME FUNCTIONS
  • Today, Now Day, Month, Year Date Functions
  • DateDif, DateAdd EOMonth, Weekday Functions
    • ADVANCED PASTE SPECIAL TECHNIQUES
  • Paste Formulas
  • Paste Formats and Images
  • Paste Validations
  • Transpose Tables
    • NEW IN EXCEL 2013 / 2016 & Excel 365
  • New Charts -Tree map & Waterfall Sunburst, Box and whisker Charts
  • Combo Charts – Secondary Axis
  • Adding Slicers Tool in Pivot & Tables
  • Using Power Map and Power View
  • Forecast Sheet Sparklines -Line, Column & Win/ Loss
  • Using 3-D Map New Controls in Pivot Table – Field, Items and Sets
  • Various Time Lines in Pivot Table
  • Auto complete a data range and list
  • Quick Analysis Tool
  • Smart Lookup and manage Store
  • SORTING AND FILTERING
  • Filtering on Text, Numbers & Colors
  • Sorting Options Advanced Filters on 15-20 different criteria(s) EXCEL,
    • ADVANCED EXCEL
  • WHAT IF ANALYSIS
  • Goal Seek Scenario Analysis Data Tables (PMT Function)
  • Solver Tool
    • LOGICAL FUNCTIONS
  • If Function How to Fix Errors – iferror
  • Nested If Complex if and or functions
    • DATA VALIDATION
  • Number, Date & Time Validation
  • Text and List Validation
  • Custom validations based on formula for a cell
  • Dynamic Dropdown List Creation using Data Validation – Dependency List
    • LOOKUP FUNCTIONS
  • Vlookup / HLookup Index and Match
  • Creating Smooth User Interface Using Lookup
  • Nested VLookup Reverse Lookup using Choose Function
  • Worksheet linking using Indirect
  • Vlookup with Helper Column
    • PIVOT TABLES
  • Creating Simple Pivot Tables Basic and Advanced Value Field Setting
  • Classic Pivot table Grouping based on numbers and Dates
  • Calculated Field & Calculated Items
  • ARRAYS FUNCTIONS
  • What are the Array Formulas, Use of the Array Formulas?
  • Basic Examples of Arrays (Using ctrl+shift+enter).
  • Array with if, len and mid functions formulas.
  • Array with Lookup functions.
  • Advanced Use of formulas with Array.
    • CHARTS AND SLICERS
  • Various Charts i.e. Bar Charts / Pie Charts / Line Charts
  • Using SLICERS, Filter data with Slicers
  • Manage Primary and Secondary Axis
  • EXCEL DASHBOARD
  • Planning a Dashboard
  • Adding Tables and Charts to Dashboard
  • Adding Dynamic Contents to Dashboard
    • VBA Macro INTRODUCTION TO VBA
  • What Is VBA? What Can You Do with VBA?
  • Recording a Macro Procedure and functions in
    • VBA VARIABLES IN VBA
  • What is Variables? Using Non-Declared Variables
  • Variable Data Types Using Const variables MESSAGEBOX AND INPUTBOX FUNCTIONS
  • Customizing Msgboxes and Inputbox Reading Cell Values into Messages
  • Various Button Groups in VBA IF AND SELECT STATEMENTS
  • Simple If Statements The Elseif Statements
  • Defining select case statements
    • LOOPING IN VBA
  • Introduction to Loops and its Types
  • The Basic Do and For Loop Exiting from a Loop
  • Advanced Loop Examples
    • MAIL FUNCTIONS –
  • VBA Using Outlook Namespace Send automated mail
  • Outlook Configurations
  • MAPI WORKSHEET / WORKBOOK OPERATIONS
  • Merge Worksheets using Macro
  • Merge multiple excel files into one sheet 6
  • Split worksheets using VBA filters
  • Worksheet copiers

VBA Macros Basic to Advance Training

INTRODUCTION TO VBA
1. What Is VBA?
2. What Can You Do with VBA?
3. VBA IDE
4. Recording the Macro

VISUAL BASIC EDITOR
1. The Project Explorer
2. The Properties Window
3. The VB editor windows
4. Creating and Removing Modules
5. Renaming Modules

VARIABLES IN VBA
1. What is Variables?
2. Using Non-Declared Variables
3. Variable Data Types

MESSAGEBOX AND INPUTBOX FUNCTIONS
1. Customising Msgboxes and Inputbox
2. Concatenating Boxes
3. Using Multiple Lines
4. Reading Cell Values into Messages
5. Asking Questions with Msgboxes / Inputbox

VBA WITH STATEMENTS
1. Writing a Simple With Statement
2. Creating More Complex References
3. Referencing other Objects

IF STATEMENTS
1. Simple If Statements
2. Single line Ifs
3. The Elseif Statement
4. Nesting Ifs
5. Combining Logical Tests

LOOPING IN VBA
1. Introduction to Loops and its Types
2. The Basic Do Loop Statement
3. Exiting from a Loop
4. Advanced Loop Examples

FOR NEXT LOOPS
1. Using Loop Counters
2. The Step Keyword
3. Exiting from a For Next Loop
4. Looping Through Items in a collection

FOR EACH LOOPS
1. The For Each Next Statement
2. Looping Over Cells
3. Nesting for Each Loops

CREATING FUNCTIONS
1. Writing a Simple Functions
2. Calling Functions
3. Creating Parameters
4. Optional Parameters
5. Rewriting Code to use Functions

ARRAYS IN VBA
1. Array Declaration
2. Assigning Values to an Array
3. Types of Array
4. One dimensional, Two dimensional and multi dimensional arrays

WORKING WITH SHEETS
1. Sheets and Types of Sheets
2. Manipulating Sheets:
3. Inserting and Deleting
4. Copying and Moving
5. Hiding and Unhiding

WORKING WITH WORKBOOKS
1. Referring to workbooks
2. Opening and Creating workbooks
3. Saving Workbooks

ERRORS HANDLING AND DEBUGGING
1. Types of Error:
2. Syntax Errors
3. Compile Errors
4. Runtime Errors

WORKING ON ACTIONS / EVENTS
1. Worksheet Level Events
2. Workbook Level Events
3. Application.OnTime
4. Working with Excel Range and Data
5. Worksheet and Workbook Operations,
6. Using Excel Functions in VBA
7. Volatile Functions

FILTER AND REPORTING
1. AutoFilter
2. Filter Object and Criteria(s)
3. Advanced Filters
4. Creating a PivotTable Report
5. PivotCaches and Naming
6. Adding PivotFields
7. Adding Fields for Calculation
8. Creating and Editing Charts

FORM CONTROLS
1. Understanding User forms and References
2. Combo box, Radio Button, Check box
3. Built in Dialog Boxes, Calendar
4. Working with Spin Buttons, Images
5. Reading data from Excel Sheets

6. Writing data to Excel Sheet

7. Project Study on GUI Pattern

MAIL FUNCTIONS – OUTLOOK
1. Using Outlook Namespace
2. Send automated mail
3. Outlook Configurations, MAPI
4. Filter Mails into Folders
5. Creating Contact
6. Create and Schedule Task
7. Export Mails Attachments in a specific location

WORKING WITH DATABASES
1. Introduction ADO
2. Various Connection Drivers
3. Database Connection and Recordset Object
4. Excel – Access Connectivity – Select, Retrieve, Insert Data Operations
5. Excel –SQL Connectivity – Select, Retrieve, Insert Data Operations

WORKING WITH WEB APIS
1. Form submissions, ROBOTs programming
2. Fill automated forms
3. Fill HTML Form using VBA
4. Robots programming

Interview Preparation

Training Certification

Course Duration

  • 2 Months ( WeekDays & WeekEnd Classes)
  • Class Duration : 2-3 Hrs
Scroll to Top