Advanced Excel for Data Reporting and Analysis
Duration:
5 days of Extensive Hands-on Training – 20 hours
(Fast track: 3 days)
Investment:
PCKP 1500
Objective: This Module will train participants on various Advanced Excel Skills and Functions, making them proficient to solve real-time industry linked scenarios, enabling them to perform efficiently.
Target Audience:
Who are a part of or wish to pursue the career in
- MIS
- Data Reporting
- Metrics
- Accounts & Finance
- Reconciliation Analysts
- Business Analysts
- CIO’s desk
Course Outline:
Quick Recap of Excel Basics
Basic Excel functionalities, Software uses and compatibilities, Exploring File Management Fundamentals, Importing and Exporting Files, File Security, Enhancing Accessibility
Applying 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…
Data Reconciliation
Understanding Data References, Assignments on combined TextFunctions, Array and lookup Functions like Lookups, Vlookup, Hlookup, Index, Match, Offset, Address, Indirect, Self Joins etc…
Information and Date Functions
Real time scenarios for usage of informationand date Functions like ISNA, ISNUMBER, ISTEXT, ISBLANK, ISERR, ISLOGICAL, NETWORKDAYS, WORKDAY etc…
Key Indicators
Methods to identify and link active performance and volume indicators, usage of advanced what-if analysis templates usingData Tables, Goal Seek, Subtotals and Solvers.
Data Rationale
Data Validations – Basic, Advanced and Indirect, Dynamic Name Ranges, Managing Conditional Formatting Rules, Advanced cell referencing, Data sourcing and Consolidation, effectively Handling Duplicates, Data Security aspects.
Report Creation
Understanding Report Elements and Report Layout options, Creating and Rearranging PivotTables, Creating customized Pivot Fields, Creating User-defined Charts, Adding and formatting different chart elements, Adding dynamics to tables and charts.
Introduction to Macros, Recording Macros
A quick introduction on what is Macros, How do they work, Steps to record a Macro.