Best Advanced Excel Training in Bangalore

COURSE CONTENT/SYLLABUS FOR ADVANCED EXCEL COURSE : Duration- 30 Hours

1 Pivots and Pivot Graphs:

• Creating of Pivot Table
• Using Summary function in Pivot Table
• Pivot Table Calculations
• Creating own formulas in Pivots
• Grouping and Ungrouping in Pivot Tables
• Creating Pivot Graph
• Custom Filter on Pivot Table
• Formatting Pivot table (Removing old data, grand total, subtotal …etc.)

2 Working on Objects:

• Inserting Objects
• Inserting different files into Excel (like .doc, .pdf, .txt files)
• Hyperlinking to different sheets
• Providing screen tips in hyperlink
• Hyperlink to send emails
• Inserting symbols
• Sending working file to an email

3 Data Management in Excel - Sorting:

• Rearranging of Data
• Sorting by alphabets, numbers and time

Filtering Data:

• Using Auto filter option
• Custom Filtering with different options
• Advanced Filtering
• Find Unique records using advanced filtering
• Filter using operators AND/OR
• Filter by color

Grouping and Outlining Data

• Group and ungroup rows and columns
• Auto outlining the data
• Freezing and Unfreezing

Data Validation:

• Use Data Validation in Excel
• Using Input Message in Data validation
• Using error message in Data Validation

Removing duplicate records:

• Remove duplicate records in a spread sheet
• Customize to remove the duplicate values

Split Texts:

• Split texts to different columns
• Remove special character from a string and split the string

Data Consolidation:

• Combining different spreadsheet using data consolidation
• Using summary function in data consolidation

Conditional Formatting:

• Use of conditional formatting
• Custom formatting

Copy Data:

• Use of format painter
• Use of paste special
• Strike through a value
• Find and Replace

View Multiple Files:

• Arrange multiple windows to view in one time
• Split big sheet into sub sheets for better analysis

Working on Tab:

• Hide/ Unhide a tab
• Color a tab
• Move /Copy the tab to another sheet/workbook

4 Advanced Functions:

• V-Lookup
• H-Lookup
• IF
• ISERROR
• Index Match
• Rows
• Columns
• Match
• Offset
• Get Pivot Data
• Date and Time Functions
• Text Functions
• Operators
• Mathematical functions
• Calculation options

Name Manager:

• Define a name to a range
• Use of name manager
• Using formulas in name manager
• Editing name range

5 Data Connection from External Data:

• From access
• From SQL
• From Web
• From ODBC

6 Customize your Excel view:

• Customize Excel view
• Developer Option
• Trust Centre
• Add-Ins
• Save directory options
• Language Setting
• Quick access toolbar

7 Security options in excel:

• Protecting the worksheet
• Protecting the workbooks
• Sharing the workbook
• Allow users to edit ranges

COURSE CONTENT/SYLLABUS FOR VBA COURSE : Duration- 20 Hours

1 Introduction to VBA

• WHAT IS VBA?
• INTRODUCTION TO ANOTHER LANGUAGES
• VBA: AN EVENT DRIVEN PROGRAMMING
• VBA: AN OBJECT BASED PROGRAMMING
• ADVANTAGES AND DISADVANTAGES OF VBA
• OBJECTS, PROCEDURES AND PROPERTIES

2 VBA IDE

• OPENING THE EXCEL VBA IDE
• MENU BAR
• TOOLBAR
• PROJECT WINDOW
• CODE WINDOW
• IMMEDIATE WINDOW
• CUSTOMIZING THE VBE

3Getting Started With Macro

• RECORDING THE MACRO
• EXAMINING THE MACRO
• SAVING WORKBOOKS THAT CONTAIN MACROS

4 Fundamentals of VBA Language

• VARIABLE & CONSTANTS
• KEYWORDS
• DATA TYPES
• PROCEDURES: SUB AND FUNCTION
• ARGUMENTS
• LOCAL VS GLOBAL VARIABLE DECLARATION
• PROCEDURES: PUBLIC OR PRIVATE
• COMMENTS

5 Control Flow & Loops Statements

• RELATIONAL AND LOGICAL OPERATORS
• IF....THEN
• IF....THEN....ELSE
• IF....THEN....ELSEIF....ELSE
• SELECT CASE ( WITH TO AND WITH IS)
• DO WHILE....LOOP
• DOLOOP....WHILE
• DO UNTIL....LOOP
• DOLOOP....UNTIL
• FOR....NEXT
• FOR EACH....NEXT
• WHILE....WEND
• THE EXIT STATEMENT

6 Using VBA and Worksheet Functions

• VB FUNCTIONS
• EXCEL FUNCTIONS
• THE WORKBOOKS COLLECTION
• THE SHEETS COLLECTION
• ACTIVATE AND SELECT
• RANGE PROPERTY
• CELLS PROPERTY
• OffSET PROPERTY
• RESIZE PROPERTY
• CURRENTREGION PROPERTY
• COLUMNS AND ROWS PROPERTIES
• REFERENCING RANGES IN OTHER SHEETS
• USING THE UNION AND INTERSECT METHODS
• USING THE ISEMPTY FUNCTION
• USING THE AREAS COLLECTION
• TRANSFERRING VALUES BETWEEN ARRAYS AND RANGES

7 Working with Dialog Boxes and Form Objects

• USING LABELS
• USING TEXT BOXES
• USING THE COMMAND BUTTON CONTROL
• USING LIST BOXES
• USING COMBO BOXES
• USING CHECK BOXES
• USING RADIO BUTTONS
• USING TOGGLE BUTTONS
• USING FRAMES
• USING MESSAGE BOXES
• TAKING INPUT USING INPUT BOXES
• TABSTRIP
• USING A SCROLLBAR AS A SLIDER TO SELECT VALUES
• ADDING HELP TIPS TO CONTROLS
• TAB ORDER
• COLOURING THE ACTIVE CONTROL
• TRANSPARENT FORMS
• WORKING WITH MENUS AND TOOLBARS
• CREATING MENUS AND TOOLBARS

8 Handling Errors

• TYPES OF ERRORS IN VBA
• USING THE ON ERROR GOTO STATEMENT
• USING THE RESUME STATEMENT
• WORKING WITH THE ERR OBJECT

9 The Excel Object Model

• INTRODUCING CLASSES AND OBJECTS
• THE OBJECT HIERARCHY
• COLLECTIONS
• REFERRING TO OBJECTS
• NAVIGATING THROUGH THE HIERARCHY
• OBJECT PROPERTIES AND METHODS
• EVENTS
• USING THE WITH…END WITH STATEMENT

10 Arrays

• DECLARING ARRAYS
• SPECIFYING LIMITS
• ARRAY FUNCTION
• TWO DIMENSIONAL ARRAYS
• MULTI DIMENSIONAL ARRAYS
• DYNAMIC ARRAYS

11 The Application Object

• GLOBALS
• THE ACTIVE PROPERTIES
• DISPLAY ALERTS
• SCREEN UPDATING
• EVALUATE
• STATUSBAR
• SEND KEYS
• ONTIME & ON KEY

12 Data Lists

• SORTING A RANGE
• CREATING A TABLE
• SORTING A TABLE
• AUTOFILTER