Excel Data Cleaning Fundamentals

Course Curriculum

Chapter 1: Introduction Data Cleaning

Lecture 1: Introduction

Lecture 2: The follow through exercise

Lecture 3: What are the attributes of a good Data-set in Excel?

Chapter 2: Data Cleaning- Text Values

Lecture 1: Introduction to Text Data Type Data Cleaning

Chapter 3: Text Values Data Cleaning: First Name Column Values

Lecture 1: First Name Column Values : The CODE() and ASCII

Lecture 2: First Name Column Values : Using the CODE() to Detect Errors

Lecture 3: Applying the CLEAN() function to clean non-printable characters

Lecture 4: Applying the TRIM() function to clean Leading and Trailing Spaces

Lecture 5: The SUBSTITUTE(), CHAR() and the TRIM() functions to clean Non-Breaking Spaces

Lecture 6: The PROPER() function to format Text Values

Chapter 4: Text Values Data Cleaning Exercise: Last Name Column Values

Lecture 1: Introduction to the Last Name Data Cleaning Exercise

Lecture 2: CODE() function to detect errors in Last Name column values

Lecture 3: Applying the =PROPER(CLEAN(SUBSTITUTE(CHAR()))) combination for data cleaning

Chapter 5: Text Values Data Cleaning Exercise: Address Column Values

Lecture 1: Introduction :The IF() function in the data cleaning exercise

Lecture 2: CODE() Try it yourself Exercise: Detecting Errors in the Address Column Values

Lecture 3: Combining the IF() with OR(),TRIM(),CLEAN(),SUBSTITUTE() and PROPER() functions

Lecture 4: Removing Non-Printable Characters between Text Strings using the Function Combo

Chapter 6: Text Values Data Cleaning Exercise: Passport Column Values

Lecture 1: The CONCATENATE() Function

Chapter 7: Text Values Data Cleaning Exercise: Mobile Phone Column Values

Lecture 1: Introduction to exercise and try it yourself exercise: RIGHT() and LEN()

Lecture 2: Applying the LEN() Function to check for errors in the Phone column

Lecture 3: Combination RIGHT(),LEN() ,IF(IF()) and other functions to correct phone values

Chapter 8: Text Values Data Cleaning Exercise: Currency Column Values

Lecture 1: =TRIM(CLEAN(SUBSTITUTE(CHAR()))) to fix Text Errors

Chapter 9: Text Values Data Cleaning Exercise: Country Column Values

Lecture 1: =IF(OR()) combination to fix column values

Chapter 10: Text Values Data Cleaning Exercise: Airport Column Values

Lecture 1: VLOOKUP() and IFERROR() in Data Cleaning

Lecture 2: =IFERROR(VLOOKUP()) combination in fixing the column value Errors

Chapter 11: Date Values: Data Cleaning Exercise

Lecture 1: Date of Departure Column Values : ISNUMBER() and "TEXT TO COLUMN"

Lecture 2: Date of Departure Column Values : DATE() function

Lecture 3: Date of Birth Column Values: ISNUMBER()

Chapter 12: Number Data Values: Data Cleaning Exercise

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

Chapter 13: Removing Duplicate Records

Lecture 1: Conditional Formatting and Remove Duplicates Features of Excel

Chapter 14: Data Normalization

Lecture 1: Introduction to the Exercise

Lecture 2: Add Age Column: YEAR() and TODAY() Combination

Lecture 3: Add Month of Departure Column: TEXT() Function

Chapter 15: Practice Exercises: Sample Dataset

Lecture 1: Practice Exercise #1: HR Sample Dataset 1

Lecture 2: Practice Exercise #2: HR Sample Dataset 2

Lecture 3: Practice Exercise #3: Sales Sample Dataset

Chapter 16: Review and Conclusion

Lecture 1: What did you Learn?