Missing Value Imputation

The Problem with Blank Cells

Before we learn how to fix missing values, it's crucial to understand why they are a problem. A blank cell doesn't mean the value is zero; it means the value is unknown or not applicable. Treating these blanks as zeros can lead to significant errors in your analysis.

For instance, if you are calculating the average price of a list of products and some prices are missing, including them as zeros in your calculation will incorrectly lower the average.

Method 1: Finding and Filling All Blank Cells

The most direct way to handle multiple missing values is to select all of them at once and decide on a single action. Both Excel and Google Sheets have a powerful feature called "Go To Special" that makes this easy.

Once all blank cells are selected, you have several strategic options. The two most common are:

1. Fill with a constant value: Replace blanks with a placeholder like "N/A" (Not Applicable), "TBD" (To Be Determined), or "Missing." This makes it clear that the data is intentionally absent.
2. Fill with the value from the cell above: This is useful for poorly formatted reports where a value in one row is meant to apply to several subsequent blank rows.

The following video demonstrates both of these techniques in action.

Method 2: Handling Missing Data Within Formulas

Sometimes, you don't want to change the original data. Instead, you want your formulas to be smart enough to ignore blank cells and avoid producing errors.

Using IF and ISBLANK

A clean and robust way to write formulas is to first check if a cell is empty. The ISBLANK function returns TRUE if a cell is empty and FALSE otherwise. You can combine this with an IF statement to control your calculation.

The logic is: IF( a cell is blank, return a blank, otherwise, perform the calculation ).

Using IFERROR

Another common scenario is when a calculation results in an error because it involves a blank cell (e.g., trying to multiply a number by an empty cell). This often produces an ugly #VALUE! error. The IFERROR function provides an elegant solution.

The logic is: IFERROR( attempt this calculation, if it results in an error, do this instead ).

Method 3: Standardizing Non-Blank Placeholders

Finally, you may encounter datasets where missing values are not blank but are represented by inconsistent text like "null," "n/a," "missing," or even "?". In this case, your goal is to standardize them. The Find and Replace tool (Ctrl + H), which you've used before, is perfect for this.

You can search for each variation (e.g., "null") and replace it with a standard placeholder (like "N/A") or with a truly blank cell by leaving the "Replace with" field empty.

Conclusion

This lesson concludes our module on Data Cleaning and Preparation. You are now equipped with a comprehensive set of skills to transform raw, messy data into a clean, structured, and reliable asset—a critical capability for any data professional.

Here are your key takeaways for handling missing values:

• Acknowledge the Issue: Blank cells are not zeros; they represent unknown information and must be handled carefully to avoid corrupting your analysis.
• Bulk Actions: Use Go To Special > Blanks to quickly select all empty cells and either fill them with a placeholder (like "N/A") or propagate values from rows above.
• Robust Formulas: Use IF(ISBLANK(...)) to prevent formulas from calculating on empty cells, or use IFERROR(...) to gracefully manage errors that arise from missing data.
• Standardize Placeholders: Use Find and Replace to unify inconsistent textual representations of missing data into a single, standard format or to remove them entirely.

In our next module, Market Research Application, we will shift gears. You will start applying your data collection and cleaning skills to practical research tasks, beginning with how to identify relevant online sources for an industry research project.