MS EXCEL Advanced
Session – 1
Naming Range
Working with Range Names
Jumping to a Named Range
Assigning Names
Using Range Names in Formulas
Creating Range Names from Headings
Applying Range Names
Deleting Range Names
*Assignments*
Session – 2
Formulas
What is Difference Between Formula and Functions
Types of references
Relative
Absolute
Mixed
3D reference
*Assignments*
Session – 3
Auto-fill
Fill series
With Without control
creating your own auto-fill
*Assignments*
Session – 4
Functions
How to write a function
shortcut for Functions
Different parts of Functions
*Assignments*
Session – 5
Text Functions
Concatenate
Right
Left
Len
Substitute
Exact
Trim
Proper
Upper
Lower
*Assignments*
Session – 6
Statistical Function
Count
CountA
CountBlank
Countif
Countifs
Sumif
Sumifs
*Assignments*
Session -7
Logical Functions
if
And
Or
Mixed
Nested if
*Assignments*
Session – 8
Reference Functions
Vlookup
Hlookup
Match
Index
*Assignments*
Session – 9
Date And Time Functions
NOW
Date
Time
DatedIF
*Assignments*
Session – 10
Finacial Functions
Pmt
Pv
FV
Rate
Nper
*Assignments*
Session – 11
Charts
Column Charts
Pie Charts
Parts of Charts
Advanced Pie Chart
Creating your Own Chart
*Assignments*
Session -12
What If Analysis
Manula V/s What If Analysis
Goal Seek
Senario
Solver
Introduction To Add Ins
*Assignments*
Session – 13
HyperLink
Session – 14
Paste Special
Session – 15
Importing And Exporing Data
Web Query
Importing From Different Format
Auto Updating
*Assignments*
Session – 16
Macro
Recording Macro
Recording Macro v/s Writing
Why Macro
Importance Of Macro
Different Ways Of Executing Macros
Storing Macors
*Assignments*
Session -17
Pivot Table
Concept Of Pivot Table
Concept Of Pivot Chart
Parts Of Pivot Table
Advanced Pivot Table
introduction To Power Pivot
*Assignments*
Session – 18
Sorting And Filtering
Customized Sorting
Sorting Left to Right
Color Sorting
Gap Sorting
Advanced Filter
Multiple Filter
*Assignments*
Session -19
Linking And Updating
Advanced Linking
Multiple Sheets
*Assignments*
Session – 20
Shortcut Keys
Session – 21
Formula Auditing
Session – 22
Sub Total
Session -23
Data Validation
Conditional Formatting
= End =
HEMANTKAJARIA
SENIOR CORPORATE TRAINER
TRAINING FOR INDIVIDUALS | GROUP | CORPORATE
(+91) 9821 041 860 | 9820 625 162
email:hkajaria@gmail.com
www.hemantkajaria.com
