Masters in Excel Application
Duration:
4 days of Extensive Hands-on Training – 16 hours
(Fast track: 2 days)
Objective:
This Module will train participants on various Advanced Excel Skills and Functions, making them proficient to perform complex tasks more efficiently.
Here our preliminary focus is on Application, applying the functions to improve efficiency, rest follows next. So, we definitely teach you functions, formulas and excel elements, but most importantly we teach you the science of implementation.
As a user whenever you face an complex excel scenario, using the science of implementation, how best you can achieve your results with the minimum effort required.
Target Audience:
- Anybody and Everybody who uses excel at their workplace for around 30% of their day job
- Anybody using or wishes to use excel for data processing, reporting, analysis, modelling or data management
- Anyone working on large chunk of numbers in Excel
- Domain Specific to BPO, KPO, Operations, Sales, Secretarial, Support and B-school graduates starting their corporate career
Course Outline:
Quick Recap
Basic Excel functionalities, Software uses and compatibilities, Exploring File Management Fundamentals, Importing and Exporting Files, File Security, Enhancing Accessibility
Working with Conditional Statements and functions
Understanding Logical Functions, Working with IF function, Understanding and creating Nested functions, Working with “AND” and “OR”, Working with IFERROR, SUMIFS and COUNTIFS etc…
Working with Advanced Excel Functions
Understanding Data Reference and Lookup Functions, Advanced Lookup Functions (INDEX, MATCH, Self Joins), Understanding Text Functions, Understanding Information Functions
Data enrichment & massaging
Naming Data Ranges, Advanced Sort Options, Levels of sorting, Using Data Filters, Advanced Filter, Data Validations Basic & Advanced, Performing Data Validations on different worksheets, Conditional Validations with Formulas, Sharing Workbook patterns, Formula Auditing
Data Presentation I (Advanced Pivoting)
Creating and Rearranging PivotTables, Creating customized Pivot Fields, Report Layout Options, Formatting a PivotTable, Working with Totals and Subtotals, Sorting and Filtering PivotTable Fields, Changing PivotTable Calculations, Grouping and Ungrouping Data
Data Presentation II (Advanced Charting)
Creating User-defined Charts, Adding and formatting different chart elements, Axis management, incorporating different Data sources, using different worksheets, choosing and working with variety of chart types, Extensive functionalities of Pivot Charts
What-IF Analysis with Template Creation
Using Data Tables, Goal Seek, Subtotals and Solvers, Creating and editing various scenarios, creating advanced Templates for performing what-if analysis
Introduction to Macros, Recording Macros
A quick introduction on what is Macros, How do they work, Steps to record a Macro.
Vikas V. Valecha – The Excel Coach
Over 10,000 working professionals have attended his hands-on workshops on Advanced Excel Techniques and Excel Macros. He has been one of the well-known office automation trainer and consultant in Investment Banking and Finance space, having an experience of over a decade in the Industry.
The results of his workshops are instantly evident; he is experienced in developing training programs focused on requirements, interests and expectations. His approach is very bullish, interactive and humorous. Training is more of a passion to him than work.
He started his career with JP Morgan Chase, where he was a part of Research Team to put in his expertise to simplify Processes using Office Automation Skills. He later moved to Nomura, where he contributed remarkable being a part of Metrics team to design dashboards and trained employees across LOB’s on Advanced Excel. He has worked in various capacities as an Automation Consultant and a Trainer and had been associated to – JP Morgan, Morgan Stanley, Credit Suisse, BMR Advisors, Bain Capital, Siemens, CMC Ltd, Tata Communications, Subhkam Capital, Accenture, Agfa Healthcare, Kouni Travels to name a few.
He is also a speaker for Executive MBA students at various B schools and enjoys teaching and interacting with young minds!