📊 Lesson 7.3: Project — A Data Cleanup & Analysis Workflow

Assess the Mess

You can't fix what you haven't spotted, so the first step is always to look. Scroll the data, sort a column, and ask: what's inconsistent? Common problems, and how they hurt you:

Clean It Up

The golden rule: never clean in place. Keep the raw data on its own tab, untouched, and build cleaned columns on a separate "Staging" tab. That way you can always compare, and you never destroy the original. Your cleaning toolkit (much of it from Lesson 2.3):

• TRIM to strip extra spaces; CLEAN to remove odd non-printing characters.
• PROPER / UPPER / LOWER to standardize case.
• SUBSTITUTE to fix known bad patterns (e.g. replace "St." with "Street").
• SPLIT or Data → Split text to columns to separate combined fields.
• Data → Data cleanup → Remove duplicates to drop exact duplicate rows, and "Trim whitespace" for a one-click space fix.
• Fix text-dates by rebuilding with DATE() or re-entering, and set the column to a real date format.

A combined cleanup formula is common: =PROPER(TRIM(A2)) fixes case and spaces in one pass. For splitting "Last, First": =SPLIT(A2, ",") then TRIM each part.

Standardize & Prevent

Cleaning today is good; preventing the mess tomorrow is better. Once you have a tidy, analysis-ready table, lock in the standard:

• Add data validation dropdowns (Lesson 3.2) to categorical columns so future entries can only be valid values — no more "In progress" vs "in-progress".
• Set proper number and date formats so new values are typed consistently.
• Use UNIQUE to get a clean list of the distinct values in a column — a great way to audit what's actually in there: =UNIQUE(A2:A).
• Document the rules on a Notes tab so collaborators follow them.

⚠️ Important Note: "Remove duplicates" deletes rows permanently, so run it on a copy (your staging or analysis tab), never on your only copy of the raw data. This is exactly why the raw-data-stays-raw discipline matters — it's your safety net when a cleanup step goes further than you intended.

Analyze — Pivot and QUERY

With clean data, analysis is finally trustworthy. To reinforce both tools you've learned, answer the same question two ways — say, "total amount per category."

With a pivot table (Lesson 5.1): Insert → Pivot table, drag Category to Rows and Amount (summarized by SUM) to Values. Point-and-click, great for exploring.

With QUERY (Lesson 4.4) — here Category is in column C and Amount in D, as in the project below:

=QUERY(Analysis!A1:D, "select C, sum(D) group by C order by sum(D) desc label sum(D) 'Total'", 1)

Both should agree — if they don't, your data still has a cleanliness problem (often stray spaces making "Food" and "Food " into two categories). That cross-check is itself a cleaning test. Then visualize the result with a chart (Lesson 5.2) to finish the story.

A Repeatable Workflow

The real deliverable of this lesson isn't one cleaned dataset — it's a process you can reuse forever. Structure your workbook into three clear stages:

• Raw — paste new data here; never edit it. It's your source of truth and your undo.
• Staging — your cleaning formulas transform Raw into tidy columns.
• Analysis — the clean, validated table (baked to values), ready for pivots and QUERY.

Next time you get a messy export, you drop it into Raw and the workflow does the rest. That's how you turn a painful chore into a two-minute routine.

🎯 Project: Clean, Then Analyze

Run the whole pipeline on a genuinely messy dataset. Make one, or paste in a real export you have.

Clean text:      =PROPER(TRIM(Raw!A2))
Split "Last, First": =SPLIT(Raw!B2, ",")   then TRIM the parts
Audit categories:    =UNIQUE(Analysis!C2:C)
Analyze (QUERY):     =QUERY(Analysis!A1:D,
                       "select C, sum(D) group by C order by sum(D) desc", 1)

If the pivot and QUERY totals disagree, hunt for stray
spaces or case differences — the data isn't fully clean yet.

Best Practices for Cleanup

✅ Do's

• Assess before you act — sort and scan to find the problems.
• Clean non-destructively on a staging tab; bake to values when done.
• Prevent recurrence with validation and documented rules.

❌ Don'ts

• Don't Remove duplicates on your only raw copy — it's permanent.
• Don't analyze dirty data — confident wrong answers are worse than no answer.
• Don't forget the space/case gremlins — they silently split categories.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You've completed all three real-world projects — a budget tracker, a task dashboard, and now a full data pipeline. You can take data from chaos to insight reliably, which is a genuinely marketable skill. You're fully prepared for the capstone, where you'll bring everything together into one interactive dashboard.

❓ Common Questions at This Stage

🔭 Looking Ahead

Next — Lesson 8.1: Templates, Add-ons & Gemini / AI in Sheets — we begin the final module. You'll explore templates and add-ons, and get an honest look at the AI features arriving in Sheets, including a clear read on what's free versus paid.

✅ Before the Next Lesson

• Save your cleanup workflow — the raw → staging → analysis structure is reusable
• Note one cleaning trick you'll use again
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Clean up your data (Google Support)
• Google Sheets function list