Skip to Main Content

Cleaning & Analyzing Data in Excel

This guide complements the Cleaning & Analyzing Data in Excel workshop

Counting Missing Values

Use the =COUNTBLANK(range) function to determine the number of missing values. 

Dealing with Missing Values

Often, researchers will replace missing values with some measure of central tendency (mean, median) if the data set is even distributed. Find and Replace can be used to identify missing values to be replaced.

Find & Replace

Use FIND AND REPLACE to replace unwanted characters or missing values. Select the range (column or row) you want to search within then visit the FIND AND SELECT option on the HOME tab. Select REPLACE and provide the dialog box with the characters/string to search for and the value to replace it with.

 

Practice

Complete the following cleaning step(s) on the practice tab of the workshop_data Excel file.

  • Replace the missing values in estimated resale value after 5 years (usd) with the mean price for that column
  • Replace " out of 5" in the customer review column with no text. Each cell should only have the customer score. 
Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.