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
= Assignment=
Session – 24
Intoduction To Ai
Different Kinds Of Ai
How Ai Work
Terms And Condition To
Use Ai
Advantage And Disadvantage Of Ai
What Is Chat Gpt
Install Chatgpt In Excel
Writing Different Formats In Excel
Submtiing Different Question To Openai
Redefining Prompt
*Assignments*
Session -19
Writing Letters
Facebook
Whatsapp
Instagram Post
Limiting
*Assignments*
Session -20
Extracting Realtime Data In Table Format
Specific Column Of Table
Creating Simple Formula And Complex Formulas
Creating Simple Function And Complex Functions
Session -21
Extracting Data From Excel Table
City, Formula Phone Numbers Etc
Flash Fill
Quick Analysis
Formatting
Session – 22
Conditional Formatting
Charts
Pivot Table
Sparkline
Creating Dash Board In Excel And Project
= End =
