Invalid Values in Excel

In the previous segment, you learnt how to standardise values. When standardising values, you do not really pay attention to the validity of the actual values of the variables. This is what we will discuss now as you learn how to fix invalid values.

A data set can contain invalid values in various forms. Some of the values could be truly invalid, e.g., a string 'tr8ml' in a column containing mobile numbers would make no sense and, hence, should be removed. Similarly, a height of 11 feet would be an invalid value in a set containing heights of children.

On the other hand, some invalid values can be corrected. For example, a numeric value with a data type of string could be converted to its original numeric type. 

Let's gain more insights into fixing invalid values.  

If you have an invalid value problem and you do not know what accurate values could replace the invalid values, it is recommended that you treat these values as missing. For example, in the case of a string 'tr8ml' in a Contact column, it is recommended to remove the invalid value and treat it as a missing value.

Let’s summarise what you learnt about fixing invalid values. You could use this as a checklist for future data cleaning exercises.

• Convert incorrect data types: Correct the incorrect data types for ease of analysis. For example, if the numeric values are stored as strings, it would not be possible to calculate metrics such as mean, median, etc. Some of the common data type corrections are: string to number: '12,300' to '12300'; string to date: "2013-Aug" to “2013/08”; number to string: “PIN Code 110001” to "110001"; etc.
• Correct values that go beyond range: If some of the values are beyond the logical range, e.g., temperature less than -273° C (0° K), you would need to correct them as required. A close look would help you check if there is scope for correction or the value needs to be removed.
• Correct values that are not in the list: Remove values that don’t belong to a list. For example, in a data set containing blood groups of individuals, strings 'E' or 'F' are invalid values and can be removed.
• Correct wrong structure: Values that don’t follow a defined structure can be removed. For example, in a data set containing PIN codes of Indian cities, a PIN code of 12 digits would be an invalid value and needs to be removed. Similarly, a phone number of 12 digits would be an invalid value.
• Validate internal rules: If there are internal rules, such as the date of a product’s delivery must definitely be after the date of the order, they should be correct and consistent.

In the next lecture, you will learn how to filter data for the ease of analysis.