Advanced Excel
Download the course material
​
Duration: 3 to 4 Weeks
contact :9491882804
EXCEL INTRODUCTION
Excel Home Tab
Shortcut Keys
Paste Special
Arithmetical Function
SUM
SUM IF
SUM IFS
COUNT
COUNT A
COUNT IF
COUNT IFS
AVG
AVG IF
AVG IFS
Data Validation
Data Validation (Numbers, Dates, Text length)
Arthmatical operation with including data validation.
Lookups
V LOOKUP
H LOOKUP
MATCH
INDEX
V LOOKUP MATCH
V LOOKUP CHOOSE
V LOOKUP ARRAY
​
MULTIPLE LOOKPS
CONDITIONAL STATEMENT & LOGICAL OPERATOR
IF CONDTION
NESTED IFS
IF AND ,IF OR
MORE CASE STUDIES
ISERROR, ISBLANK, ISNUMBER, ISTEXT, IFERROR
OFFSET
INDIRECT
PMT
​
​
​
Advanced Excel
Conditional Formatting
Conditional Formatting I (Blanks, Errors, Values, Duplicates)
Conditional Formatting II (Formula-based)
Conditional Formatting III (Data Bars, Color Scales, Icon Sets)
Data Cleansing functions
Using CONCATENATE, & - to join data strings
Text-to-Columns – Delimited
Text-to-Columns - Fixed Width
Find & Replace – Advanced
UPPER, PROPER, LOWER, TRIM, T, N, REPT
LEFT-RIGHT-MID, LEN, SEARCH
SEARCH vs. FIND
REPLACE, SUBSTITUTE
Pivot table
Starting and Understanding Pivot Table Areas
Creating Yearly Quarterly and Monthly Reports
Refreshing Pivot Table
Using Interactive Slicer and Timeline
Comparing Yearly Data with Pivot
Creating Dynamic Dashboard with Pivot
Creating Interactive Dashboard with Charts
Date & TIME Functions
​
WHAT-IF ANALYSIS
SCENARIO MANAGER
GOAL SEEK
DATA TABLE
GROWTH PERCENTAGES
How to Calculate Daily/Monthly Percentage Growth In MS Excel
Year over Year % Growth
How to Calculate Sales Growth in Excel
Target vs Achievement
​
​
​
Date & TIME Functions
EXPORT DATA
SUB TOTAL
CONSOLIDATE
TEXT TO COLUMN- Delimited,Fixed width
GROUP
UN GROUP
FREEZE
UN FREEZE
PROTECT SHEET
SHARE WORKBOOK
PROTECT WORKBOOK
REMOVE DUPLICATES
Rank
SUMPRODUCT, POWER, ROUND, MROUND
Array Formulas
Array Formula (CtrlShift Enter)
MAX IF and MIN IF with one condition
Array Formula using MAX() MIN() and IF() - 2 criteria
Charts
Dash Boards
Excel Tasks
With Real Time Examples
Course Material
INTERVIEW QUESTIONS
50 Questions are taken from different - different Interviews of MIS, Analytics, Data Analytics. All the questions are very very important. Once you complete the practice of the 50 Question, you will never fail in any Interview. These are Awesome questions. These are trial and tested.