Excel Project · Beginner · 2 to 3 hours
Rescue a candle shop's merged multi-platform orders file
Three platform exports pasted into one spreadsheet: duplicates, stray headers, three date formats, peso signs stored as text, and payment labels typed six ways. Untangle it all.
The brief
Liwanag Home Scents sells candles and room sprays on TikTok Shop, Shopee, and a Facebook Page. The owner's VA merged the three Q2 exports into a single file for a sales report, and the merge brought every platform's quirks along: TikTok dates look like 2026-05-04, Shopee dates like 05/04/2026, Facebook dates like May 4 2026. Shopee exported totals as text with peso signs. Payment methods were typed freely (GCash, gcash, G-Cash, PayMaya, Cash on Delivery). A chunk of rows got pasted twice, the file headers appear again in the middle of the data, some names came through garbled (Pena became Peña), and a few quantities are plainly impossible. The owner wants one number she can trust before doubling her ad budget. This is the single most common task in real analytics work: making a merged file mean something.
Your role
You are the analyst the owner hired for a cleanup-and-report engagement. Deliverable: a clean orders sheet, a quarantine list, and the Q2 numbers she can act on.
The dataset
Q2 2026 orders from three platforms, merged by a VA, intentionally messy
shop-orders-merged.csv · 378 rows
Columns: order_id, order_date, platform, customer_name, customer_phone, city, product, qty, unit_price, total, payment_method, delivery_fee
Setup
- Download shop-orders-merged.csv and open it in Excel or Google Sheets.
- Save a working copy so the raw file stays untouched, the same discipline analysts follow at work.
- Add a cleaning_log tab and record every fix with a count as you go; the log is part of the deliverable.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Find and delete the stray header rows that got mixed into the data during the merge.
- 2Remove the exact duplicate rows (a re-pasted chunk) and log how many you removed.
- 3Convert all three date formats into one real date column that sorts correctly.
- 4Convert the text totals (the ones with peso signs and commas) into real numbers, and verify every total equals qty times unit_price.
- 5Build a small mapping table and standardize payment_method to exactly GCash, Maya, COD, or Bank Transfer.
- 6Clean the city column: fix casing, trim spaces, and repair the garbled characters (ñ should be ñ).
- 7Treat placeholders as what they are: N/A phones and unknown cities stay, but label them consistently; a 9999 delivery fee means unknown, not nine thousand pesos.
- 8Quarantine the impossible rows (negative quantities, a quantity of 250, order dates in 2027) into a for-review list instead of fixing or deleting them.
- 9With the clean sheet: compute Q2 revenue, revenue by platform, the top product, and the GCash share of revenue.
- 10Write a five-line cleaning summary for the owner: what you found, what you fixed, what needs her decision.
Work like an AI-powered analyst
The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.
- Paste 15 sample rows into ChatGPT or Claude and ask it to list every data-quality issue it can spot, then compare against your own list before cleaning.
- Ask the AI for a SUBSTITUTE formula chain that strips the peso sign and comma from a text amount, then have it explain each step.
- Describe your quarantine rule to the AI and ask it to argue when quarantining beats deleting, and when it does not.
Expected output
- A clean orders sheet: one header row, no duplicates, one date format, numeric totals, four payment methods, readable names and cities.
- A quarantine list of the impossible rows with a one-line reason each.
- A cleaning log with a count per fix, plus the five-line summary for the owner.
- The Q2 numbers: total revenue, revenue by platform, top product, and GCash share.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- You should find exactly 2 stray header rows and exactly 12 duplicate rows; 364 orders remain after removing them.
- Exactly 7 rows belong in quarantine: 3 negative quantities, 2 quantities of 250, and 2 orders dated 2027.
- Clean Q2 revenue (the 357 kept orders) is ₱253,578.
- TikTok Shop is the top platform at ₱87,328 in revenue.
- The top product is the Citrus Reed Diffuser Set at ₱61,488.
- After mapping the payment labels, GCash carries 25.4 percent of revenue.
Finished it? Put it in your portfolio.
This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.