Why does your sales report show $12,500 as text instead of a number? Why does =SUM(A2:A10) return zero even though the cells look filled? Why does changing one date break three pivot tables?
Because you’re adjusting data like it’s Word—not Excel. You’re editing cells directly, overwriting formulas, ignoring data types, and skipping validation. That’s why numbers won’t sum, dates won’t sort, and filters fail silently.
The Setup
You get a raw export from your CRM: unformatted, inconsistent, and full of hidden junk. It lands in Sheet1, starting at A1. Here’s what it actually looks like:
| Sales Rep | Region | Deal Value | Close Date | Status |
|---|---|---|---|---|
| Sarah Chen | APAC | $45,200 | 2024-03-15 | Won |
| James Okafor | EMEA | $32,750.00 | Mar 22, 2024 | Won |
| Priya Mehta | APAC | $61,000 | 2024/04/01 | Pending |
| Diego Ruiz | Americas | $28,900.00 | 04/05/2024 | Lost |
| Amina Diallo | EMEA | $19,450 | 2024-03-30 | Won |
| Kenji Tanaka | APAC | $53,120.00 | Apr 12, 2024 | Pending |
| Lena Petrova | EMEA | $37,800 | 2024-04-08 | Won |
| Marcus Bell | Americas | $22,300.00 | 04/15/2024 | Pending |
| Tara Singh | APAC | $48,650 | 2024-04-10 | Won |
The Challenge
You need to adjust this data for reporting—but not just ‘fix’ it. You must:
- Convert all Deal Value entries to true numbers (no dollar signs, no commas as text)
- Standardize Close Date to Excel-recognized dates (not text strings)
- Replace 'Pending' with 'In Review' across column E without affecting formulas elsewhere
The trap? Using Find & Replace on column E *before* cleaning dates or numbers. That breaks cell formatting. Or double-clicking each value in column C and retyping—introducing typos and killing formula links. Or worse: selecting B2:E10 and hitting Ctrl+1 to format—but that doesn’t convert text-to-number. It just masks the problem.
Walking Through It
Step 1: Fix Deal Value (Column C)
Don’t delete $ or commas manually. Do this:
• Select C2:C10
• Press Alt + H + F + S (Home → Format → Format Cells)
• Choose ‘Number’, 0 decimal places, uncheck ‘Use 1000 Separator’
• Click OK → nothing changes yet. That’s expected.
Now press Alt + H + N (Home → Number → Text to Columns). In the wizard, choose ‘Delimited’ → Next → uncheck everything → Next → under Column data format, select ‘General’ → Finish.
This forces Excel to reinterpret each cell as numeric. Check C2: it now shows 45200 (not $45,200), and =ISNUMBER(C2) returns TRUE.
| Before (C2:C10) | After (C2:C10) |
|---|---|
| $45,200 | 45200 |
| $32,750.00 | 32750 |
| $61,000 | 61000 |
| $28,900.00 | 28900 |
| $19,450 | 19450 |
| $53,120.00 | 53120 |
| $37,800 | 37800 |
| $22,300.00 | 22300 |
| $48,650 | 48650 |
Step 2: Standardize Close Date (Column D)
Select D2:D10. Press Ctrl + H. In ‘Find what’, type , (comma). Leave ‘Replace with’ blank. Click ‘Replace All’. Now do the same for / and -—but only if they appear inconsistently. Then press Alt + H + N + D (Home → Number → Short Date). Excel will auto-convert most entries. For stubborn ones like “Apr 12, 2024”, use =DATEVALUE(D2) in column F, then copy-paste values back to D2. Surprising tip: If DATEVALUE fails, wrap it in IFERROR: =IFERROR(DATEVALUE(D2),--SUBSTITUTE(SUBSTITUTE(D2," ","-"),",","")) — then paste values.
Step 3: Update Status (Column E)
Select E2:E10. Press Ctrl + H. Find: Pending, Replace: In Review. Click ‘Replace All’. Done. No formulas broken. No reformatting needed.
The Result
Here’s your clean, report-ready table—fully numeric, date-recognized, and consistently labeled:
| Sales Rep | Region | Deal Value | Close Date | Status |
|---|---|---|---|---|
| Sarah Chen | APAC | 45200 | 2024-03-15 | Won |
| James Okafor | EMEA | 32750 | 2024-03-22 | Won |
| Priya Mehta | APAC | 61000 | 2024-04-01 | In Review |
| Diego Ruiz | Americas | 28900 | 2024-04-05 | Lost |
| Amina Diallo | EMEA | 19450 | 2024-03-30 | Won |
| Kenji Tanaka | APAC | 53120 | 2024-04-12 | In Review |
| Lena Petrova | EMEA | 37800 | 2024-04-08 | Won |
| Marcus Bell | Americas | 22300 | 2024-04-15 | In Review |
| Tara Singh | APAC | 48650 | 2024-04-10 | Won |
What Could Go Wrong
Mistake #1: Editing inside a merged cell
You see ‘Total Q1’ merged across A1:E1 and try to change ‘Q1’ to ‘Q2’. Excel warns ‘Merged cells cannot be edited individually’. But you ignore it and hit Enter anyway. Result: Only A1 updates. B1:E1 stay blank. Your SUM() below now includes empty rows. Fix: Unmerge first (Alt + H + M + U), adjust, then remerge if absolutely necessary.
Mistake #2: Pasting over formulas with values
You copy cleaned numbers from C2:C10 and paste into the same range using Ctrl+V. Excel replaces =ROUND(B2*1.15,0) with static 45200. Later, when B2 changes, C2 won’t update. Fix: Paste Special → Values (Alt + H + V + V) only when you *intend* to break the link.
Mistake #3: Applying date formatting before conversion
You select D2:D10 and press Alt + H + N + D *before* running Text to Columns or DATEVALUE. Excel treats ‘Mar 22, 2024’ as text, then formats it as a date—but it stays text. =MONTH(D2) returns #VALUE!. Fix: Always verify with =ISTEXT(D2) first. If TRUE, convert *then* format.
Next step: Open your last messy dataset. Run these three actions in order: (1) Text to Columns on number columns, (2) DATEVALUE + Paste Values on date columns, (3) Ctrl+H on text labels. Do it now — before you open another email asking for ‘the updated numbers’.
