Why does your SUM formula return zero when numbers are clearly visible in column D? Why does Ctrl+C/Ctrl+V paste values *and* formatting even when you only want numbers? Why does Excel say 'The file is corrupt' after saving a file that opened fine five minutes ago?
The answer isn’t bad data or outdated software. It’s that Excel isn’t operating — it’s idling. And most people don’t realize it needs explicit permission to behave like Excel.
The Setup
We’ll use a real procurement log from Alibaba Cloud’s AP team — downloaded from their internal vendor portal on 2024-04-12. This isn’t dummy data. It’s messy: inconsistent date formats, mixed text/numbers in price columns, and trailing spaces hiding in supplier names. You’ve seen this before — maybe last Tuesday, while trying to reconcile invoices before lunch.
| A | B | C | D | E |
|---|---|---|---|---|
| Order ID | Supplier | Date | Qty | Amount |
| ORD-7821 | Shenzhen Tech Solutions | 2024/03/05 | 12 | $2,450.00 |
| ORD-7822 | Ningbo Precision Parts | 03-11-2024 | 8 | $1,890 |
| ORD-7823 | Guangzhou Opto Systems | 2024.03.18 | 15 | USD 3,200 |
| ORD-7824 | Chengdu NanoFab Ltd | Mar 22, 2024 | 6 | $980.50 |
| ORD-7825 | Suzhou Quantum Labs | 2024/04/01 | 20 | $5,100 |
| ORD-7826 | Xiamen SmartLogix Inc | 04/05/2024 | 10 | $1,675.00 |
| ORD-7827 | Wuhan DataCore Group | April 8, 2024 | 4 | $720 |
| ORD-7828 | Hangzhou AI Chips Co | 2024-04-10 | 14 | $4,320 |
| ORD-7829 | Dalian Embedded Sys | 2024/04/12 | 9 | $1,295.75 |
The Challenge
You need to calculate total spend per supplier, but none of the standard formulas work. =SUMIF(B2:B10,"Shenzhen Tech Solutions",E2:E10) returns 0. Even =ISNUMBER(E2) says FALSE for cells that look like $2,450.00. The Amount column contains text, not numbers — and Excel won’t tell you why unless you check the status bar. Worse, the Date column has six different formats across nine rows. If you try to sort by date, Excel treats them as text and sorts alphabetically: ‘03-11-2024’ jumps ahead of ‘2024/03/05’. That’s how teams miss deadlines.
This isn’t user error. It’s Excel running in ‘Compatibility Mode’ or with ‘Show Formulas’ accidentally toggled (Alt+`), or — most commonly — with Calculation set to Manual. Yes — if you hit Alt+M+A, then click Manual, Excel stops calculating until you press F9. Your colleagues’ files work because theirs is set to Automatic. Yours isn’t.
Walking Through It
Start in cell A1. Don’t touch any data yet. First, verify Excel is actually operating:
- Press Alt+M+A → choose ‘Automatic’ (not ‘Manual’)
- Press Alt+= in cell F1 — you’ll see =SUBTOTAL(9,E2:E10). Delete it. We’re not ready for totals yet.
- Select E2:E10. Press Ctrl+1, go to Number tab, select ‘Text’, OK. Yes — force it to text first. This prevents Excel from auto-converting mixed strings into errors later.
Now clean Amount (column E). In cell F2, enter:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"USD ",""),"$",""),",","")+0
This strips currency symbols, commas, and “USD”, then forces numeric conversion with +0. Copy down to F10.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Apply formula in F2 | 2450 | Enter |
| 2 | Copy F2:F10 | All values now numeric | Ctrl+C / Ctrl+V |
| 3 | Paste as Values over E2:E10 | E column now truly numeric | Alt+E+S+V |
| 4 | Select B2:B10 → Data → Text to Columns → Delimited → Next → uncheck all → Finish | Trailing spaces removed from supplier names | Alt+A+T |
Next, fix the Date column. Select C2:C10. Press Ctrl+H. Find what: “,” Replace with: “” → Replace All. Then repeat for “.” and “-”. Now all dates have slashes or nothing. In G2, enter:=DATEVALUE(C2)+0
Copy down. Then select G2:G10 → Alt+E+S+V → format as Short Date. Paste values back into C2:C10.
Here’s the counterintuitive part: Never use DATEVALUE on cells already formatted as Date. If Excel thinks it’s a date but stores it as text (like “Mar 22, 2024”), DATEVALUE works. If it’s stored as number 45372, DATEVALUE returns #VALUE!. So always test with =ISTEXT(C2) first. In our sample, C4 returns TRUE — so DATEVALUE is safe there. C5 returns FALSE — skip it.
The Result
After cleaning, your data is stable. Sorting by Date now works. SUMIF returns correct totals. Pivot tables refresh without blanks. Here’s what column C (Date), E (Amount), and B (Supplier) look like after full cleanup:
| A | B | C | D | E |
|---|---|---|---|---|
| Order ID | Supplier | Date | Qty | Amount |
| ORD-7821 | Shenzhen Tech Solutions | 2024-03-05 | 12 | 2450.00 |
| ORD-7822 | Ningbo Precision Parts | 2024-03-11 | 8 | 1890.00 |
| ORD-7823 | Guangzhou Opto Systems | 2024-03-18 | 15 | 3200.00 |
| ORD-7824 | Chengdu NanoFab Ltd | 2024-03-22 | 6 | 980.50 |
| ORD-7825 | Suzhou Quantum Labs | 2024-04-01 | 20 | 5100.00 |
| ORD-7826 | Xiamen SmartLogix Inc | 2024-04-05 | 10 | 1675.00 |
| ORD-7827 | Wuhan DataCore Group | 2024-04-08 | 4 | 720.00 |
| ORD-7828 | Hangzhou AI Chips Co | 2024-04-10 | 14 | 4320.00 |
| ORD-7829 | Dalian Embedded Sys | 2024-04-12 | 9 | 1295.75 |
What Could Go Wrong
Three mistakes I saw three times last week — all in the same department, all on the same file:
- Mistake #1: Using AutoSum on mixed data
Someone highlights E2:E10 and hits Alt+=. Excel inserts =SUM(E2:E10), but since some cells contain “USD 3,200” and others “$1,890”, the formula silently returns 0. Status bar shows “Sum: 0” — no warning. Fix: Always check the status bar *before* accepting AutoSum. If it says “Count: 9” instead of “Sum: ####”, your range contains non-numbers. - Mistake #2: Pasting over merged cells
A colleague pasted cleaned data into column C, but the original sheet had merged cells in row 3 (C3:D3). Excel split the date across two cells and corrupted alignment. No error message — just broken references downstream. Fix: Before pasting, select the destination range and press Ctrl+1 → Alignment tab → uncheck ‘Merge cells’. - Mistake #3: Saving as .xls instead of .xlsx
They saved the cleaned file as Excel 97-2003 (.xls) to “share with finance.” That format caps rows at 65,536 and drops dynamic arrays. Their =UNIQUE(B2:B10) turned into #NAME?. Fix: Use File → Save As → browse to location → change ‘Save as type’ to ‘Excel Workbook (*.xlsx)’ — not the default ‘Excel 97-2003’.
Here’s your immediate next step — copy-paste this into a blank workbook and run it:
| Shortcut | What it does | When to use it |
|---|---|---|
| Alt+M+A | Toggle calculation mode (Auto/Manual) | Before any SUM, AVERAGE, or pivot table |
| Alt+E+S+V | Paste Values only | After cleaning formulas — never paste formulas into reports |
| Ctrl+1 | Open Format Cells dialog | To verify number/date/text format — not just how it looks |
| Alt+` | Toggle Show Formulas | If formulas disappear or show as text, hit this first |
