3.6 Identifying missing values

Missing values are present in virtually all databases. Like extreme values, the presence of missing values can lead to misleading analyses.

It is possible to identify missing values in Excel via several methods:

• Filter empty cells: place the cursor on the column header row where you want to apply the filter and then under the Data tab > click the Filter button. Click the small arrow that appears on the column header where you want to apply the filter, uncheck the Select all filter and then go to the very bottom of the list. If one or more observations are missing, Excel suggest the ‘(blanks)’ filter.

  Warning: Sometimes missing observations are not empty but are identified with #N/A or 0 depending on the data collection criteria.

• Compare 2 columns to highlight missing values: the following formula identifies missing values within a variable: =IF(C2=””;”Missing value”;””)

Once you have identified the missing values, you can either:

• replace with a zero, “no”, “not applicable”,
• leave the cell empty but keep the line (the subject will be included in the analysis for the other variables - those not empty),
• delete the whole line when there is a missing value.

These actions have consequences on the sample size and are likely to bias the results.