Cleaning Up Messy Excel Data, Part 4: How to Handle Missing Data (with examples)

How many times have you downloaded a Microsoft Excel document for analysis, only to find that there is missing data in your spreadsheet? There are many ways to handle blank cells in your spreadsheet -- I will outline just a few below. If you are working for an organization, make sure you are following their regulations and guidelines about how to handle missing data. If you're unsure about how to handle missing data, check with your organization's data steward and review the compliance guidelines for your industry.

Here are some ways to handle blank cells in Excel:

1. Delete blank cells: If you want to remove the blank cells from your data range, you can select the range and then right-click on the selection. Choose "Delete" and select the option to shift cells up, left, or entire row/column to remove the blanks.

2. Use conditional formatting: You can apply conditional formatting to highlight or format the blank cells differently. This is useful if you want to keep blank cells in your spreadsheet, but still want to flag them for follow-up.

Steps to Highlight Blank Cells:

>?On the Home Tab, select “Conditional Formatting”

>?Select “New Rule”

>?Select a rule type – “Format Only Cells that Contain”: blanks

>?Choose how you want to format the blanks – for example, highlight the whole cell in red

3. Filter the data: Use the filter feature to display or hide the blank cells in your data.

Steps to Filter Out Blank Cells:

>?On the Home Tab, under “Editing”, select “Filter”

>?Add a filter to the columns you want to filter

>?Click on the down arrow for the column you want to filter

> Uncheck all the boxes except for the box “blanks”

>?Click “OK”

>?This will filter the spreadsheet for the rows that have a blank cell in the column you chose.

I often use this when looking at Qualtrics survey outcomes, when a field is not required and I only want to look at fields that the students filled out with usable data. I may choose to not remove the blank cells but to highlight them or filter them out during analysis.?

4. Replace blank cells using "Find and Replace" feature: If you want to replace the blank cells with specific values, you can use the "Find and Replace" feature. Press 'Ctrl+H' to open the "Find and Replace" dialog, leave the "Find what" field blank, enter the desired value in the "Replace with" field, and click on "Replace All" or "Replace" to replace the blanks.

In the example below, Katrina’s department is left blank. If I go to the “Find and Replace” dialogue box, I can leave the “Find” field empty and enter her correct department name in the “Replace with” field. In this case, I filled in her department as “Tech.” Now, Katrina's department will be correctly marked as "Tech".

5. Use formulas: Excel provides several formulas to handle blank cells. For instance, you can use the IF function to check if a cell is blank and return a specific value or perform calculations based on the condition. Another option is the ISBLANK function, which returns TRUE if a cell is blank and FALSE if it contains a value.

Again, these are just a few ways to handle missing data in an Excel spreadsheet. Before taking any of these routes, make sure you are following your company's data policies for handling missing information. If there are any tips I've missed, comment below.