Many people associate analytics with pretty charts, complex analyses leading to pivotal insights that support organization outcomes. But those are only some of the more visible aspects of analytics and the tip of the iceberg. There is much more to the analytics value chain; and it really starts with data. Data in its raw form may not be ready for analytics; data could be sitting across disparate locations, or may contain errors, or may lack crucial metrics that need to be calculated. A lot of works goes into preparing data for analytics; requiring unique skillsets quite different from analysis.

“It's amazing how much data is out there. The question is how do we put it in a form that's usable? – Bill Ford

About the workshop

This 2 Day workshop aims to provide a broad but practical overview of the analytics value chain, with a specific focus on data cleaning and processing. Delegates will get the opportunity to get their hands “dirty” with sizable data sets and experience what it takes to clean & process data across MS Excel and Power Query before turning them into accessible insights through interactive reports.

Objective

• Understand the analytics process and recognize the pivotal role that data cleaning and processing play in preparing data for analysis.


• Develop technical skills for data cleaning and processing using Power Query and MS Excel, including the ability to:
  • Transform raw data into a structured format.
  • Merge data across multiple tables.
  • Identify and remove duplicate records.
  • Manage various types of data errors.
  • Create new calculated data fields from existing data.


• Turn cleaned data into useful reports for analytics, and automatically update these reports as new data is integrated.

Outline

Day 1

• Introduction to Data Analytics
√ Participants will understand the concept of the analytics value chain and the significant role that data cleaning and processing play in ensuring data readiness for analysis.

• Understanding Data Quality
√ Participants will explore the various attributes that contribute to data quality and learn how to evaluate data files for accuracy and readiness for analysis.
√ Through practical examples, participants will determine what is needed to improve data quality, including a quiz to reinforce their learning.

• Introduction to Power Query
√ This segment will introduce participants to Power Query and its key functionalities for data preparation. Participants will understand the differences, benefits, and limitations of using Power Query compared to MS Excel for data processing.

• Working with Tidy Data
√ Participants will learn the importance of tidy data and how to transform unstructured datasets into tidy formats. Practical exercises will guide participants in restructuring data and splitting columns to achieve tidy datasets.

• Exploring the Movie Dataset for Data Exploration
√ Using a case study on a movie dataset, participants will appreciate the importance of understanding the business context before performing data analysis.
√ Participants will determine the key considerations when studying a dataset to extract meaningful insights.

• Exploration Using Excel
√ Participants will interact with data using pivot tables and auto-filters to explore the dataset. Hands-on exercises will help participants understand how to manipulate data within Excel to find insights.

• Conditional Formatting for Error Detection
√ In this session, participants will explore the use of conditional formatting to identify and resolve data errors. This practical exercise will demonstrate how conditional formatting can be used to highlight inconsistencies and errors in the dataset.

• Exploration Using Power Query
√ Participants will apply Power Query's tools, such as column quality checks and data distribution analysis, to explore and improve the dataset.


Day 2

• Assembling Data Tables
√ In this segment, participants will explore the use of merge and append functions to combine multiple data sources. Practical exercises will demonstrate how to create calculated fields using both Excel and Power Query.

• Managing Duplicate Records
√ Participants will understand the implications of duplicate records and learn how to identify and remove them effectively. Hands-on exercises will guide participants through the process of handling duplicates using both Excel and Power Query.

• Utilizing Excel Functions for Data Cleaning
√ Participants will apply key Excel functions such as IF, OR, AND, and VLOOKUP to fix data errors. This segment includes practical exercises that demonstrate how these functions can be used to clean data efficiently.

• Groupwork on the Movie Dataset
√ In this collaborative session, participants will work in groups to apply the concepts learned in Day 1 and Day 2 to the movie dataset. They will use pivot tables and charts to interact with the data and extract meaningful insights.

• Capstone Case Study: Yummy Bakery Store
√ Participants will be introduced to the business context of the Yummy Bakery Store dataset. They will then conduct data cleaning and processing exercises on the dataset to generate actionable insights. This hands-on team exercise will involve using Excel and Power Query to clean and transform the data.

√ In the final segment, participants will complete their data processing and generate final insights from the dataset. Teams will present their findings, followed by a class discussion on the different approaches and insights derived.

Who should attend

• Professionals with data management and/or reporting responsibilities; who are looking for a more structured and efficient approach to cleaning and processing data
• Analysts or Professionals looking to pick up new tips, tricks and tools to enhance their skills in data cleaning and processing
• All other professionals with a keen interest in developing strong foundations in data analytics
• Prior MS Excel training or experience is helpful, but not necessary

Methodology

A combination of lectures, discussions and interactions, as well as hands-on exercises, are designed to enable participants to acquire those practical skills. This workshop utilises MS Excel for all computer-based exercises.

Logistics Requirements

Participants are required to bring their own laptops with MS Excel (version 2013 or later) to participate in the data exercises

Workshops are designed to be delivered in Face to Face formats

Testimonial

“The trainer was patient in troubleshooting troubles faced by the participants, and it was useful being able to practice what was being taught.”

“Discovered that excel can do wonders! as a beginner to excel. The Trainer was very patient with me. 2 thumbs up!!!”

“A lot of practice given to us and this able to let us understand better.”

“FYTs data analytics workshop was practical and engaging. The trainer shared useful techniques, tips and shared relatable real-life examples. Right after the course, I was able to create an interactive dashboard to share with the team.”

Michael-Lee's Profile

Michael Lee, MBA, BSc Principal Consultant

• An award-winning trainer, Michael leads engagements to help clients build practical analytics capabilities. His clients span multiple functions, sectors and geographies including Insurance, Finance, Education, Government across Asia Pacific.

• In a prior life, Michael was an IT professionals with more than 20 years of experience spanning notable tech companies, functions and geographies. Notably, Michael headed the business analytics & intelligence function for a large technology MNC with >$48B in revenue.

• An ACLP certified trainer with the Civil Service College and CCISG, focused in developing & delivering analytics related curriculum

• Michael has a Bachelor’s degree in Computer Science & Mathematics, and an MBA from the National University of Singapore