Data Management and Analysis using Advanced Excel for Project Managers and Officers
This will be a comprehensive, Advanced and practical training course for finance staff, departmental heads and other staff whose roles necessitate periodic reports or otherwise and whose functionality imperatively revolves around use of Excel Spreadsheets. The course aims to impart skills that will among other things lead to objective reporting and improved use of excel spreadsheets.
Microsoft Excel has countless business value adding features and the expert trainers will aim to select the most powerful and business relevant tools and cover these while ensuring the practical sessions enable learners to emerge from the training more empowered.
Advanced Excel Modeling
This will be a comprehensive, advanced and practical training course for project heads and other staff whose roles necessitate periodic reports or otherwise and whose functionality imperatively revolves around use of Excel Spreadsheets. The course aims to impart skills that will among other things lead to objective reporting and improved use of excel spreadsheets.
Microsoft Excel has countless value adding features and the expert trainers will aim to select the most powerful and relevant tools and cover these while ensuring the practical sessions enable learners to emerge from the training more empowered.
Main Training Modules
Data Manipulation in Excel
How Excel handles different data types
Data consistency, starting with the end in view
Building Datasheets that can easily scale
Sorting
Cascaded sorting
Sorting across rows (left to right sorting, not the usual up to down sorting) Sorting and Conditional
Formatting to identify trends
Filtering Data cleaning
Removing duplicates
Text-to-column
Grouping
Data Validation
Conditional Formatting
Data formatting
Using Tables (and when to convert to tables)
Formatting for printing
Formatting for email
Data Review and formatting for 3rd party use
Charts
Chart types
Line chart and when to use it
Column chart and when to use it
Bar chart and when to use it
Pie chart and its dangers
Combining charts; when and how.
Dynamic Charts, using filter.
Best practices when making charts Sparklines
Power Map and Power View (Excel 2013)
Pivot Table, Pivot Chart and Power Pivot
Pivot Table
Default Pivot Table
Tabular Pivot Table
Pivot Table Filtering
Making a very dynamic regular table from Pivot Table
Calculations and Formula use with Pivot Table
Advanced Pivot Table tricks
Pivot Chart
Pivot Chart and its limitations
Dynamic Pivot Charts
PowerPivot (for Excel 2010 and 2013) only
Lookup functions
V-lookup
H-lookup
Looking up the last data or pattern in a particular row or column
Overcoming the limitations of Vlookup and Hlookup using index and match functions
Power Functions
IF, IFERROR, AND, OR, ISBLANK, and others in the same family
TEXT manipulative functions to make a completely automated Dashboard
COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables
MATCH and INDEX to do the impossible
Executive Dashboards and Reporting
Best Practices
Executive Dashboards
Executive Dashboards
Dynamic Reports
Determining the KPIs and tracking them
Strategic Insights & Analysis
Data Visualization
Having the audience/recipient in mind
E-mails and Excel reports
Way forward After the Training
Participants will develop a work plan through the help of facilitators that stipulates application of skills acquired in improving their organizations. ASPM will monitor implementation progress after the training
Training Evaluation
Participants will undertake a simple assessment before the training to gauge knowledge and skills on data analysis & management, another assessment will be done after the training in-order to demonstrate knowledge gained through the training.