Business Data Analytics & Advanced Excel · Beginner level

Beginner level · Module 1: Clean Data for Business Analysis · Lesson 3 of 3

Cleaning Business Data

Find and fix common data problems with Excel tools, and document every change.

In this lesson you will

  • Apply a cleaning checklist
  • Use Excel's cleaning tools and keep a cleaning log

Key concepts

duplicatesinconsistent categoriestext dates and numberscleaning log

The lesson

Check every data set for duplicates, inconsistent names ('Juba', 'JUBA', 'Juba '), numbers or dates stored as text, impossible values and missing entries.

Excel's tools help: Remove Duplicates, TRIM and PROPER, Find and Replace, Text to Columns, Flash Fill and filters to find blanks and outliers.

Never overwrite the original: keep it on its own sheet, clean a copy and record every change in a cleaning log — what, why and how many rows.

Step by step

  1. Run the cleaning checklist on the quarter's data.
  2. Fix each problem with the right tool.
  3. Record each fix in the log.
  4. Compare row counts before and after.
Step-by-step

Cleaning a quarter of sales records

A checklist that turns a messy export into data you can analyse — with every change recorded.

  1. Protect the original

    Copy the raw export to a sheet named Raw and never edit it; work on a copy.

  2. Fix the structure

    Delete title rows, subtotal rows and blank rows; keep one header row; unmerge cells.

  3. Remove duplicates

    Use Remove Duplicates on the receipt-number column and record how many rows were removed.

  4. Standardise categories

    TRIM and PROPER branch and product names; replace variants such as 'JUBA' and 'Juba Town' with the agreed name.

  5. Convert types

    Turn text dates into real dates and text numbers into numbers; check that amounts sum.

  6. Check and log

    Filter for blanks, negatives and extreme values; record every fix — what, why, how many rows — in the cleaning log.

Result

A tidy table that PivotTables and Power BI can use, and a log that lets anyone repeat or check the cleaning.

Practice activity

Complete the cleaning activity and its log.

Expected result: A clean data set with a log that explains every change.

Check your understanding

  1. Why keep the original data untouched?

    Show answer

    So changes can be checked and repeated.

  2. Name three common data problems.

    Show answer

    For example duplicates, inconsistent names and dates stored as text.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.