VBA in Advance Excel Course

This module will introduce the candidate to the world of excel, its effective working and functions.

•   Advantages of using MS Excel & Various purpose to use Excel

•   Understanding of Workbook, Worksheet, columns, rows & Cells

•   Ribbon & Tabs

•   Quick access bar, Formula bar, Name bar, Status bar

•   Keyboard Shortcuts & Navigation while using large data

•   Different Use of Mouse

•   Insert Comment

In this module the candidate will learn about different tables and their working.

•   Advantage of Converting data into tables

•   Structured formula

•   Use of Slicers

•   Dynamic Range

In this module the candidate will learn about different references of cells.

•   Absolute Reference

•   Relative Reference

•   Mixed Reference

•   3D Reference

In this module the candidate will learn the basic functions of Excel.

•   Text to Column

•   Flash Fill

•   Auto Fill

In this module learn different ways of sorting data effectively. 

•   In Ascending & Descending Order

•   Sort with Text, Numbers & Date

•   Sort with formatting (Cell, Color, Font Color)

•   In Multiple Order

•   In Custom Order

In this module learn how to filter data by using advanced filters to texts, numbers etc. 

•   Applying Filters by text, numbers, or date on the Data

•   Advanced Filter: Setting up Criteria to Filter the data

•   Applying Complex Criteria

•   Clearing the existing criteria

•   Use Wildcard characters in Filter Criteria

In this module the candidate will learn about basic formulas, functions for easy calculations.

•   Form multiple Sheets

•   Form multiple Files

•   Understanding Syntax, Intellisense, Suggestion box & optional argument

•   Basic Calculations & BODMAS Rule

•   Calculate Square, cube & Raise Number to Power

In this module the candidate will learn about the ‘If’ function.

•   Simple If

•   Combination of If with OR, AND & NOT

•   Nested If Function

•   LEN, TRIM, LEFT, RIGHT, MID, FIND, SEARCH, CONCATENATE, UPPER, LOWER, PROPER

•   REPLACE, SUBSTITUTE, SEARCH, FIND

In this module the candidate will learn how to work with COUNTIF and SUMIF, and understand the date and time concept of Excel.

•   Date & Time Formula

•   COUNTIF, COUNTIFS, SUMIF, SUMIFS

In this module the candidate will learn about Lookup and information functions, its advantages and disadvantages.

•   Lookup Functions - VLOOKUP. HLOOKUP. LOOKUP

•   Disadvantage / Limitations of Vlookup

•   Reference Functions - MATCH INDEX

•   Simple XLookup

•   Exact Match

•   Approx Match

•   Nested XLookup

•   Case Sensitive Xlookup

•   Multiple Answer column Xlookup

•   Lookup Repeating Value (Using Filter)

In this module the candidate will learn the effective usage of Named Ranges.

•   Defining and Using Range Names

•   Using Named Ranges in Functions

In this module the candidate will learn about dependent Data Validation.

•   By Numbers, Text Length, Date, List (dropdown)

•   Input Message and Error Message

•   Dependent Data Validation

•   Dynamic Dropdown range with Table

In this module the candidate will learn about Array Formulas such as Sort, Unique and more. 

•   Spilled Ranges & use of #

•   Sort

•   Sort by

•   Unique

•   Filter

•   Sequence

•   Ifs

In this module the candidate will learn about formatting.

•   Applying Conditional Formatting

•   Applying Multiple Rule Sets

•   Creating Custom Rule Sets

•   Viewing and Managing Rules

•   Clearing Custom Rules

In this module the candidate will learn about Macros. 

•   Displaying the Developer Tab

•   Review and Purpose of Macros

•   Where to Save Macros

•   Absolute and relative record

•   Running macros: Assigning to Quick Access Toolbar, shapes

•   Pictures and keyboard shortcuts

•   Macro recording with charts & sorting Data

•   Alter the recorded code

•   User-Defined Function (UDF)

•   Use pre built VBA code

In this module learn about Pivot tables and their uses.

•   Creating Pivot Tables

•   Rearranging Pivot Table Data

•   Modifying Pivot Table calculations

•   Drill-Down

•   Filtering the Pivot data

•   Summarizing Values by Sum. Count. Average. Max. and Product

•   Show Values As % of Grand Total, % of Column Total, % of Row Total

•   Grouping Fields

•   Creating Calculated Column(s) in Pivot

•   Creating Calculated Item(s) in Pivot

•   Showing and Hiding the Grand Totals

•   Refreshing data in pivot table

•   Changing the scope of data source

In this module the candidate will learn slicers. 

•   Inserting and Using a Slicer

•   Renaming the Slicer

•   Changing Slicer Settings

•   Formatting a Slicer

•   Clearing the Slicer

In this module the candidate will learn about Power Query. 

•   Enable PowerPivot

•   Understand Import Data, Transform data, Data Model to create a report

•   Use Load, Load to & Transform Data

•   Use of Power query to record steps for all files from a folder

•   Add column, Update Column with Text, Numbers, & date

In this module the candidate will learn about Charts, its types, uses and working.

•   Selecting data

•   Formatting your Chart

•   Understanding Different Types of Charts

•   Combo Chart

•   Creating Custom chart types

•   Use of Secondary Axis

•   Using Trend Line in Charts

In this module the candidate will learn about topics such as how to transport data, format painter, printing etc.

•   Format Painter

•   Freeze Pans

•   Removing Duplicates

•   Transposing Data

•   Paste Special

•   Print Settings

•   Custom Formatting

In this module the candidate will learn about the Excel Dashboard.

•   Objective to Create Dashboard

•   Create Pivot Table & Charts

•   Design Dashboard

•   Use Macro to Automatically Refresh all when row data is changed or new data is added

This module will help the candidate to create a strong security for their data. 

•   Password Protect workbook (file) for open & modify

•   Password Protect Sheet

•   Password protect workbook structure (Review => protect Sheet)

•   Allow Edit Range with password

In this module the candidate will Introduce the concept of Visual Basic for Application to the candidate. 

• What is VBA?
• What is a Macro? 
• What can Macros do? 
• To use or not to use Macros? 
• Macro – The 5 Tenets 
• What is the Excel Object Model? 
• Your Personal.XLSB file 
• Need to check your security options 
• How do I access VBA? 
• Macro Security Settings 
• Displaying and reviewing the Developer Tab in the Ribbon

In this module the candidate will learn about the various Modules and Procedures of VBA. 

• Modules and Procedures 
• Program design and concepts
• A Good Spreadsheet Application
• Code Format / Layout
• To Dim or Not to Dim? In other words Why Dim?
• How to Declare a Variable / Dim / Private /Public
• Understanding Constants and how to Declare them
• Run Timing Test Macro
• Modules – Understanding how to Create,
• Name, Edit, Copy and Remove
• Practical on Modules

In this module the candidate will learn abouT Loops and Flows. 

• How to work with Loops & Conditional Statements

In this module the candidate will learn about creating and using messages. 

• Creating and Using Message Boxes 
• Types Of Message Box 
• Using Input Box 
• Running a Macro from within Excel 
• Assigning a Keyboard Shortcut to a Macro 
• Assigning and launching a Macro from a shapes

In this module the candidate will learn about cells in Excel and its functions. 

• Different Functions to access cells. 
• Cell Range 
• Cell Color 
• Cell Back Color 
• Cell Alignment 
• Cell Wrap & Merge 
• Cell Orientation

In this module the candidate will learn how to work with the rows and the columns for effective representation of data.

• Inserting Rows & Columns 
• Hiding Rows & Columns 
• Deleting Rows & Columns 
• Height & Width, Autofit 
• Formatting

In this module the candidate will learn about sheets and its functions. 

• Adding Sheets 
• Deleting Sheets 
• Hiding & Unhiding Sheets 
• Activating Sheets 
• Protecting Sheet

• Business communication
• Preparation for the Interview 
• Presentation Skills

Resume Writing