Training on Advanced Data Management and Analysis using Microsoft Excel

Excel has powerful tools that enable busy users to set up systems that simplify data management, analysis and presentation. Effectively using Excel allows users to devote their time more profitably to making decisions about and acting on results, with more effective outcomes. This course gives practical experience in the use of Microsoft Excel for data summary, presentation and for other basic statistical work. It concentrates on areas where Excel really can excel in your workplace functions. We look at data entry and management, pivot tables, formulae, graphics for results presentation and useful shortcuts. Participants will learn through a series of practical simulations which ensures that they maximize their learning and can use the tools immediately when they return to the office. In addition to the practical component, all participants will also receive a manual describing all the techniques learned.

Target Participants

This course is aimed at persons who handle a lot of data, often processing and analyzing it using Microsoft Excel. It is ideal for monitoring and evaluation practitioners, researchers as well as accounting/finance and administrative support staff.

What you will learn

By the end of the course the learner should be able to:

• Familiarize with advanced formulas and functions in Microsoft Excel
• Easily combine, link and export data
• Create key outputs from data (tables and graphs)
• Reduce the amount of time spent in delivering output through Microsoft Excel through time-saving keyboard shortcuts
• Easily summarize, analyse and present data through pivot tables and charts.

Course duration   5 days

Course Outline

Module 1:

Introduction

• Navigating within the sheet
• Making selection
• Entering data
• Data management in Excel
• Deleting unwanted data
• Find errors, conditional formatting and data validation

Module 2:

Introduction to Excel Formatting

• Adjusting Numeric Formats
• Controlling Fonts, Borders and Cell Color
• Alignment Tools & Other formatting Tools
• Row, Column and Sheet Adjustment & Adding Picture
• Referencing in Excel
• Working with Structured tables
• Excel Formula Basics
• Keyboard shortcuts in Excel

Module 3:

Pivot Table and Pivot Charts

• Preparing data for use with Pivot Table
• Creating Pivoting Tables
• Summarizing PivotTable Data
• Sorting and Filtering Pivot Table
• Formatting PivotTable
• Applying Conditional Formatting
• Working with Calculated Fields
• Grouping Data
• Filters and Advanced Filter
• Advanced Data Field Settings
• Creating Pivot Chart
• Power Query
• Printing Pivot Tables and Pivot Charts
• GETPIVOTDATA function

Module 4:

Excel Functions

• Lookup/Ref Functions
• String/Text Functions
• Date/Time Functions
• Math/Trig Functions
• Statistical Functions
• Logical Functions
• Information Functions
• Financial Functions
• Database Functions

Module 5:

Practical application of Excel

• Project assignment involving huge application of Pivot Tables, Formulae and Shortcuts
• Additional topics: Plans will be made to cover a few additional topics as per the suggestions that would be made by the participants.

Training Approach

This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.

Training manuals and additional reference materials are provided to the participants.

Certification

Upon successful completion of this course, participants will be issued with a certificate.

Tailor-Made Course

We can also do this as a tailor-made course to meet organization-wide needs. Contact us to find out more via email.

Payment

The training fee covers tuition fees, learning materials, and training venue. Accommodation and airport transfer are arranged for our participants upon request.

Payment should be sent to our bank account before start of training and proof of payment sent via email.