Hirdesh Bhardwaj,
a well-known author as well as Founder of Webs Jyoti Training and
development organization in Gurgaon, India. He conducts corporate and
classroom training on Excel in corporates covering basic to advanced MS
Excel VBA including pivot tables, lookup functions, INDEX, MATCH, pivot
charts, Goal seeking, logical functions, dashboard designing and various
features available in Excel. He has more than 13 years of
experience with the well-reputed MNCs where he spends more than 8 hours
in a day working with Excel sheet, VBA editor and always tries to
explore new challenges in excel to make it absolute for corporate use.
As an Author, He wrote books on VBA for Excel, Web Designing & PHP MYSQL.
His Books are referenced & taught in NIELIT -Govt. of India, Jiwaji
University Gwalior, MMYVV University, Mangalore University, J.C Bose
University & Directorate of School Education Kashmir.
Hirdesh started his career in 2007 with NIIT as an IT Trainer Where He
taught various technologies over 5000+ Students. Besides books, Hirdesh
has to published about 250 scripts and tutorials in upcoming years. In
November 2009, He founded Webs Jyoti, - Development and Training
Organization.
Corporate Training in Advance Excel, VBA Macros Conducted By Hirdesh Bhardwaj:
Olam Agro, Cars 24, Nestle, Beumer Group, PGS Partner, GET Global,
Process 9, Hema Engineering, National Security Guard- NSG HQ, Realatic
Realtors, India Shelter Finance Corporation, Jastech Systems, National
Skills Foundation of India, MB Informatics, SAP Lab India, MB
Informatics, Alpine India, FIA Global, Pace Academy, Dudhi Industries
etc..
Home
Excel Advance Excel Training
More than 13 Years of experience Trainer
Has conducted more than 50 Corporate training
Trained 10000+ Students so far
Call us :
+91-8802000175
70+ Hours Course Duration
Free Demo Class Available
100% Job Oriented Training
Small Batches- Max 6 Participants
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
EXCEL INTRODUCTION
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 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
