Introduction Data Cleaning
  • Introduction
  • The follow through exercise
  • 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
Practice Exercises: Sample Dataset
  • Practice Exercise #1: HR Sample Dataset 1
  • Practice Exercise #2: HR Sample Dataset 2
  • Practice Exercise #3: Sales Sample Dataset
Review and Conclusion
  • What did you Learn?