1 OPENCASTLABS LIMITED Suite 214, Madonna House | Westlands – Nairobi | Westlands Road Email: [email protected] | www.opencastlabs.co.ke
MICROSOFT EXCEL MASTER CLASS
Course Modules and Class Outline
KRA PIN: P051417872P
Company Registration No. CPR/2013/99872
2
MICROSOFT EXCEL MASTER CLASS | LIVE ON SITE/LIVE ONLINE
Advanced Microsoft Excel & Data Visualization Master Class | Course Content and Course Outline
Summary
Duration Level Technology Delivery Method
Online: 4 Hours per Session | 5 Sessions
Full Day: 2 Days
Class Assignments in-between sessions.
Advanced Microsoft Excel 2016/2019 Office 365
Add-Ons: Power Pivot/Power Query/Solver/Data Analysis Add-In/Power BI
In-Person Training/Live Online
Course Fees: KES. 19,500.00
OpenCastlabs Limited is a Certified Microsoft Silver Partner.
3
OVERVIEW
The course shows how to analyze and review data extracted from accounting systems and databases in order to prepare management reports and build efficient financial models.
The course studies Excel’s functions and how to use these effectively. The program also shows how to access the Visual Basic Editor and use macros and user defined functions in order to enhance Excel’s functionality. Half a day will also be spent showing attendees how to use Excel’s pivot table functionality. Pivot tables are often poorly understood and yet are the most powerful feature in Excel. A basic pivot table will allow you to summarize 50,000 rows of data in 30 seconds
The course is highly practical: delegates will be exposed to examples (in Microsoft Excel) throughout the 2 days.
Note that this course aims to cover a very wide field in a relatively short time and will thus provide more of an overview (with examples) than an in-depth, highly technical course.
EACH STUDENT WILL RECEIVE:
• Expert instruction from a Microsoft Excel Expert
• Sample course files and solutions.
• A course completion certificate upon successful completion of the course
METHODOLOGY
The course is highly interactive, and formal lecture content is kept to a minimum. Spreadsheet techniques are explained and demonstrated by using extensive case studies, adhering closely to real life examples. Throughout the program emphasis is laid on the importance of reality- checking and the dangers of spreadsheet blindness, on a fundamental understanding of key issues and on the skills of communicating conclusions from spreadsheet techniques to colleagues and partners. Delegates will be expected to be highly participative and motivated to learn, and to express their needs and priorities clearly.
POST TRAINING ASSESSMENT
4
The Microsoft Office Excel modules are spread over (1) Foundation, (2) Intermediate and (3) Advanced Microsoft Excel training. Our custom in-house pre-training assessment tool will output among other insights the following:
- Current Skill level – From Intermediate, Advanced & Expert (Macros & VBA)
- Learning Path – Analytics, Visualization, Data Science, Business Intelligence/Analyst - Familiarity across software versions – Office 2010/2013/2016/2019 and Office 365
We will send you a link to test yourself or your colleagues – with an automatic ranking and scoring based on skill level, competency and recommendation of the level of training to undertake. Our experts will also guide you on the process of obtaining the Microsoft Office Specialist (MOS) certification.
POST TRAINING ASSESSMENT
At end of each session and module, trainees are required to fill a custom feedback form on the following items:
- Impact of training per module and class topics as outline on the course content.
- Quality of Training Materials used
- Trainers’ knowledge on the subject matter
- Whether the trainer invited questions and comments
- How the training session/module is relevant to the staff being trained/
PRE-REQUISITES
Attendees will be expected to be using Excel in the context of a reporting related role and be comfortable with using Excel for data entry. A basic knowledge of Excel 2013 or a later version will therefore be assumed, but if delegates need a refresher on specific items then, time permitting, the course director will try and meet their needs. This class is taught on Office 2019 and Office 365
The course is also suitable for all senior professionals who may not have had specific spreadsheet training in reporting, analysis, and modeling and forecasting, who have developed their skills over time and who would like to develop a broader knowledge of what Excel can offer, as well as reassurance about their existing spreadsheet techniques.
Candidates ideally should bring their own computers with Excel 2016/2019 and Office 365 loaded in order to benefit from the course.
COVID RESTRICTIONS
5
Our classes are aligned to COVID19 restrictions. All in-house trainings attendees are firmly requested to put on their masks at all time, wash their hands at the building’s entrance points and use sanitizers provided at the Office/training centers
Our On-Site class sizes have also been reduced and is restricted to a maximum of 15 participants in a standard 30 Square foot training room.
SAMPLE HAPPY CUSTOMERS WHO BENEFITED FROM OUR MICROSOFT EXCEL MASTER CLASS RECENTLY:
6
COURSE OUTLINE AND CONTENTS
Module Session/Module Course Detail
Module I Introduction to Microsoft Excel 2016 The Workbook Model
Naming and Naming Conventions
• Introduction to Microsoft Excel and the Office 365 Environment
• Microsoft Excel Short-Cuts
• Introduction to Microsoft Excel Range Names and Naming Conventions
• Understanding number formats, custom number formats and Calculation Types
• Working with Excel Ranges, Multiple Workbooks and Excel Data Model
• Preparing Excel Data from Printing – Advanced Formatting Techniques applied to massive data sets
• Freezing and Unfreezing Panes
• Splitting a Worksheet Window
• Using Functions with Lists
Module I.B Mastering Range Names Data Validation
Data Integrity (In
Questionnaires/Templates/Financial Data)
• Mastering Range Names – Using Names inside Formulas and Functions
• Budget Models – Introducing What-If Analysis in Microsoft Excel
7
Working with Lists • Data Integrity – Controlling Data Entry with Excel Data Validation
• Working with Scenarios/Data Table Outputs and Inputs Module II
Pivot Tables – Part I
The Microsoft Excel Data Model
PowerPivot for Excel 2013/2016/2019
• Summarizing Data with Pivot Tables – Part I
• Preparing Data for Analysis with Pivot Tables
• Understanding PivotTables
• Creating a PivotTable using Worksheet Data
• Creating a PivotTable using an External Data Connection
• Laying out a PivotTable on a Worksheet
• Modifying PivotTable Fields
• Using a Report Filter
• Refreshing a PivotTable
• Formatting a PivotTable
Module III
Microsoft Excel Function – Part I
• Entering Excel Functions
• Function Classes and Categories
• Functions
o Text Functionsi o LOOKUP Functionsii
8
Module IV Tables and Working on Excel like a Database
Pivot Tables – Part II
Creating Relationships in Microsoft Excel (Power Pivot)
Analyzing Data from Multiple Data Sources
Creating Measures and Calculated Columns in Excel using Power Pivot
• Working with Tables o Creating a Table o Formatting a Table o Sorting a Table o Filtering a Table o Creating a Slicer
o Working with the Total Row o Creating a Calculated Column
• Enhancing Pivot Tables
o Working with Summary Functions o Creating a Slicer
o Applying Filters o Grouping Data o Using a Timeline
o Creating a Calculated Field o Creating a Calculated Item o Creating a PivotChart
Module VI Microsoft Excel Functions – Part II • Functions for Summarizing Data: Math Functionsiii
• Decision Functions: Logical Functions
9
Functions for Automating Reports from Databases and Finance Applications/ERPs
• Practical Exercises for summarizing and evaluation data with Math and IF Functions
Module VII
Charts & Visualizations
Building Scalable Dashboards
• Preparing Data for Charting
• Chart Models
o Charts that show trends o Charts that show Differences
o Combo Charts: Charts with multiple axis o Charts that show trends
• Dynamics charts with IF and Lookup Functions
• Charting for uneven data
• Charting for Missing Data
• Using Pictures to visualize data
• Dashboards best-practices and Dashboards tools Module VIII Introduction to Power BI • Getting Started – Installing Power BI
• Getting Data from Excel, Text and Azure (SQL Server)
• Basic Visualization & Measures Module IX Working with Large Data Sets
Consolidating Data (Workbooks and Worksheets)
• Filtering Data
• Using the Advanced Filter Tool o Filtering with
• Analyzing and Transforming data using Criteria
10
Power Query ETL
Power Query – Shaping & Transforming Data
• Microsoft Excel Query – Extraction, Transformation and Loading (ETL) Excel data from external data sources
• Consolidating Data
o Consolidating Multiple Workbooks o Consolidating Multiple Worksheets
Module X
Automating Microsoft Excel (Macros)
• Macros
o Introduction to Macros o What Macros can do o Recording Macros
o Assigning Key Board Shortcuts, Buttons and Quick Access Toolbar
o Editing Macros
o Practical Examples in Microsoft Excel Data Analysis Automation
• Random Useful Items
o Introduction to Power BI o Common Excel Add-Ins
i Text Functions: TEXT, CONCATENATE, LEFT, RIGHT, MID, LEN, CLEAN, TRIM and many more!
ii Lookup Functions: VLOOKUP, INDEX, MATCH, INDEX, LOOKUP, HLOKUP, OFFSET and many more!
iii MATH Functions: SUMIF, SUMIFS, AVERAGE, AVERAGEIFS, COUNT, COUNTIFS, COUNTBLANK and many more