MIS Practical Training in Delhi

Module 1 Basic & Advanced Excel Function

Worksheet Operations, Cell Operation, Format Cells, Freeze Panes, Techniques of Paste Special, Protect

Worksheets & Workbooks , Format Painter, Data Sorting using custom list, Summarizing data using Auto

Outline, Concept of Cell Referencing, Relative Referencing, Absolute Referencing, Mixed Referencing,

Important Functions in Excel:

AND,OR,NOT,COUNT,COUNTA,COUNTBLANK,COUNTIF,MAX,MIN,UPPER,LOWER,PROPER,RIGHT,LEFT

MID,POWER,SUM,SUMIF,AVERAGE ,EXACT,CONCATENATE,LEN,NETWORKDAYS,MATCH,IF,VLOOKUP,HL

OOKUP,SUBTOTAL

Name Manager, Conditional Formatting, Highlighting Duplicates

Data Validation :--Different Data validation in Excel ,Using list in validation

Charts in Excel :--Elements of Charts ,Major charts used in Excel

Pivot Table :--Pivot Table creating Methods ,Rearranging a Pivot Table ,Filtering Pivot Table

Data ,Performing Custom Calculation ,Creating Dynamic Dashboards using pivot charts and

Slicer ,Consolidating Data from external source or multiple files in less than a minute

Data Filter :--Normal Data Filter ,Advance Filter ,Calculation based filter ,Filter using wild cards ,

Subtotal

Analyzing data with What-If Analysis,Determine unknown with Goal Seek ,Analyze data with Data,

Table ,Sparklines for Data trends

Module 2: VBA

 Variables, Arrays, Constants, Data Types, Modules, Functions and subroutines,

Decisions and Looping, Strings and Functions and Message Boxes, Operators, Debugging, Errors

and the Error Function, Dialogs, Common Dialog Control, Command Bars and Buttons.

 Excel Object Model, Object Model--Main Objects, Business tool objects.

 Charts and Graphs, Working with Databases, API Calls, Class Modules, Pivot Tables, Ribbon

 Converting Labels to Numbers and Numbers to Labels, Transposing a Range of Cells, Adding

Formula Details into Comments, Calculating a Range, Reversing a Label, Who Created the

Workbook, Evaluating a Cell, Sorting Worksheets into Alphabetical Order, Replacing Characters

in a String, Timed Events, Auto-Totalling a Matrix of Numbers, Absolute and Relative Formulas,

Cells Containing Formulas, Alternate Rows and Columns of the Spreadsheet, Changing a Range

of Values, Cells by Reference to a Master Cell, Hidden Sheets Without a Password.

 VBA Automation Programme: Data distribution ,Data consolidate ,Outlook connectivity program

,User from programs ,data base connectivity with access ,Data insert program ,Data fetch

program, Workbook Consolidation Programme, Report Automation, Sample of Productivity

Report, Sample of Break Schedule & Time Report, Absenteeism , Shrinkage Report, Sample of

Sales Dashboard, Sample of KPI Dashboard & many more as per student requirement.

Module 3 Access

Introduction to Microsoft

Access Concept of

MS Access How to Start Access

Quick Access Toolbar

Type of Data Type

 Text

 Long

 Number

 Date & time

 Currency

 Auto number

 Yes/no

 Hyperlink

 Attachment

 Calculated

 Lookup wizard

Creating Database and Tables

Working with fields and records

a. Modify Table

b. Find and Replace

c. Sorting and filter

General Properties

Field size

Format

Input Mask

Caption

Default Value

Validation rule

Validation text

Required

Allow zero length

Index

Lookup

Text Box

Combo Box

List Box

Relational Database Techniques

 Types of Relationship

1. One to one

2. One to many

3. Many to many

 Referential Integrity

 Cast Cade Update

 Cast Cade Delete

 Joins

Working with Queries

Select Query

Make Table

Append Query

Update Query

Cross tab Query

Delete Query

Working with Forms

Overview

Form Wizard

From Scratch

Form design

Sub Forms

Form Commands

Sorting and filter

Switch-board

Working with Reports

Report Wizard

Repor