Data Analyst

Chapter 2 — Excel: Data Cleaning & Transformation

📚 Chapter Overview

Bad data leads to bad decisions. In this chapter, you will learn how to use Microsoft Excel to identify errors, remove duplicates, and handle missing values in a raw dataset.

Learning Objectives:

2.1 Text Cleaning Functions

Part 1 — Definition

Data often comes with extra spaces or inconsistent capitalization. TRIM() removes unnecessary spaces, while PROPER() capitalizes the first letter of each word. These ensure your data is uniform and professional.

Part 2 — Formula

=TRIM(A2)
=PROPER(B2)
=UPPER(C2)

Part 3 — Example

Problem: Clean a list of messy names like " jOHN dOE ".

Raw Data: "  jOHN dOE  "
Formula: =PROPER(TRIM(A2))
Result: "John Doe"

Observation: Nesting functions allows you to clean multiple issues in one step.

Part 4 — Video

Video: Coming Soon

2.2 Removing Duplicates & Blanks

Part 1 — Definition

Duplicate records can inflate your totals and skew analysis. Excel's "Remove Duplicates" tool is the fastest way to ensure each record is unique. Similarly, handling blank cells is critical for accurate calculations.

Part 2 — Process

  1. Select your data range.
  2. Go to Data tab → Remove Duplicates.
  3. Select the columns that should define a unique record.

Part 3 — Example

Problem: A sales list has the same Order ID listed twice.

By applying "Remove Duplicates" on the Order ID column, Excel will keep only the first instance and delete the rest, ensuring your total revenue isn't double-counted.

Part 4 — Video

Video: Coming Soon

🏆 Chapter Challenge

Challenge Objective

Clean a raw "Customer List" dataset in Excel.

Requirements

Step-by-Step Process

  1. Use TRIM on the Name column.
  2. Use the Remove Duplicates tool on the Email column.
  3. Use "Find and Replace" or a simple IF formula to handle blanks.

Expected Deliverables