Find and Fix Data Issues

As data analysts, our insights are only as good as the data we rely on. Incomplete or messy data can quietly undermine our work, leading to faulty conclusions or misinformed decisions. That’s why detecting and fixing data quality issues is a crucial early step in any analysis.

Spotting the gaps

First, we need to locate where our data is missing. Google Sheets provides us with a couple of ways to do this.

1. The ISBLANK() function

Google Sheets makes it easy to detect missing data with the ISBLANK() function. This function checks whether a cell is empty and returns TRUE if it is, and FALSE otherwise. Here’s the syntax for ISBLANK:

• cell_reference: This is the cell we want to check for blankness. It could be a single cell like D2, E5, etc.

Suppose we have a sales dataset where the column C contains Sales (USD) values. Our goal is to identify rows where this sales information is missing.

1. Create a helper column (e.g., BLANK?) next to the Date.

2. In the first cell of this helper column (e.g., F2), type =ISBLANK(C2) to check for blank Sales(USD).

3. When we drag the small blue circle at the bottom-right corner of the formula cell, Google Sheets automatically updates the cell references for each row, checking C2, then C3, C4, and so on. This simple action quickly applies the logic down the column, flagging every row where column C is blank.

We can adjust the formula to check other columns too, like =ISBLANK(B2) for blank regions or =ISBLANK(D2) for blank units sold. This method is highly efficient for systematically identifying every single empty cell, no matter how large our dataset is.

2. Filter for empty cells

If we want to find blank cells without using formulas, we can apply a filter directly to the data. This lets us quickly display only the rows where values are missing in a specific column.

1. Go to “Data” > “Create a filter.”

2. Click the filter icon in the column we want to check (e.g., Sales).

3. Choose “Filter by condition” > “Is empty.”

Only the rows with blank cells in that column will remain visible. This method is fast and especially useful for spotting and fixing blanks directly. To reset the view, just remove the filter or change the condition. Filtering is perfect for visual reviews, even in larger datasets.

Filling in missing data

Once blanks are identified, the next crucial step is to decide whether and how to fill them. The approach depends on what makes logical sense for our data.

• Manual entry: If we know the exact correct value for a blank cell, we simply type it in directly. For example, if we know the missing Sales (USD) value for the Mouse West entry is 750, we can ...