Excel Project · Beginner · 2 to 3 hours
Clean and analyze a sari-sari store's messy sales export
A real cleanup job: duplicates, inconsistent names, and blank cells stand between you and the store owner's first sales report.
The brief
Aling Nena runs a mini-mart with two branches and finally exported a month of point-of-sale data. The export is messy: product names typed in different casing, duplicated receipts from a POS glitch, blank totals, and payment methods encoded three different ways. She wants one clear answer: what sells, when, and how much of her money now moves through GCash. This is what most first analytics tasks in a real company actually look like.
Your role
You are the freelance data analyst Aling Nena hired for a one-day engagement. Deliverable: a clean worksheet plus a one-page summary she can act on.
The dataset
One month of POS transactions (May 2026), intentionally messy
sari-sari-sales.csv · 384 rows
Columns: date, or_number, product, category, qty, unit_price, total_amount, payment_method, branch
Setup
- Download sari-sari-sales.csv and open it in Excel or Google Sheets.
- Save a working copy so you always keep the raw file untouched, the same discipline analysts follow at work.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Find and remove the duplicated receipts (same OR number, identical row).
- 2Standardize the product names with TRIM and PROPER so each product appears exactly one way.
- 3Fill the blank total_amount cells with a formula (qty times unit_price).
- 4Standardize payment_method to exactly Cash or GCash, and label blanks as Unknown.
- 5Build a pivot table of revenue by product and find the top 5 earners.
- 6Build a pivot of revenue by day of week to find the busiest selling day.
- 7Compute the share of revenue paid through GCash.
- 8Lay out a one-page summary sheet: top products chart, weekday chart, payment split, and 3 written recommendations.
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 10 sample rows into ChatGPT or Claude and ask it to spot every data-quality issue you should check for, then compare with what you found.
- Ask the AI to explain any formula you used but do not fully understand, line by line.
- Draft your 3 recommendations yourself, then ask the AI to challenge them against the numbers.
Expected output
- A cleaned transactions sheet with no duplicates, consistent product names, no blank totals, and standardized payment methods.
- A summary dashboard sheet with a top-products bar chart, a revenue-by-weekday chart, and a payment-method split.
- Three specific, data-backed recommendations for Aling Nena, for example what to restock and when to add staff.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- You should find exactly 15 duplicate rows; 369 unique transactions remain.
- Total revenue for the month is ₱18,410.
- The top product by revenue is Cooking Oil 200ml Pouch at ₱3,264.
- The busiest day of the week by revenue is Sunday.
- GCash carries roughly 54 percent of identifiable revenue (rows with a blank payment method will shift your exact figure slightly).
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.