MODULE 2
EXCEL: BASICS TO ADVANCED
Intorduction to Excel :
Quantum of Excel and Basics
Workbook
Types of workbooks and their uses
Common uses of Excel
Cell
Row
Column
Range/Array
Name box
Formatting of cells
Ribbon
Formula bar Status bar
Basic operators
Intorduction to Functions :
Commonly used Excel Functions
What is syntax
Arguments
Navigations using keyboard
Shortcuts
Sum
Average
Maximum- Minimum
Product
CountBlank
CountA
CountIF
If,Now,Today
Cut,Copy,Paste,Paste Special
Anchoring data :
Referencing , Named ranges and its uses
Absolute
Relative
Mixed referencing
Name Manager
Named ranges
Creating Tables
Create functions using named ranges AND/OR
referencing
Referring data from different tables:
Various types of Lookup, Nested IF
Lookup
Vlookup
Nested Vlookup
Hlookup
Index
Index with Match function
If with combination of AND/OR
IFERROR
Referring data from different tables:
Advanced functions
RANK
RAND
RANDBETWEEN
INDIRECT with ADDRESS & MATCH
OFFSET
Data Handling : Data cleaning,
Data type identification, Data restrictions
LEN
LEFT
RIGHT
MID
CONCATENATE
CONCAT
FIND
SUBSTITUTE
TEXT
TRIM
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
WORKDAYINTL
ISNUMBER
ISNA
ISNONTEXT
ISEVEN
ISODD
ISFORMULA
ISERROR
Data validation
Depended drop down
Protecting cell
Array
range
sheet
Workbook
Data Handling : Formatting and Filtering
Conditional formatting
Sort
Advanced Sort
Filtering
Data Summerization :
Advanced functions, Charts
Sum
Average
Max-Min with IF and IF'S
CountIF'S
Various types of Charts
Data Summerization :
Pivots, Preparing the Dashboard
Pivot table
Slicers
Pivot charts
Calculated field
Calculated item
ADD/REMOVE/CHANGE data into the pivot table
Refreshing pivot data
Dashboard creation
Power query, Power pivot
Cleaning data
Extracting data from multiple sources
Transforming data
Imputation techniques
Getting data from CSV files
Databases
Workbooks
Webpages
Power query, Power pivot, Use case
discussion:
Data Preparation, Project
Summarization
Consolidating data from multiple
sources
Merging data from different
workbooks/worksheets
Relationships
Use Data handling steps taught in the
previous session
Use Data summarization techniques
Populate output in Excel
Combining multiple functions
Intro to Automation:Macros(Recorded
/VBA)
How VBA works
Record a sample macro
VBA
If constructs, Select construct,
User defined functions
Input box, message box
Procedures
Automatic macros
Methods to cleanup the codes