MS-EXCEL
Training Module
Advanced
Excel
Time:
40 hrs
1.
Pivots
and Pivot Graphs:
a) Creating
of Pivot Table
b) Using
Summary function in Pivot Table
c) Pivot
Table Calculations
d) Creating
own formulas in Pivots
e) Grouping
and Ungrouping in Pivot Tables
f)
Creating Pivot Graph
g) Custom
Filter on Pivot Table
h) Formatting
Pivot table (Removing old data, grand total, subtotal …etc.)
2.
Working
on Objects:
a) Inserting
Objects
b) Inserting
different files into Excel (like .doc, .pdf, .txt files)
c) Hyper
linking to different sheets
d) Providing
screen tips in hyper link
e) Hyperlink
to send emails
f)
Inserting symbols
g) Sending
working file to an email
3.
Data
Management in Excel:
Sorting:
a. Rearranging
of Data
b. Sorting
by alphabets, numbers and time
Filtering Data:
a. Using
Auto filter option
b. Custom
Filtering with different options
c. Advanced
Filtering
d. Find
Unique records using advanced filtering
e. Filter
using operators AND/OR
f. Filter
by color
Grouping and Outlining Data
a. Group
and ungroup rows and columns
b. Auto
outlining the data
Freezing and Unfreezing
Data Validation:
a. Use
Data Validation in Excel
b. Using
Input Message in Data validation
c. Using
error message in Data Validation
Removing duplicate records:
a. Remove
duplicate records in a spread sheet
b. Customize
to remove the duplicate values
Split Texts:
a. Split
texts to different columns
b. Remove
special character from a string and split the string
Data Consolidation:
a. Combining
different spreadsheet using data consolidation
b. Using
summary function in data consolidation
Conditional Formatting:
a. Use of
conditional formatting
b. Custom
formatting
Copy Data:
a. Use of
format painter
b. Use of
paste special
c. Strike
through a value
d. Find
and Replace
View Multiple Files:
a. Arrange
multiple windows to view in one time.
b. Split
big sheet into sub sheets for better analysis
Working on Tab:
a. Hide/
Unhide a tab
b. Color
a tab
c. Move
/Copy the tab to another sheet/workbook
4.
Advanced
Functions:
a. V-Lookup
b. H-Lookup
c. IF
d. ISERROR
e. Index
Match
f. Rows
g. Columns
h. Match
i. Offset
j. Get
Pivot Data
k. Date
and Time Functions
l. Text
Functions
m. Operators
n. Mathematical
functions
o. Calculation
options
Name Manager:
a. Define
a name to a range
b. Use of
name manager
c. Using
formulas in name manager
d. Editing
name range
5.
Data
Connection from External Data:
a. From
access
b. From
SQL
c. From
Web
d. From
ODBC
6.
Customize
your Excel view:
a. Customize
Excel view
b. Developer
Option
c. Trust
Centre
d. Add-Ins
e. Save
directory options
f. Language
Setting
g. Quick
access toolbar
7.
Security
options in excel:
a. Protecting
the worksheet
b. Protecting
the workbooks
c. Sharing
the workbook
d. Allow
users to edit ranges
Microsoft Excel VBA
1. Introduction to VBA