Excel, VBA | Macro Training

Excel Macro Training Content

An overview of the screen, navigation and basic spreadsheet concepts
Various 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 Training Content

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

  • 50 Hours ( Weekdays & Weekend Classes)
  • Class Duration : 2-3 Hrs
  • Call us : 0124-4360863, 8802000175

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
YouTube86
YouTube