2-Day Excel Data Cleansing and Building Techniques Course

Course Overview

This course is created, owned, and trained by its original creator, Mr. Alaster Leong. Learn from the original creator.

Note: This course required Microsoft Power Query to be installed in Excel. Power Query is already included in Excel version 2019 and above or Office 365 and no further action is required. Click here to Power Query availability.

Dealing with Big Data and Data Analysis? Make sure your data is clean. In today’s business world, incorrect or inconsistent data can be costly. When the data is clean, not only does it provide substantial growth to the business, it also helps to maximise employees’ efficiency and improve their response rates.

What is Excel Data Cleansing or Data Cleaning then? It is a process that involves detecting and correcting or removing corrupt, inaccurate, incomplete and irrelevant records from a record set, table or database in Excel.

Do you face the following challenges with your data?

Extra space(s) in the front, back or even within the data
All data are in upper case or in lower case
Position of the columns are not correct
Lots of holes (empty cells) in the data
Extra labels within the data list
Merged cells within the data list
Non-printable characters within the data
Data are stored in horizontal format (standard is vertical)
Date field become text field (cannot be calculated)
Person names are stored in one field instead of splitting into First and Last name
Duplicate records, etc

Apart from the above, there are many more challenges people face daily with their data sets. Regardless of the reporting tools you use to generate the unclean data (e.g. Power BI, Tableau, Power Pivot or Pivot Table), the generated reports will come out to be inaccurate.

Data cleansing on Excel is not just recommended, but a required process. Data is the core business asset that needs essential management for every business enterprise to generate returns. Data cleaning plays a pivotal role during decision-making process or data analysis. Incorrect or inconsistent data leads to poor business decisions and inferior customer relationship management. It can land your business in big trouble!

Data sets have the potential to grow inaccurate over time in many ways, from human error, inconsistent data styling to aging information. It is vital to keep your data in a uniform state with regular data cleaning for building quality data analytics and business intelligence solutions. This will improve the decision-making process, streamline business practices and help you efficiently use your time well.

In this Excel Data Cleansing and Building Techniques course, you will be guided on the process of preparing data for accuracy and to build data not found in the existing data before using it as an analytics input. It involves the following:

Analysing the data to detect any problems for reporting
Clean the data if problems are detected: remove incorrect data, update incomplete information, clear or reformat the improper formatting, remove extra spaces and many more
Reorganizing and shaping the data
Building data that not found in the existing data

Objectives

Upon completion of the course, learners will be able to:

Understand how to analyse and detect problems
Utilise a set of functions that are required for data cleansing and building
Apply the skills and techniques for data cleansing on Excel
Use Microsoft Power Query to clean and build data
Get hands-on exercises to practice on the skills and techniques learned

Who Should Attend

This course is for anyone who needs to work with data.

Methodology

This is a hands-on practical course. Trainer will walk through the topic step-by-step. Learners will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.

Prerequisites

Intermediate Excel skills
Excel Power Query is available on your laptop.

Things To Bring

A Windows operating system laptop with Microsoft Excel version 2019 or higher or Office 365 installed with Power Query available (personal laptop is preferred as some company laptops may have security restrictions).
Laptop power adaptor and a USB mouse.

Course Information

Fee

SGD 810.00 (Nett)

Absentees will be charged the full course fees

Time

9:00am to 5:00pm (14 hours)

Mode

Classroom @ Hotel

(Venue may be subject to changes)

Dates

20-21 Jun 2024
05-06 Aug 2024
24-25 Oct 2024
05-06 Dec 2024

Closed Class

Important Note

Closed Class

Important Note

Why Learn With Us?

Course Outline

1. Functions used in Data Cleansing & Building

Date Functions
Text Functions
Lookup Functions

2. Cleaning & Reorganising Data

3. Cleansing & Building Data with Power Query

Splitting Data
From Report Format to Data
Combine Data
Merge Data
Types of Join
Connect to a Folder and Auto Cleansing
Unpivot a Pivot Data

Our Dedicated Lead IT Trainer - Mr. Alaster Leong

This course is created, owned and trained by Mr. Alaster Leong. Learn from the original creator of this course.

Alaster has been training adult learners in IT in both the private and public sectors since 1986. Alaster’s career before training centred around IT for over 12 years, as he held various roles within software development. He has earned himself a Microsoft Office Specialist (MOS) certification and he is an existing Trainer approved by People’s Association (PA). He was also a former Associate Trainer with the Civil Service College.

He specialises on training Microsoft Applications and Adobe Photoshop. Based on his vast experience and keeping abreast with the emerging trends, tools and technologies with the IT industry, especially software, he has extensively involved in developing several course outlines and course materials and conducting training to equip learners with skills in-demand or likely to be most important in the jobs of the future work.

Alaster constantly explore innovative and effective ways in imparting knowledge and engaging his learners at the same time. He also focuses his training on providing practical solutions to real-life problems, especially Excel. He is more than willing to help overcome various problems faced by his learners that arise in their workplace in applying their newly-acquired knowledge and skills any time after the course. He is well-known for his dedication, commitment and passion to training that makes him well-liked and appreciated by his learners.