Skip to Main Content

Cleaning & Analyzing Data in Excel

This guide complements the Cleaning & Analyzing Data in Excel workshop

Data Analysis Functions

Below are common data analysis functions to use in Excel --

  • =AVERAGE (returns the mean)
  • =MEDIAN (returns the median)
  • =CORREL (calculates the correlation between two sets of data)
  • =UNIQUE (provides a list of all unique values)

Analyze Data Button

As part of a modern update, Excel has included a button to easily ask questions of your data. By selecting the ANALYZE button in the HOME tab.

 

This will open a new window that allows you to ask questions of your data. Remember that your analysis will be based on the current shape of your data.

Practice

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

  • Determine the average price after discount 
  • Determine the correlation strength between price and customer review
  • Determine the unique makes provided in the dataset
Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.