Learn Advanced Excel & VBA with Award Winning Trainer

What is Microsoft Excel and why it is important to have good skills in Microsoft Excel?

Microsoft Excel is a spreadsheet application distributed by Microsoft and the basis of all data entry jobs. It allows the customer to have the ability to use simple spreadsheet capacities like practical tools, equations, diagrams, and other critical tools. Every one of us knows all the essential options of Microsoft Excel, but there are many advanced excel functions which very few know. It is one of the major benefits of getting advanced excel training.

5 reasons to join us!
What you wil learn?
Who should join?
  • Step-by-step instruction
  • 100 Lessons of Ms-Excel & VBA
  • 32 hours training + 60 hours assignment
  • 6 months exclusive online & offline Support
  • Excel tips & videos update
  • Business Forecast
  • Automatic reports
  • Streamline Operation
  • Decision making reports
  • Better business planning
  • Business Owners & CXOs
  • Sales Professionals
  • Finance Professionals
  • HR Professionals
  • Marketing Professionals
  • Everyone who deals with the data

Career Scope of Microsoft Excel & VBA – MS Excel & VBA has many advantages to those who are working with multinational companies utilizing different advanced excel formulas and functions while preparing ready MIS reports for their organizations. Excel & VBA is widely used to make financial statements, and other types of financial reports, for example, budget, forecast, ratio analysis, financial modeling, business scenario, and various other reports.

MS Excel users around the globe utilize this program effectively by creating many workbooks, preparing complicated reports that contain various links between workbooks briefly using formulas, MIS reports like balance sheets, profit and loss account & trial balance.

The Course Curriculum -

Designed for Modern Excel:-This advanced excel course is designed in Excel 2019, Office 365. You can practice the concepts in older versions of to get the most out of it, use the latest version of Excel or Office 365.

Microsoft Excel Course Content

✓ Introduction to Excel 2013/2016/2019/Office 365
✓ Application Interface and Key Components of Excel
❑ Ribbon Bar, Quick Excel Toolbar, Formulas Bar, Name Box, Rows, Columns, Status Bar etc.
✓ Navigating Through Excel Ribbon Tabs
❑ Exploring Each Ribbon Tab i.e. Home, Insert, Page Layout, View etc.*
✓ Exploring Important Excel Options*
❑ Changing Cursor Direction
❑ Enable/Disable Fill Handler and Live Preview
❑ Hiding Horizontal/Vertical scroll bars
❑ Showing gridlines, and Changing Calculation mode etc.
✓ Live Session Exercise
❑ Working with Cells/Ranges
❑ Difference between Keyboard Shortcuts and Hot Keys
❑ Use of Short/Hotkeys and Mouse
❑ Auto-Fit Content to Column Width / Row Height
❑ Adding/Deleting/Moving/Copy cells/ranges
✓ Splitting data of Single Column into multiple
❑ Import data from text file into Excel
✓ 10 Examples to use Auto-fill and Flash Fill
✓ Magics of Go-To Special
✓ Merge/Unmerge Cells & Wrap Text
✓ Extracting Unique Values & Important Ribbon, General and Data Entry Keyboard Shortcuts

✓ Formatting Essentials
❑ First thing First – Exploring Universal Formatting Rules
❑ Recognizing Text & Number entries.
❑ How Excel stores Date & Time Stamps etc.
❑ Recognizing Text Representation of Numbers
✓ Custom Cell Number Formats
❑ Converts a number into Currency format, Negative format, Percentile format, Phone Number format
❑ Displays 0 at the start of a number without converting in text format
❑ Shows Text with Number without converting in text
✓ Custom Date/Time Formats
❑ How to custom Date / Time in a different format as per need
✓ Working with Comments / Notes
❑ Introducing New Type of Comments (For 365 Users* only)
❑ Inserting, Modifying & Deleting Comments with different methods
✓ Format Painter – A Quick way to copy ‘Formatting Attribute’
✓ Paste Special
❑ Pasting specific attributes like Value, Formulas, Comments, Column Width, Basic Mathematical
Operations etc.
✓ Table, Table Styles & Formatting
❑ Introducing Excel Table Feature (a way to quickly convert your data-set into the dynamic table)
❑ Live example
✓ Freeze Panes
❑ Freeze Rows, Columns etc.
✓ Page Setup & Print Formatting

✓ Introduction to Excel Functions and Formulas
❑ Difference between Functions & Formulas
✓ Basics of Functions & Formulas
✓ Working with Cell References Types
❑ Relative Cell References
❑ Absolute Cell References
❑ Row Relative & Column Absolute Cell References and vice-versa
✓ Most Used Basics & Advanced Functions & Formulas
❑ Text Functions
❑ Date & Time Functions
❑ Mathematical
❑ Statistical Functions
❑ Logical Functions
❑ Lookup & Reference Functions
❑ Newly Introduced Excel Functions
❑ Customized Formulas Tricks
✓ Working with Array Formulas
✓ Creating Customize Formulas Step-by-Step with Live examples
✓ Creating and Working with Dynamic Ranges using Function and Excel Table features
✓ Formulas Debugging / Formulas Auditing
✓ Types of Formula Errors / Error Handling Tricks

Functions & Formulas
✓ Text Functions
❑ CLEAN, CONCATENATE, LEFT, RIGHT, MID, LEN,
LOWER, UPPER, PROPER, REPT, TRIM, VALUE, FIND,
SEARCH, SUBSTITUTE, and TEXT etc.
✓ Date & Time Functions
❑ DATE, DAYS, TIME, NOW, TODAY, EDATE,
EOMONTH, NETWORKDAYS, NETWORKDAYS.INTL,
WEEKDAY, WEEKNUM, WORKDAY, and
WORKDAY.INTL etc.
✓ Math & Trig Functions
❑ INT, MOD, ROUND, ROUNDDOWN, ROUNDUP,
SUM, SUMIF, SUMIFS, SUMPRODUCT etc.
✓ Statistical Functions
❑ AVERAGE, COUNT, COUNTA, COUNTBLANK, MAX,
MIN, COUNTIF, COUNTIFS, SMALL, and LARGE etc.
✓ Logical Functions
❑ IF, IFS, AND, OR, and IFERROR.
✓ Lookup & Reference Functions
❑ FORMULATEXT, VLOOKUP, HLOOKUP, INDEX,
MATCH, INDIRECT, and OFFSET
✓ Newly Introduced Functions in Recent
Version of Excel*
❑ CONCAT, TEXTJOIN, IFS, SWITCH
✓ Nested Conditions/Customize Formulas*
❑ One dimensional dynamic lookup
❑ Two dimensional dynamic lookup
❑ Formulas for calculating Aging
❑ Calculate remaining/pending days and weeks in a
year
❑ Finding out Weekend/Weekday dates
❑ Nested IF condition (using multiple criteria)
❑ Alternatives of Nested IF Condition
❑ Extracting First, Middle & Last Name dynamically
❑ Formulas to get Sum/Average of Nth Top/Bottom
values
❑ Merge Numbers & Text by keeping Number
formatting alive
❑ Extract Date or Time from Date-Time stamp
❑ Extracting Unique List of Items
❑ Reverse lookup
❑ Many more….

✓ Data Sorting
❑ Introduction to Data Sorting
❑ Simple Sorting basis on cell value
❑ Advanced / Multilevel Sorting
❑ Sorting based on Cell Colors, Font Color etc.
✓ Data Filtering
❑ What is Filter and How to apply?
❑ Sorting with Filter
❑ Advanced Filter
❑ Conditional Filtering
✓ Named Ranges
❑ Introduction to Defined Names
❑ Assigning Name to Cells and Ranges
❑ Use of Named Ranges in Functions & Formulas
❑ Working with Dynamic Ranges
✓ Conditional Formatting
❑ Introduction to Conditional Formatting
❑ Exploring all CF Rules in Detail
❑ Formatting Cells based on values using different Styles
❑ Formatting Cell Values using Traffic
Indicators/Icons
❑ Highlighting Cells Containing Specific Text
❑ Highlighting Top/Bottom Values (Simple &
Dynamic Examples)
❑ Highlighting Unique/Duplicate Values
❑ Highlighting Rows/Columns with Empty Cells
❑ Highlighting using Functions/Formulas
❑ Highlighting Weekday/Weekend Dates using CF
✓ Data Validation
❑ What is Data Validation in Excel?
❑ Exploring all Data Validation Rules in detail
❑ Setting up Input and Error Messages
❑ Validating Numbers, Decimals, Date, Text
Length etc.
❑ Data Validation using Functions & Formulas
❑ Validating Data using Named Range
❑ Dynamic Drop Down List
❑ Dynamic Dependent Drop Down List
✓ What-If Analysis
❑ Introduction and working with Scenario
Manager
❑ Use of Goal Seek with Examples

✓ Introduction to Excel Charts
✓ Exploring most commonly used Charts and
Templates
✓ Basics of Charts
✓ Selecting Requirement based Charts
✓ Working with Basic Charts:
❑ Column / Clustered / Stacked Column
❑ Line / Line with Markers
❑ Bar
❑ Pie / Pie of Pie
❑ Map
❑ Waterfall
❑ Gantt Chart | Pareto Chart
❑ Clustered Column with Line etc.
✓ Creating Customized / Advanced Charts
✓ Creating a Dynamic Chart
✓ Working with Dynamic Interactive Charts in Excel
using Drop Down
✓ Working with Chart Elements, Formatting, Chart
Styles, Properties etc.

✓ Introduction to Pivot Table
✓ Creating a Pivot Table
❑ Basics of Pivot Table Creation (Planning and Studying the Data)
❑ Two Dimensional Pivot Table Summary
✓ Use of Calculated Fields/Items
✓ Pivot Table Formatting
✓ Grouping Items & Summarizing data in Pivot Tables
✓ Grouping and Bucketing data in Pivot Table
✓ Changing/Modifying Data Sources
✓ Working with Pivot Table Designs & Layouts
✓ Exploring Important Pivot Table Options & Field Settings
❑ Formatting Empty Cells
✓ Pivot Table Filters
✓ Changing Pivot Table Summary Calculation
✓ Use of Slicers in Pivot Table
✓ Using Source Data to Convert into Infographic Summary
✓ Introduction to Pivot Charts

✓ Protecting Workbook/Worksheets/Ranges
✓ ‘Go To Special’ Feature and Its’ Uses
✓ 350+ Window Keyboard Shortcuts Guide (Cheat-Sheet)
❑ 30 Quick Time-Saving Excel Keyboard Shortcuts to increase
productivity
❑ Learn with Live Examples & Easy way to Best Practice
✓ 150+ Mac Excel Keyboard Shortcuts Guide
✓ Exploring Some Ready to Use Templates and Industry
Dashboards
✓ Session Study Material
❑ Session Study Material with solved examples after every session
❑ Access to some Excel Videos*
❑ Excel Formula Tips
✓ Real Life Case Studies and Situations for Best Practice and
Get Ready for Corporate World
✓ Bi-Monthly Quiz
✓ Situation-based Questions for Practice
✓ 6 Months Live Support via Phone/Email/Messages

VBA (Macros) Course Content

✓ Quick Recap of Microsoft Excel
✓ Introduction to Developer Ribbon Tab
✓ Introduction to VBA
✓ What is VBA / Macro in Excel?
❑ Difference between VBA and Macros
✓ Introduction to Visual Basic Editor Window
❑ Open Visual Basic Editor (VBE)
✓ Getting Familiar with Visual Basic Editor (VBE)
❑ Project Explorer
❑ Immediate Window
❑ Run Button
❑ Reset Button
✓ Customizing the VBE Environment
✓ The Macro Recorder
❑ Recording Your First Macros in Excel
❑ Steps to record a Macro
❑ Relative References vs Absolute Reference in Macro Recording
❑ Ways to Run Macros
❑ Live Project: Dynamic Cell Selection (Using Excel Go to Feature)
✓ Executing / Modifying Recorded Macros
✓ Saving Macro Workbook

✓ Introduction to Sub and Function Procedures
❑ Declaring Procedures
❑ Procedures’ Scope (Public/Private Procedures)
✓ Introducing the Excel VBA Object Model (A must know thing to become Pro in VBA)
❑ Object Hierarchy (Objects and Collections)
✓ Properties and Methods
❑ Working with Object Properties
❑ Working with Object Methods
❑ Live Case Study – Working with Comment Object
✓ Important and Useful Properties of Application Object
✓ Working with Ranges Objects
❑ Selecting, Copying, Pasting ranges
✓ Working with Comments
❑ Tips to make a best use of comments
✓ Variable, Data Types and Constant
❑ Rules for Naming Variable
❑ Declaring & Initializing Variables
❑ VBA built-in data types
❑ Variable Scope and Working with Constant
✓ Finding Last or Next available Row/Column
✓ Selecting Rows/Columns/Table (Normally & Dynamically)

✓ Introduction to VBA Loops & Conditional Statements
✓ Working with Conditional Statements
❑ Types of Conditional Statements
❑ Use of Simple and Nested IF conditions
❑ IF with AND, OR & NOT
❑ Alternative of IF, a Select CASE statement
✓ Working with Loops
❑ For Next Loop (aka Counter Loop)
❑ For Each Loop
❑ Normal Do-Loop
❑ Do Until
❑ Do While Loop
❑ While Wend Loop
✓ Working with Message & Inbox Box
✓ With-End With Constructs
✓ Controlling Program Flow
❑ Use of GoTo statements
✓ Introduction to Arrays
❑ Declaring arrays
❑ Declaring Multidimensional arrays
❑ Static Arrays vs Dynamic Arrays
❑ Working with Dynamic Arrays

✓ Introduction to Excel VBA Functions and Worksheet Functions
✓ Difference between R1C1 and A1 style referencing
✓ Difference between Worksheet Functions Vs Active.cell Formula
✓ Getting started with VBA Excel Functions
❑ IIf, UCase, LCase, Date, StrReverse, Split, RemoveVowels, InStr, Date, DateSerial,
Format, MonthName etc.
✓ Getting started with WorksheetFunction
✓ Working with Function Procedures
✓ Getting started with User Defined Functions (UDF)
❑ UDF with No, One, & Multiple arguments
❑ UDF to Sum Each Digit in a Cell
❑ UDF to extract only numbers from alphanumeric cell
❑ Function with an Array argument
✓ Fixing arguments of Formulas in VBA
✓ Working and Creating Customize Functions
✓ Error Handling & Debugging Techniques
❑ Trapping Errors (Ingoing Error or Jumping to specific section)

✓ Introduction to Excel VBA Functions and Worksheet Functions
✓ Difference between R1C1 and A1 style referencing
✓ Difference between Worksheet Functions Vs Active.cell Formula
✓ Getting started with VBA Excel Functions
❑ IIf, UCase, LCase, Date, StrReverse, Split, RemoveVowels, InStr, Date, DateSerial,
Format, MonthName etc.
✓ Getting started with WorksheetFunction
✓ Working with Function Procedures
✓ Getting started with User Defined Functions (UDF)
❑ UDF with No, One, & Multiple arguments
❑ UDF to Sum Each Digit in a Cell
❑ UDF to extract only numbers from alphanumeric cell
❑ Function with an Array argument
✓ Fixing arguments of Formulas in VBA
✓ Working and Creating Customize Functions
✓ Error Handling & Debugging Techniques
❑ Trapping Errors (Ingoing Error or Jumping to specific section)

✓ Working with Pivot Tables
❑ Recording & Modifying macro to create Pivot Table
❑ Writing codes to create Pivot Table Report
❑ Working with Pivot Table Fields and Items
❑ Updating Pivot Tables
❑ Generating multiple Pivot Table reports using VBA
✓ Charts
❑ Difference between embedded chart and chart sheets
❑ Understanding the Chart object model
❑ Creating an Excel Chart using VBA
❑ Changing Chart Type and Series Type
❑ Modifying Charts
❑ Creating Spark-line Chart in Excel using VBA
❑ Looping through all charts available in worksheet
❑ Adjusting Size and Aligning ChartObjects
✓ Sorting & Filtering
❑ Recording a macro to sort the table
❑ Understanding and modifying the recorded macro
❑ Performing Simple sorting
❑ Dynamic and multilevel sorting
❑ Filter and display relevant information

✓ Add-Inns
❑ Introduction to Add-Ins
❑ Add-Inn vs COM Add-Ins
❑ How to Create Add-Inn
❑ Installing an Add-Ins
✓ Type of different Excel events
❑ Workbook Events (Open, Activate, SheetActivate, BeforeSave, NewSheet, Deactivate etc.)
❑ Worksheet Events (Activate, BeforeRightClick, BeforeDoubleClick, Calculate, PivotTableUpdate
etc.)
✓ Starting an application from Excel
❑ VBA Shell function to launch other programs
❑ Creating a new Word Document
✓ Interacting with Microsoft Office Apps
❑ Opening a Word Document
❑ Opening Outlook
❑ Launching PowerPoint
✓ Controlling Excel from another application
✓ Sending Personalized emails via Outlook
✓ Sending e-mail attachments from Excel
✓ Working with Files & Folders
✓ Converting / Saving Excel File into PDF

Testimonials

I took online excel classes with Anil and it was a good experience overall. Anil was extremely knowledgeable, patient and flexible with timings. He did not rush through the classes and was equally willing to devote extra time as and when needed. I would definitely recommend Anil to anyone looking to ...Read more
We had invited Analytics Training Hub to office for two days Excel training workshop and I feel so good we did that. The workshop was well planned and delivered smoothly. Anil knows his job perfectly and excels in delivering his knowledge in easy to understand manner. Course material ...Read more
I had a great learning experience with Analytics Training Hub, Instructor Anil is very knowledgeable and he is a Subject matter expert in the field of Analytics.All the training sessions would be one-one, which i really liked it. During the training Anil always engage students and ensures that students are.. Read more

Are you ready to invest on your skills!

Request for Callback





Frequently Asked Questions

You can attend the same session with another batch

Yes, ofcourse. If you join our ongoing batch you will be applicableto get the discount of 5%

Yes, this is one time payment

Yes, you need to pay full amount of course fees before the first session

Yes, ofcourse. We assign a practice trainer to everyone.

-Yes, you will learn automation of reports during the course period.

-You will see Excel in a new limelight and will learn the Superpowers of this amazing tool to propel your productivity

Gallery

  • 14/3, Ideashacks Corworking Sector 31, Faridabad
  • 9990748956
  • info@analyticstraininghub.com