Module 1 : Excel Basics

1.1 Office button
Ribbon, Tabs, Groups
Quick Access Toolbar
Formula bar
Rows and Columns
Sheet Options

1.2 Working With Ranges
Selection techniques
Shortcuts
Range names
Goto Special, Find and Replace, Comments
Fill Series

1.3 Format Data
Format cells
Cell styles
Format as tables
Format Painter
Colors and borders
Paste as Special options(Value, Transpose)



Module 2 : Formulae & Functions

2.1 Simple calculations
Excel formula(ex. Calculate percentage)
Cell references

2.2 Functions
Basic Functions(Sum, Max, Min, Average, Count, Counta, Countblank)
Conditional Functions(Countif, Sumif, Averageif)
Simple IF function
Vlookup/Hlookup

Module 3 : Sorting & Filtering

3.3 Sort Data
Single column sort
Multi column sort

3.4 Filter Data
Autofilter
Subtotals
Single level subtotal

Module 4 : Pivot Reports

4.1 Pivot Reports
Pivot Table 
Need & Application
Data Source 
Fields & Types
Functions 
Filter By Field

4.2 Pivot Chart 
Create Pivot Chart 
Custom Pivot Chart 
Change Data Source

Module 5 : Format | Text | Import

5.1 Conditional Format
Highlight cell values in a column
Highlight a record in a data

5.2 Text & Date Functions
Date Functions - 8
Text Functions -12
Text to column
Remove Duplicates
Freeze Panes/Split

5.3 Import From Other Applications
Data from text files
Data from access tables
.
- - - - ADVANCE EXCEL - - - -

Module 1
Group of Worksheets
Make Format easy
Collective print 

Data Validation 
Text, numeric...built-in
Us formulae, compare with other fields 
Create List based on other list values


Module 2
Conditional Format
Built-in
Data bars
Icons 
Formulae option
With dropdown lists  
 
Paste Special
Mathematical Operators 
Values, comments, blanks
Link, transpose
Hyper Link – Options

Module 3
Name & Name in formula
Range names, named constants
Apply name in formula
Edit names
Advantages of us names 
Substitute names in exist formulae 

VLookup and Hlookup 
vLookup /hLookup 
Wild characters 
Repeated values 
Multiple values 
Return Multiple Fields
Reverse Lookup

Module 4
Match & Index
Index 
Match 
Index-Match Substitute
Repeat Values

Conditional IF
Simple, Nested, Multiple
Simple IF
Nested IF
AND | OR |NOT

IF and IFS functions 
Countif, Sumif, Averageif 
Countifs, Sumifs, Averageifs 

Module 5
Database Functions
Database Functions 
Ease of usage 
Substitute for vLookup 

Subtotals  
Advantages 
Single level subtotal 
Multi-Level subtotal

Array Functions
Entry of array
Pros-Cons
Application of array functions 

Module 6
Auto Filters and Advanced Filters
Merits
Demerits 
Advance Filter
Us AND / OR Criteria
Copy to Other Dest.
Remove Duplicates 

Table in Excel
Advantage of Tables
Table formula
Autofill
Create Dynamic Range 

Module 7
Date &Time Functions
Calculations
D&T Datatypes
Upper & Lower Limits 
Date Functions
Data Validation for Date 
Time Functions 
Data Validation for Time
Goal Seek
Goal Seek For Analysis 
Hypothesis 
Financial Application
Non-Financial Application

Module 8
MSQuery to Import Data  
Import Data Form Different Worksheets / Workbooks 
MS Query 
Customize Query Options
Parameter Query

Consolidation 
Consolidate Data from Other Sheets / Books 
Different Consolidation Functions
Consolidate By Value 
Consolidate By Position

Solver for What-If Analysis  
Hypothesis
Complex Analysis 
Solver Options
Solver Optimization
Summary Report

Module 9
Chart 
Create Dynamic Charts 
Simple Charts
Change Data Source, Series Values, Chart Type 
Format Axes, Labels, Legends, Titles
Automate Chart Title 
Switch Axis, Plot on Secondary Axis 
Customize Items with Images 
Dynamic Charts
Special Charts - Speedometer, Thermometer, Waterfall, Pareto, Gantt, Guage Etc... 

Module 10
Pivot Tables
Introduction, Concepts  
Advantage of Pivot Table
Data Range
Create Simple Pivot Table 
Change Display Options, Totals
Change Summary Functions
Pivot Table for Analysis
Create Calculated Field, Item
Group
Slicer
Timeline

Use Pivot Table To Create Frequency
Work On Dynamic Range
Internal Vs External Data Source
Move, Refresh, Delete

Pivot Charts
Create Dynamic Pivot Charts
Slicers to Automate
Edit Charts
Change Source Data
Move, Edit, Delete
Make a Static Chart 

Module 11
Create Dynamic Ranges
App & Advantage of Dynamic Ranges 
App Offset Function
App Excel Table

Module 12
Optional Topics
Indirect Function
Choose Function
Getpivotdata Function
Subtotal Function
Aggregate Function
Financial Functions
Dashboards – Introduction
Shortcut Keys 
Protection & Track Changes
Special Commands

- - - - - - - - - - - - - - - - - - - -
Placement Support Services available for Trainees at no additional cost

Call  96505 15620 for a Free Demo Class
- - - - - - - - - - - - - - - - - - - -
Practical MIS & Analytics Training Provided by Experienced Sr. Managers with Leading Corporates & MNC's

Interview Questions Covered throughout the Program