Ensure your imported raw data is free from errors
We hear a lot about Data Analytics but data cleaning which is an important preparation of data before performing the analysis is often overlooked until errors occur. We need a dataset that is free from data errors to be incorporated into our work reports. However, often times, the raw data files presented to us could contain errors such as duplicates, wrong data types, wrong letter case, unnecessary spaces between data just to name a few. Any of these would render your analysis and reports inaccurate and can be damaging to your company.
This one-day course will equip you on using relevant Excel functions and formulas to find and eliminate the more common errors found in raw datasets. The organized and consolidated data can then be used to generate useful and accurate information for your company.
Who is this course for?
Learners who work with imported raw data and have attended our Microsoft Excel 2016 Basic to Intermediate module or must be well versed in the topics covered in that module.
Public Sector - Div. 1, 2 & 3
Private Sector - Applicable to All
Course Outline
- Import Data into Excel
You will learn how to identify and decide on the appropriate format to import the raw data into Excel.
- Remove Unwanted Spaces in Data
Having additional spaces between words can be an issue when doing comparison and matching of data. You can quickly and effectively remove those unwanted spaces.
- Fill Blank or Empty Cells
Empty values in cells can cause issues to calculations and retrieval of relevant data. These blank cells can be quickly filled with your required value.
- Fix Errors in Cell Format
Having cells that contain text (alphanumeric) values instead of a numeric value can be disastrous and result in errors. You will learn how to safely convert such values.
- Extract Dates and Convert Dates
Dates can be entered in different formats like DD/MM/YYYY or MM/DD/YYYY which will cause errors in your reports, ensuring they are all aligned in the same format is essential. At times there might be a need to obtain portions of the date like the month or year.
- Text Data Order and Format
Entered values in the wrong format or placement can often cause or pose challenges when handling certain tasks. These issues like sentences entered entirely in uppercase or lowercase, surnames or family names of different races differ in positions, can also cause problems. Being able to address these issues are important.
- Replace or Substitute Text
The need to replace or substitute a text within a sentence because of changes to for example, the department name or a reference code can be tedious when done manually. In Excel this can be done easily.
- Duplicate Records
Duplicate record is a frequently occurring problem in data. Learn how to manage or remove them.
- Highlight Errors
Data files can contain errors such as Division error, Value error, etc. To have Excel highlight them so that you can decide what and how to correct them quickly.
- Parse Data
Imported raw data can come with each row of data all in stored into a single column cell which is not usable for analysis until they are properly separated into their respective columns.
Questions to Ask Yourself
I am a very experienced user of an earlier version of Microsoft Excel; can I take up this course?
Do note that this course is a focused course on data cleaning, time will not be spent teaching or on the revision of Excel features from our basic to Intermediate, and Advanced course syllabus. You are expected to be familiar with the features covered in those module.
