Webbo3 Data Analytics · Cohort 2 · Excel Track · Pod Task, Week 2
Pod Task Week 2: Clean the Verdant Foods Data
Your pod takes a raw file with four deliberate problems and turns it into data you could defend in front of a client, with a record of every change.
By the end of this class you can
- Remove duplicate transactions and prove the count
- Turn every date into a real date in one format
- Make every Category one of four exact values
- Flag missing quantities without deleting a single real transaction
- Write a Cleaning Log someone else could follow step by step
Everything this week has been practice for this. You removed duplicates and flagged blanks on TechPlug in Live 1, cleaned text in Live 2, and fixed Verdant's categories and dates in Virtual 2. Now your pod finishes the job and writes down exactly what it did.
Verdant Foods Nigeria: the pod project file
Download everything as one zip file (verdant-foods-pod-dataset.zip)
- VerdantFoods_Pod_Dataset.csv: 470 rows, one sheet, 10 columns, with the four deliberate problems
The Brief
Pod Task Week 2: Clean the Verdant Foods Data
- Remove the 15 duplicate rows with Data > Remove Duplicates. Log the count.
- Fix the 10 dates in the wrong format. Every date must be a real date shown as YYYY-MM-DD.
- Fix the 30 inconsistent Category values with TRIM + PROPER (or Find and Replace). No spaces left before or after.
- For the 20 blank Quantity cells, add a column called Quantity_Flag that says Missing for those rows. Do not delete the rows.
- Record every fix in the Cleaning Log tab: what was fixed, which column, how many rows, how.
- Keep the cleaned data in a tab called Cleaned Data. Leave the Raw Data tab exactly as it was downloaded.
Hand in: The Cleaned Data tab and a completed Cleaning Log tab in your pod's shared workbook, by the deadline your tutor gives.
Skills tested: TRIM, PROPER, Text to Columns, Remove Duplicates, Filter, and documenting your work
The Workbook at the End of Week 2
| Tab | What it holds | Rule |
|---|---|---|
| Raw Data | The file exactly as downloaded: 470 rows | Never edited. It is how you start again |
| Cleaning Log | One row per fix | Written as you go, not at the end from memory |
| Cleaned Data | 455 transactions, clean, with Quantity_Flag | Every change here appears in the log |
Split the Work, Share the Understanding
A pod of four might split it like this, but every member must be able to explain every step, because your tutor may ask any of you:
| Member | Owns | Then checks |
|---|---|---|
| 1 | Duplicates and the Cleaning Log tab | Member 2's dates |
| 2 | Dates | Member 3's categories |
| 3 | Category | Member 4's flags |
| 4 | Quantity_Flag and the final checks | Member 1's log against the data |
Steps 1 to 4 all change the Cleaned Data tab. If two people sort or delete rows at the same moment, work is lost. Take turns, say in the pod chat when you start and finish, and check the row count after each step.
Step 1: Remove the Duplicates
Duplicates go first, so every count after this step is a count of real transactions. Here is one of them: transaction VF-0012 appears twice, every column the same.
Remove the duplicates
If your message says anything else, press Ctrl + Z straight away. The most common reason is a column unticked by mistake, or a helper column from Virtual 2 still sitting next to the data with different values in it. Delete helper columns before this step.
Step 2: The Dates
You did this in Week 2, Virtual 2, Part 3. Check it now: =ISTEXT(B2) filled down should give TRUE on no rows, and the whole column should be formatted yyyy-mm-dd. If your laptop read the day-first dates itself, use the Notepad check in that lesson to find the 10 Transaction_IDs for the log.
Check figure: every date is in 2025, from 2025-01-01 to 2025-12-31. Sort the column both ways to confirm; a date outside 2025 means one was read the wrong way round.
Step 3: Category
Also done in Virtual 2, Part 2. Check: the filter list on Category shows exactly four entries.
| Category | Transactions after cleaning |
|---|---|
| Beverages | 126 |
| Cereals | 67 |
| Condiments | 153 |
| Snacks | 109 |
| Total | 455 |
Filter each category and compare the status bar with this table. If one is short, there is a spelling you have not caught.
Step 4: Flag the Missing Quantities
Quantity is column G. The flag goes in the first empty column, K, using the pattern from Week 2, Live 1:
Filter Quantity_Flag for Missing: the status bar must say 20 of 455 records found. These transactions happened; you just do not know how many cartons were sold. In Week 3 they will be left out of revenue, and the flag is how you will say so honestly.
Either is fine for Quantity_Flag. Keeping the formula means the flag updates itself if someone later fills in a missing quantity, which is a small advantage. Whatever you choose, write it in the log.
The Cleaning Log
Create a tab called Cleaning Log between Raw Data and Cleaned Data. Each fix gets one row. Here is the layout, with the first row filled in as an example; your pod writes the rest.
A good log entry lets a stranger repeat your work exactly and get the same result. "Fixed categories" is not enough. "Category, 30 cells, wrong case and extra spaces, =TRIM(PROPER(F2)) in a helper column then Paste Values, filter list now shows 4 values" is.
Final Checks Before You Hand In
| Check | How | Must show |
|---|---|---|
| Row count | Select column A, read the status bar Count | 456 (the header plus 455 transactions) |
| No duplicates left | Run Remove Duplicates again, then Ctrl + Z | "No duplicate values found." |
| Dates | =ISTEXT on the Date column | FALSE on every row; yyyy-mm-dd format |
| Categories | Filter list on Category | 4 entries |
| Quantity_Flag | Filter for Missing | 20 of 455 records found |
| Raw Data | Select column A on Raw Data | Count 471: untouched |
| Cleaning Log | Read it aloud to your pod | Four rows, each with a count and a method |
Deleting the blank-Quantity rows instead of flagging them. Running Remove Duplicates with a helper column still attached. Cleaning on the Raw Data tab. Running PROPER on Product_Name or Sales_Rep "while you are at it" and not logging it. Writing the log from memory at the end.
Checkpoint
Q1. Why are duplicates removed before anything else?
So every later count is a count of real transactions, not of copies.
Q2. Why flag the blank quantities instead of deleting the rows?
They are real transactions. Deleting them would lose them from counts by region, rep and customer type; the flag keeps them visible and lets Week 3 leave them out of revenue honestly.
Q3. A pod mate says the log can be written on Friday. What do you say?
Write it as you go. From memory you lose the counts and the exact method, which are the whole point.
Tick Before You Move On
- ☐ Raw Data is untouched
- ☐ Remove Duplicates said 15 removed and 455 remain
- ☐ Every date is a real date shown as yyyy-mm-dd
- ☐ Category has exactly four values
- ☐ Quantity_Flag says Missing on exactly 20 rows, and no row was deleted
- ☐ The Cleaning Log has one complete row per fix
- ☐ Every pod member can explain every step
Quick Recap
Duplicates first, then dates, categories and flags, each one checked with a count. Flag what is real but incomplete; never delete it. The Cleaning Log is part of the deliverable, not paperwork after it. Raw Data is sacred: it is how anyone can check your work.
Next, Week 3, Live 1: how Excel reads a formula, the basic functions from SUM to MAX, and labelling data with IF, IFS, AND and OR. Next week your pod puts a value on every Verdant transaction.