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
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
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?
