Best MIS 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

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

SQL – Part 1

1. Users and Schemas

   1. Introduction to Users and Schemas
      1. Definitions and Client Tools
      2. SQL Developer Login
   2. Creating An Application Schema

2. Pseudo Columns & Functions

   1. Using The Dual Table
   2. Functions
   3. Pseudo-Columns
   4. Using Rowid
   5. Using Rownum

3. Using Subqueries

   1. Simple Subqueries
   2. Inline Views
   3. Correlated Subqueries
   4. Scalar Subquery

4. Joining Tables

   1. Review of Joins
   2. Equijoins
      1. Inner Joins
      2. Outer Joins
   3. Cross Joins
   4. Natural Joins
   5. Using Named Subqueries

5. Group Operations

   1. About Group Processing
   2. Simple GROUP BY
   3. ROLLUP Function

6. Using Set Operators

   1. Set Operators Defined
   2. Relationship to Mathematical Set Theory
   3. Restrictions on Set Operators

7. Conditional Processing

   1. The DECODE Function
   2. The Case Expression
   3. Exercise: Conditional Processing

8. SQL Functions (Character)

   1. What Are SQL Functions?
   2. Character Functions
      1. CONCAT
      2. LENGTH
      3. INSTR
      4. REPLACE
      5. UPPER
      6. LOWER
      7. INITICAP
      8. LPAD
      9. RPAD
      10. TRIM
      11. TO_CHAR
      12. SOUNDEX
   3. SQL Functions (Non-Character)
      1. Numeric Functions
         1. The TO_CHAR Function with numbers
      2. Date/Time Functions
      3. Date Format Functions
      4. Date Arithmetic Functions
      5. Null Value Functions
   4. SQL Data Manipulation Language
      1. The INSERT Statement
         1. INSERT Statement Categories
      2. The UPDATE Statement
      3. The DELETE Statement
      4. Transactions
      5. Complex Table References
      6. The MERGE statement

SQL – Part 2 – Advanced:

11. Analytical Functions
12. Queries for Queries
13. Techniques to compare data.
14. Efficiently writing SQL queries.