Advanced Learning Singapore

Download Year 2024 Training Calendar

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

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

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

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.

  • Intermediate Excel skills
  • Excel Power Query is available on your laptop. 
  • 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

05-06 Aug 2024
24-25 Oct 2024
05-06 Dec 2024

Why Learn With Us?

Course Outline

  • Date Functions
  • Text Functions
  • Lookup Functions
  • 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

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.

Share This Course

Scroll to Top