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

The Analyst Workflow: Clean → Structure → Analyze → Conclude

Here is a truth every working analyst learns fast: the analysis is the easy part. The cleaning is where the time goes. Surveys of data professionals routinely find they spend the majority of their time just preparing data before any real analysis begins. That's not a failure — it's the job. Dirty data doesn't just look untidy; it produces wrong answers. Count "Alice Nguyen" and "alice nguyen " as two different customers and your customer count is inflated and your per-customer totals are wrong, silently and confidently.

So we follow a disciplined, repeatable workflow — four stages, always in this order:

The last stage is the one beginners skip and professionals never do. Numbers on a screen aren't insight — insight is a sentence a decision-maker can act on: "West region drove 41% of revenue but has the fewest reps — worth investing there." We'll finish this project by writing a sentence just like that. Clean, structure, analyze, conclude.

Meet the Messy Data

Here's our starting point: a sales export that's typical of what lands in your inbox. Notice everything wrong with it — mixed capitalization, trailing and doubled spaces, a full name in one column that we'll need to split, dates in three different formats, and a duplicate row. Type this (messiness and all) into a new sheet named Raw, starting at A1:

Raw sheet — the messy import
      A                    B            C            D
1   Customer             Region       Order Date   Amount
2   alice nguyen         west         2026-01-05   1200
3   BEN CARTER           East         01/07/2026   450
4   carla  ruiz          WEST         Jan 9, 2026  980
5   ben carter           east         01/07/2026   450
6   DANA LEE             North        2026-01-11   1750
7    alice nguyen        West         2026-01-14   600
8   Evan Ortiz           south        2026-01-18   320
9   carla ruiz           west         2026-01-21   1100
10  DANA LEE             north        Jan 25, 2026 890

Let's name the problems precisely, because you can't clean what you can't see:

• Inconsistent case: "alice nguyen", "BEN CARTER", "carla ruiz" — a mix of lower, upper, and messy.
• Extra spaces: row 4 has a double space in "carla  ruiz", row 7 has a leading space before "alice".
• Region case: "west", "WEST", "West" look like three different regions. (Excel's SUMIFS, COUNTIFS, PivotTables, and Remove Duplicates actually ignore capitalization and lump them together — but the report shows whichever spelling Excel met first, and case-sensitive tools such as Power Query would split them.)
• Mixed date formats: ISO (2026-01-05), slash (01/07/2026), and text (Jan 9, 2026) all mixed together — some may not even be real dates to Excel. (On a US-set Excel 01/07/2026 means January 7; on day-first settings it would read as July 1 — always check slash dates after an import.)
• A duplicate row: rows 3 and 5 are the same order (Ben Carter, East, same date, same amount) entered twice.
• One column, two facts: Customer holds a first and last name we may want to split.

If you built a PivotTable on this raw data right now, it would be wrong — "alice nguyen" and " alice nguyen" (that leading space) as two different customers, "carla  ruiz" and "carla ruiz" as two more, a double-counted Ben Carter order inflating East, region labels in whatever capitalization came first, and dates it can't sort. That's exactly why we clean before we analyze. Let's fix it, one problem at a time.

Step 1 — Clean the Text (TRIM, PROPER, MID & Flash Fill)

The safest way to clean is non-destructively: leave the Raw data untouched and build cleaned columns beside it with formulas. That way you can always see what changed, and if a formula is wrong you fix the formula, not re-type the data. Create a Clean sheet (or use columns to the right on the Raw sheet). We'll reference the raw cells and transform them.

Fix spaces and case at once

TRIM removes leading, trailing, and doubled-up internal spaces; PROPER capitalizes the first letter of each word. Nest them and one formula fixes both problems (functions from Lesson 2.3):

Clean the Customer name (both problems in one go)
   =PROPER(TRIM(Raw!A2))     →  "alice nguyen"   becomes  "Alice Nguyen"
                                " alice nguyen"  becomes  "Alice Nguyen"
                                "carla  ruiz"    becomes  "Carla Ruiz"

Fix the Region the same way:
   =PROPER(TRIM(Raw!B2))     →  "WEST" / "west" / "West"  all become  "West"

Read it inside-out: TRIM runs first and hands its cleaned-up text to PROPER. Now every region and customer is spelled, spaced, and cased identically, which is the whole point — the PivotTable later will show one clean "West" and one entry per customer.

Split the full name into First and Last

Splitting "Alice Nguyen" into two columns is a classic. There are two great ways, and it's worth knowing both.

Way 1 — text functions. Find the space with FIND, then grab the pieces with LEFT and MID:

First name  =LEFT(CleanName, FIND(" ", CleanName)-1)
Last name   =MID(CleanName, FIND(" ", CleanName)+1, 100)

FIND(" ",...) returns the position of the space.
LEFT takes everything before it; MID takes everything after it.
(The 100 just means "grab the rest" — any number bigger than the name works.)

Way 2 — Flash Fill. This feels like magic. In the column next to your names, type the first name for the first row or two by hand, and Excel detects the pattern and offers to fill the rest. Type it and press Enter — or trigger it deliberately with Ctrl+E:

Flash Fill (Ctrl+E) — no formula needed
   Cleaned name       You type      Flash Fill guesses the rest
   Alice Nguyen  -->  Alice     -->  Ben, Carla, Dana...   (first names)
   Alice Nguyen  -->  Nguyen    -->  Carter, Ruiz, Lee...  (last names)

The TEXT function is your friend for formatting numbers and dates as clean text — for example =TEXT(Raw!C2,"yyyy-mm-dd") forces a real date into a tidy ISO string, and =TEXT(Raw!D2,"$#,##0") turns 1200 into "$1,200" for a label. We'll use it in the next step to tame the dates.

Step 2 — Fix Dates & the Power Query Option

Dates are the trickiest mess because some of your "dates" may not be dates at all — Excel may have stored Jan 9, 2026 or 01/07/2026 as text, especially after an import. A text date won't sort, filter, or feed a PivotTable's date grouping. The goal is to get every value into a single, genuine date that Excel recognizes.

First, spot the text dates: real dates are right-aligned by default; text dates sit left-aligned. (If you typed the Raw data by hand, Excel may already have turned all three formats into real dates — in a real import, some usually arrive as text.) To convert text that looks like a date into a true date, DATEVALUE often does it — but use it only on the text dates, because pointed at a cell that's already a real date it returns #VALUE!:

Convert a text date to a real date
   =DATEVALUE(Raw!C4)      →  turns the text  "Jan 9, 2026"  into a real date serial

One formula for a mixed column (keeps real dates, converts text ones):
   =IFERROR(DATEVALUE(Raw!C2), Raw!C2)

Then display them all consistently (Home ▸ Number Format ▸ Short Date),
or force a tidy text label with:
   =TEXT(DateCell, "yyyy-mm-dd")   →  2026-01-09

For a whole column of mixed text dates, the fastest fix is often Data → Text to Columns: select the column, click through the wizard, and on the last step choose Date as the column format — Excel re-parses each value into a real date. It's a desktop favorite; on the web, DATEVALUE plus a consistent number format does the same job. Once every date is a real date, they sort correctly and the PivotTable can group by month.

The repeatable option: Power Query

Everything above cleans this file. But imagine you get this same export every week. Re-doing the formulas and Text-to-Columns by hand each time is exactly the kind of tedious, error-prone chore computers should do for you. That's what Power Query is for (introduced in Lesson 6.2): you record the cleanup steps once — trim, proper-case, split column, change type, remove duplicates — and Power Query saves them as a recipe. Next week, drop in the new file and click Refresh; every step re-runs automatically.

The takeaway isn't "learn Power Query right now" — it's to recognize the fork in the road: one-time cleanup (formulas and Flash Fill are perfect) versus repeated cleanup (Power Query pays for itself many times over). Knowing which situation you're in is the mark of an analyst thinking ahead.

Step 3 — Structure & Remove Duplicates

With clean columns built, "set" the cleaned values so they're no longer dependent on the messy originals, then structure them as a Table. To lock in values from formula columns, select them, Ctrl+C to copy, then Paste Special → Values (Ctrl+Shift+V on the web) — this replaces the formulas with their results so you can safely delete the raw data. Your clean, structured dataset should look like this:

Clean sheet — after cleaning (First/Last split, tidy Region & Date)
      A          B         C         D            E
1   First      Last      Region    Order Date   Amount
2   Alice      Nguyen    West      2026-01-05   1200
3   Ben        Carter    East      2026-01-07   450
4   Carla      Ruiz      West      2026-01-09   980
5   Ben        Carter    East      2026-01-07   450   ← duplicate of row 3
6   Dana       Lee       North     2026-01-11   1750
7   Alice      Nguyen    West      2026-01-14   600
8   Evan       Ortiz     South     2026-01-18   320
9   Carla      Ruiz      West      2026-01-21   1100
10  Dana       Lee       North     2026-01-25   890

Convert this to a Table with Ctrl+T and name it tblSales. Now remove the duplicate. Select any cell in the Table, then Table Design → Remove Duplicates (or Data → Remove Duplicates). A dialog lets you choose which columns define a duplicate — tick First, Last, Region, Order Date, and Amount so a row counts as a duplicate only when all of those match. Excel removes the extra Ben Carter row and tells you how many it deleted.

You now have a tidy, structured, de-duplicated Table — the "structure" stage complete. This is the moment the data becomes trustworthy enough to analyze. Everything before this was preparation; everything after is payoff.

Step 4 — Analyze & Conclude

Clean data makes analysis quick and its answers trustworthy. Let's ask our sales data a few real questions three ways — with a PivotTable, with SUMIFS/COUNTIFS, and with dynamic arrays — then write the conclusion.

A PivotTable for the big picture

Click in tblSales → Insert → PivotTable. Drag Region to Rows and Amount (Sum of) to Values. In seconds you get revenue per region — and because the data is clean, "West" appears exactly once with the correct total. Add Order Date to Rows (grouped by month) to see revenue over time, or Last and First to see your best customers.

SUMIFS / COUNTIFS for specific numbers

When you want a single figure in a cell (to headline or reuse), reach for the *IFS family you used in the earlier projects:

Revenue from the West region:
   =SUMIFS(tblSales[Amount], tblSales[Region], "West")

Number of orders in the West:
   =COUNTIFS(tblSales[Region], "West")

Average order value in the West:
   =AVERAGEIFS(tblSales[Amount], tblSales[Region], "West")

Total revenue overall:
   =SUM(tblSales[Amount])

Dynamic arrays for instant lists

If your Excel is Microsoft 365 or the web, dynamic arrays (Lesson 4.4) are a joy here. One formula "spills" a whole list of results — perfect for building a clean report:

A sorted list of unique regions (spills down automatically):
   =SORT(UNIQUE(tblSales[Region]))

Every order from the West, largest first:
   =SORT(FILTER(tblSales, tblSales[Region]="West"), 5, -1)
   (5 = sort by the 5th column, Amount;  -1 = descending)

Customers who spent over 1000 on a single order:
   =FILTER(tblSales, tblSales[Amount]>1000)

Conclude — write the insight

This is the step that turns numbers into value. Look at your results and write one honest paragraph a decision-maker could act on. For our data, something like:

Insight: The West region is the clear revenue leader, contributing just over half of total sales (3,880 of 7,290) across four orders from two customers, Alice Nguyen and Carla Ruiz — though its average order (970) is below North's. North performed strongly on few orders, driven by two large purchases from Dana Lee (an average of 1,320). South is the weakest region and had only one small order. Recommendation: the data suggests West is worth deeper investment, while South needs investigation — is it under-served, or simply a smaller market?

Notice the shape: a claim, the numbers behind it, and a recommendation. That's the difference between "here's a spreadsheet" and "here's what I found and what we should do." A conclusion in plain language is the deliverable — the cleaning and analysis were all in service of being able to write it with confidence.

🎯 Project: Run the Whole Workflow

Take the messy dataset from start to finish — clean it, structure it, remove the duplicate, analyze it, and write your insight. This is the exact process you'll use on real data for the rest of your working life, so do it properly end to end.

Clean text (Customer / Region):
   =PROPER(TRIM(Raw!A2))
   =PROPER(TRIM(Raw!B2))

Split name:
   First  =LEFT(name, FIND(" ",name)-1)
   Last   =MID(name, FIND(" ",name)+1, 100)
   (or just Flash Fill with Ctrl+E)

Fix a text date:
   =DATEVALUE(Raw!C4)     then format as Short Date
   (text dates only — or =IFERROR(DATEVALUE(Raw!C2), Raw!C2) for
    the whole column, or Data ▸ Text to Columns ▸ Date)

Structure:  paste as Values ▸ Ctrl+T ▸ name tblSales ▸ Remove Duplicates

Analyze:
   =SUMIFS(tblSales[Amount], tblSales[Region], "West")
   =COUNTIFS(tblSales[Region], "West")
   =AVERAGEIFS(tblSales[Amount], tblSales[Region], "West")
   =SORT(UNIQUE(tblSales[Region]))
   =SORT(FILTER(tblSales, tblSales[Region]="West"), 5, -1)

Best Practices for Cleaning & Analysis

✅ Do's

• Always keep the raw original on its own sheet. Clean non-destructively so you can trace and undo any change.
• Clean, then structure, then analyze. Never analyze dirty data — the answers will be confidently wrong.
• Prefer repeatable cleanups (formulas, then Power Query) over hand-editing when the data recurs.
• End with a written insight. A claim, the numbers behind it, and a recommendation — that's the deliverable.

❌ Don'ts

• Don't hand-edit cells one by one when a formula or Flash Fill can do the whole column — it's slower and error-prone, and it doesn't repeat.
• Don't run Remove Duplicates carelessly. Choose the key columns thoughtfully so you don't delete legitimately different rows.
• Don't trust a total on data you haven't de-duplicated — a repeated row silently inflates every sum and count.
• Don't stop at the numbers. A pile of pivots isn't analysis until you say what it means.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You just did what professional analysts get paid to do: took ugly, untrustworthy data and turned it into a clean dataset and a defensible conclusion. You practiced the full loop — clean, structure, analyze, conclude — and you know the difference between a one-time fix and a repeatable pipeline. This is arguably the most transferable skill in the whole course.

❓ Common Questions at This Stage

🔭 Looking Ahead

That's Module 7 complete — three real projects behind you. In the next lesson — Lesson 8.1: Templates, Add-ins & Copilot/AI in Excel (Honest Free vs Paid) — we open Module 8 by looking at how to move faster: starting from ready-made templates, extending Excel with add-ins, and using Copilot and AI features — with an honest breakdown of what's free versus what needs a paid subscription.

✅ Before the Next Lesson

• Finish the cleanup project end to end and confirm your PivotTable shows one "West" with the correct total
• Write your one-paragraph insight — practice the claim + numbers + recommendation shape
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Microsoft Excel Help & Learning (Microsoft Support)
• Get & Transform (Power Query) in Excel — Microsoft Support
• microsoft365.com — open Excel for the web