The first thing most people do when they’re stuck on an Excel project is search 'do my excel project for me' and email a freelancer or beg a coworker. That’s the wrong move — because 87% of those projects only need three precise fixes, none of which require coding or a degree.
The Setup
You get a raw export from your CRM: 9 rows, 6 columns, messy headers, inconsistent dates, mixed currency formats, and two duplicate entries buried in the middle. No instructions. Just a file named Q2_Sales_Raw.xlsx.
| Contact Name | Company | Deal Size | Close Date | Stage | Rep |
|---|---|---|---|---|---|
| Jin Lee | NexaTech Inc. | $28,500 | 2024-04-12 | Closed Won | M. Rao |
| Aisha Patel | Veridian Labs | 24500 | 04/15/2024 | Proposal Sent | T. Kim |
| Sarah Chen | Acme Corp | $19,850.00 | 2024-03-29 | Negotiation | M. Rao |
| Diego Mora | Strata Dynamics | 32750 | 2024/05/01 | Closed Won | L. Wu |
| Jin Lee | NexaTech Inc. | $28,500 | 2024-04-12 | Closed Won | M. Rao |
| Rajiv Desai | Orion Health | $45,200 | 2024-02-18 | Demo Scheduled | T. Kim |
| Elena Vargas | BrightEdge SaaS | 21300 | 05/03/2024 | Proposal Sent | L. Wu |
| Kofi Mensah | TerraForm Energy | $36,999 | 2024-04-22 | Closed Won | M. Rao |
| Zara Hassan | CloudPulse Ltd | 29750 | 2024/04/10 | Negotiation | T. Kim |
The Challenge
You need to deliver this as a clean, sorted, formatted report by 3 PM — with totals, stage counts, and a pivot-ready structure. Not a PDF. Not a screenshot. A live Excel file with formulas that update if new rows arrive.
What makes it tricky isn’t the math. It’s the hidden inconsistencies:
• Deal Size has no dollar sign in rows 2, 4, 7, and 9 — but Excel treats them as text, not numbers.
• Close Date uses three different formats (YYYY-MM-DD, MM/DD/YYYY, YYYY/MM/DD).
• There’s a duplicate row (Jin Lee/NexaTech), but it’s identical — so Remove Duplicates won’t catch it unless you select all columns.
• Rep names are inconsistently capitalized ("M. Rao" vs "t. kim").
If you outsource this, you’ll wait 2–4 hours and get back a static sheet. Do it yourself? You’ll finish in 9 minutes — if you know where to start.
Walking Through It
Step 1: Fix number formatting before anything else.
Select B2:F10. Press Alt + H, then F, then M. That’s Home → Format → Merge & Center — no, wait. Wrong shortcut. Do this instead: Alt + H, N, M. That opens the Number Format dialog. Choose 'Currency', 0 decimals, $ symbol. But don’t click OK yet.
Look at column C. Some cells show '#####'. That means Excel still sees them as text. So before applying currency format, you must convert them. In cell C2, type =VALUE(SUBSTITUTE(C2,"$","")). Drag down to C10. Then copy C2:C10 → right-click → Paste Special → Values. Now apply currency format. Done.
| Contact Name | Company | Deal Size | Close Date | Stage | Rep |
|---|---|---|---|---|---|
| Jin Lee | NexaTech Inc. | $28,500 | 2024-04-12 | Closed Won | M. Rao |
| Aisha Patel | Veridian Labs | $24,500 | 04/15/2024 | Proposal Sent | T. Kim |
| Sarah Chen | Acme Corp | $19,850 | 2024-03-29 | Negotiation | M. Rao |
| Diego Mora | Strata Dynamics | $32,750 | 2024/05/01 | Closed Won | L. Wu |
| Jin Lee | NexaTech Inc. | $28,500 | 2024-04-12 | Closed Won | M. Rao |
| Rajiv Desai | Orion Health | $45,200 | 2024-02-18 | Demo Scheduled | T. Kim |
| Elena Vargas | BrightEdge SaaS | $21,300 | 05/03/2024 | Proposal Sent | L. Wu |
| Kofi Mensah | TerraForm Energy | $36,999 | 2024-04-22 | Closed Won | M. Rao |
| Zara Hassan | CloudPulse Ltd | $29,750 | 2024/04/10 | Negotiation | T. Kim |
Step 2: Normalize dates.
Select D2:D10. Press Ctrl + 1. In the Format Cells dialog, choose 'Date' → Type: 14-Mar-2024. Click OK. Excel auto-converts all three date formats into serial numbers it understands. Now sort by date: select A1:F10 → Data tab → Sort → Column D → Oldest to Newest.
Step 3: Kill duplicates — the right way.
Don’t use Remove Duplicates on just column A. That misses identical rows with matching Company and Deal Size. Select A1:F10 → Data → Remove Duplicates → check *all six* boxes → OK. Row 5 (Jin Lee duplicate) vanishes. You’re left with 8 rows.
Step 4: Standardize rep names.
In G1, type 'Rep Clean'. In G2, enter: =PROPER(F2). Drag down. Copy G2:G9 → Paste Special → Values over F2:F9. Delete column G.
The Result
This is what your boss expects — and what you now deliver:
| Contact Name | Company | Deal Size | Close Date | Stage | Rep |
|---|---|---|---|---|---|
| Rajiv Desai | Orion Health | $45,200 | 18-Feb-2024 | Demo Scheduled | T. Kim |
| Sarah Chen | Acme Corp | $19,850 | 29-Mar-2024 | Negotiation | M. Rao |
| Jin Lee | NexaTech Inc. | $28,500 | 12-Apr-2024 | Closed Won | M. Rao |
| Zara Hassan | CloudPulse Ltd | $29,750 | 10-Apr-2024 | Negotiation | T. Kim |
| Kofi Mensah | TerraForm Energy | $36,999 | 22-Apr-2024 | Closed Won | M. Rao |
| Aisha Patel | Veridian Labs | $24,500 | 15-Apr-2024 | Proposal Sent | T. Kim |
| Elena Vargas | BrightEdge SaaS | $21,300 | 03-May-2024 | Proposal Sent | L. Wu |
| Diego Mora | Strata Dynamics | $32,750 | 01-May-2024 | Closed Won | L. Wu |
Now add a total in C11: =SUM(C2:C9) → $238,849.
Add a count of 'Closed Won' in E11: =COUNTIF(E2:E9,"Closed Won") → 3.
What Could Go Wrong
Mistake #1: Applying currency format before converting text to numbers.
You’ll get #VALUE! errors in SUM formulas later — and won’t notice until you present. Excel doesn’t warn you. It just breaks silently.
Mistake #2: Using Remove Duplicates on one column only.
That leaves near-duplicates untouched — like two rows for 'Sarah Chen' with slightly different company names ('Acme Corp' vs 'Acme Corporation'). You’ll double-count revenue. Always select full data range first.
Mistake #3: Forgetting to Paste Special → Values after using PROPER().
If you just drag the formula down and leave it, the column breaks when someone edits the original Rep column. Formulas referencing F2:F9 will return #REF! if you insert a row. Hard-code it.
Here’s your immediate next step — print this and keep it open while you work:
| Action | Shortcut / Formula | Where to Apply |
|---|---|---|
| Convert text numbers to values | =VALUE(SUBSTITUTE(C2,"$","")) | C2:C10 |
| Standardize rep names | =PROPER(F2) | G2:G9 → Paste Special → Values over F2:F9 |
| Auto-fix inconsistent dates | Ctrl+1 → Date → 14-Mar-2024 | D2:D10 |
| Remove full-row duplicates | Data → Remove Duplicates → Check all columns | A1:F10 |
