Excel, Advance Excel Macro Training

More than 14 Years of experience Trainer
Has conducted more than 80 Corporate training
Trained 10,000+ Students so far
Call us : +91-8802000175

70+ Hours Course Duration   Free Demo Class Available
100% Job Oriented Training   Small Batches- Max 10 Participants


Excel training in Gurgaon

Course Highlights:

Comprehensive Curriculum: Our course begins with a solid foundation in basic Excel concepts before delving into the intricacies of intermediate and advanced functionalities. Moreover, we'll guide you through the fascinating realm of VBA macros to automate and streamline your tasks.

Flexible Online | Offline Sessions: We understand the demands of modern life, which is why we offer online sessions to cater to your convenience. Now you can learn and grow without being tied to a physical location.

Small Batch Advantage: To ensure personalized attention and interactive learning, we maintain a limited batch size of 6 to 8 participants. This deliberate choice allows our experienced trainers to cater to individual expectations and requirements effectively.

Diverse Session Timings: Our weekday sessions are available both in the morning and evening, accommodating various schedules. Additionally, we provide weekend sessions for those seeking more flexibility.

Hands-on Assignments: Learning by doing is at the core of our approach. With assignments accompanying each topic, you'll have the chance to apply your newfound knowledge to real-world scenarios. Raw data and practice sheets will be your tools for honing your skills.

Personalized Support: Have questions? Need clarification? Our trainers are dedicated to addressing your queries and ensuring your learning journey is smooth. Your success is our priority.

Course Completion Certification: Once you've successfully navigated the course, you'll receive a certification recognizing your commitment and achievement. It's a testament to your acquired skills and knowledge.

Path to Placement: As a corporate training agency, we go beyond instruction. We offer placement assistance by sharing your CV with reputable companies. Your potential employers are just as eager to discover skilled professionals as you are to launch your career.

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