Data Management and Analysis using Advanced Excel for Data Capturing Officers

This short course 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.