2-Day Excel Data Cleansing And
Building Techniques Course In
It is vital to keep your dirty data clean to build quality data for analytics and drive better decision-making
All our courses are not subsidised and non-claimable using SkillsFuture credit
Excel Data Cleansing And Building Techniques Course Overview
Learn from the original creator of this course – Mr. Alaster Leong.
Dealing with Big Data and Data Analysis? Make sure your data is clean.
What is Excel Data Cleansing or Data Cleaning? 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 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.
False conclusions are derived from incorrect or inconsistent data. Your ability to clean the data highly impacts the quality of results. The longer you allow your Excel spreadsheet to run without proper excel data cleaning, the longer it will take to clean them up.
This Excel Data Cleansing and Building Techniques course is created and conducted by ALS IT lead trainer Mr Alaster Leong. 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
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.
At the end of the course, learners are 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.
This is a hands-on practical course. Our trainer will walk through each of the topics step-by-step. Learners will also be provided with exercise files on each topic to effectively apply what has been taught. A short Q & A session will be available after each topic.
- Intermediate Excel skills
- Software Requirement: Microsoft Excel version 2019 or higher or Office 365 installed with Power Query available
Things To Bring
1. Functions Used in Data Cleansing and Building
- Date Functions
- Text Functions
- Lookup Functions
3. Cleansing and Building Data with Power Query
- Splitting data
- From report format to data
- Combine data
- Merge data
- Types of Join
- Connect to a folder & auto cleansing
- Unpivot a pivot data
Our Dedicated Trainer
Learn from the original creator of this course.
Having trained adult learners specialising in IT from both public and private sectors since 1986, Alaster has a wealth of experience in training. Before moving into the training sector, Alaster worked in IT for over 12 years – boasting a wide range of roles held within software development. Prior to being approved by the People’s Association (PA), Alaster was an Associate Trainer with the Civil Service College. He also holds Microsoft Office Specialist (MOS) certification and specialises in training Microsoft Applications and Adobe Photoshop.
With his vast experience and commitment to keeping abreast with emerging trends, tools and technologies within the IT and software industry, Alaster is extensively involved in developing course outlines and materials that equip learners with up-and-coming skills in demand for the future of work.
In constantly exploring innovative and effective methods of imparting knowledge, Alaster is able to engage his learners in the courses he conducts. A large part of his trainings are committed to providing practical solutions to real-life problems, especially in the Excel courses he conducts. An approachable and engaging trainer, Alaster is always willing to aid his learners in overcoming various problems using the skills acquired even after courses. Well-known and liked for his dedication, commitment and passion for training, Alaster is appreciated by his learners.
Aside from the Excel Data Cleansing Course, Alaster is also our trainer for a myriad of other Microsoft specialised courses we conduct here in Singapore.
All our courses are not subsidised and non claimable using SkillsFuture credit.
For organisations applying with more than 2 learners, please click here to download the registration form.
All registration forms should be submitted at least 2 weeks before course commencement. Many courses have pre-requisites, intended to foster a learner’s success in the course. Be sure to check the necessary pre-requisites before registering.
This course is not subsidised and non claimable using SkillsFuture credit.
No laptop will be provided. Bring Your Own Laptop Course.
Call us for a group rebate when you register in a group of 8 or more.
Absentees will be charged the full course fee.
2 days, 9:00am – 5:00pm
(14 hrs, exclude lunch break)
A closed class can be requested if you have a group of at least 6 or more registered for the same course and date at a preferred date, subject to trainer availability.
Closed class is not customised class.
To be confirmed one (1) week prior to the course start date.
For Company-Sponsored learners: Click here to download registration form.
For Self-Sponsored learners: Click here to download registration form.
You can also register online here.
ALS reserves the right to reschedule, postpone or cancel any course, change the trainer and venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control.
More Original Courses Developed & Trained By Alaster
Create Digital Fill-In Form In Word And Auto Import Form Data From Excel Course
Data Analysis Using Dynamic And Interactive Excel Dashboard Course
Data Analysis With Excel Power Query, Data Model And Power Pivot Course
Excel 100 Tips And Techniques Course (Exclusive)
Excel 365 Dynamic Arrays And Power Functions (New & Exclusive)
Excel Advanced Formulas And Functions Course
Excel Automation With VBA Series 1 – Fundamentals Course
Excel Pivot Table In-Depth Course
Excel Power Query – Automating Data Preparation (Exclusive)
Power BI Desktop Series 1 – Data Model, DAX And Dashboard Visualization Course (Exclusive)
Power BI Desktop Series 2 – Advanced DAX Formulas Course (New & Exclusive)
PowerPoint Tips And Techniques Course