Excel Data Cleaning Fundamentals

How to detect and fix errors in datasets imported into Excel for data analysis

4.79 (40 reviews)
Udemy
platform
English
language
Microsoft
category
instructor
541
students
2 hours
content
Sep 2022
last update
FREE
regular price

What you will learn

How to detect and fix errors in datasets imported into Excel for analysis

How to categorize and fix errors in the different data types in excel using basic to advanced excel functions

Different concepts and techniques used in excel data cleaning

Description

Analyst from time to time may need to analyze small datasets from larger datasets that may exist in other applications or other forms of storage for very quick results.

Excel has proven to be a very reliable support analytical tool in this regard , it is easy to learn and work with, it provides the analyst with reliable and easy ways of  importing datasets into excel for quick analysis.

The main challenge that analysts  face  having imported datasets into excel from different applications is the issue of data inconsistencies, anomalies and other errors.

The course is designed to provide the analysts with the necessary skill set to overcome this problem, by providing a step by step instruction using a follow along exercise and also several case study exercise and quizzes, on how to use basic to advanced excel functions , concepts and techniques in a fast and efficient way to detect and fix errors that result from datasets imported from other sources into excel for analysis.

The techniques in this course are simple but yet very effective in excel data cleaning , and will not require the use of macros or any excel add on tools

Content

Introduction

Introduction
What is the exercise about?
What are the attributes of a good Data-set in Excel?

Data Cleaning- Text Values

Introduction to Text Data Type Data Cleaning

Text Values Data Cleaning: First Name Column Values

First Name Column Values : The CODE() and ASCII
First Name Column Values : Using the CODE() to Detect Errors
Applying the CLEAN() function to clean non-printable characters
Applying the TRIM() function to clean Leading and Trailing Spaces
The SUBSTITUTE(), CHAR() and the TRIM() functions to clean Non-Breaking Spaces
The PROPER() function to format Text Values

Text Values Data Cleaning Exercise: Last Name Column Values

Introduction to the Last Name Data Cleaning Exercise
CODE() function to detect errors in Last Name column values
Applying the =PROPER(CLEAN(SUBSTITUTE(CHAR()))) combination for data cleaning

Text Values Data Cleaning Exercise: Address Column Values

Introduction :The IF() function in the data cleaning exercise
CODE() Try it yourself Exercise: Detecting Errors in the Address Column Values
Combining the IF() with OR(),TRIM(),CLEAN(),SUBSTITUTE() and PROPER() functions
Removing Non-Printable Characters between Text Strings using the Function Combo

Text Values Data Cleaning Exercise: Passport Column Values

The CONCATENATE() Function

Text Values Data Cleaning Exercise: Mobile Phone Column Values

Introduction to exercise and try it yourself exercise: RIGHT() and LEN()
Applying the LEN() Function to check for errors in the Phone column
Combination RIGHT(),LEN() ,IF(IF()) and other functions to correct phone values

Text Values Data Cleaning Exercise: Currency Column Values

=TRIM(CLEAN(SUBSTITUTE(CHAR()))) to fix Text Errors

Text Values Data Cleaning Exercise: Country Column Values

=IF(OR()) combination to fix column values

Text Values Data Cleaning Exercise: Airport Column Values

VLOOKUP() and IFERROR() in Data Cleaning
=IFERROR(VLOOKUP()) combination in fixing the column value Errors

Date Values: Data Cleaning Exercise

Date of Departure Column Values : ISNUMBER() and "TEXT TO COLUMN"
Date of Departure Column Values : DATE() function
Date of Birth Column Values: ISNUMBER()

Number Data Values: Data Cleaning Exercise

Amount Column Values: VALUE() and TEXT() Functions

Removing Duplicate Records

Conditional Formatting and Remove Duplicates Features of Excel

Data Normalization

Introduction to the Exercise
Add Age Column: YEAR() and TODAY() Combination
Add Month of Departure Column: TEXT() Function

Review and Conclusion

What did you Learn?

Screenshots

Excel Data Cleaning Fundamentals - Screenshot_01Excel Data Cleaning Fundamentals - Screenshot_02Excel Data Cleaning Fundamentals - Screenshot_03Excel Data Cleaning Fundamentals - Screenshot_04

Reviews

Ruben
October 28, 2022
I have seen some biases in the proposed exercise. I have asked the professor and, after two months!!, I have not gotten an answer. Therefore, I consider that the quality of the course is very low because the data cleaning method he explains will only be useful for you if all the assumptions made by the professor are strictly fulfilled (which is almost impossible in a real life data application). EDITED: Do not worry for me. I have got very clear what a data cleaning is and how it works in real life. Then if you answer professional questions (for second time) I will ask you the next: 1. the non-printable characters at the beginning and at the end of the text string are of the same type never different type. So you can apply the SUBSTITUTE formula and fix all of them with this formula. But, what do you do if the non-printable character at the beginning of the text string and the non-printable character in the middle of the text string are different? 2. The other condition that your example always meets is that always there is: if there is a non-printable character in the middle of the text string there will be also a non-printable character at the beginning of that text string. However, what happens if we only have one non-printable character into our text string and it is located in the middle of the text string?
Kabir
April 30, 2020
The course was really awesome and very easy to grasp. The facilitator has done a marvelous job and i wish he will add more exercise sheets to the existing one. Kudos!

Charts

Price

Excel Data Cleaning Fundamentals - Price chart

Rating

Excel Data Cleaning Fundamentals - Ratings chart

Enrollment distribution

Excel Data Cleaning Fundamentals - Distribution chart

Related Topics

2015578
udemy ID
11/8/2018
course created date
2/29/2020
course indexed date
Bot
course submited by