The first thing most people do when they type 'where is tools in excel' into Google is click the File tab, then Options, then Customize Ribbon — hoping to find a 'Tools' group. That’s not just inefficient. It’s backwards. Excel hasn’t had a Tools menu since 2007. What you’re actually looking for isn’t missing — it’s scattered across six different locations, each with its own logic.
The Setup
You’re auditing a sales ledger for Acme Corp’s APAC region. Finance sent you Sheet1 with raw entries: inconsistent date formats, duplicate vendor names, and numbers stored as text in column D. Your job is to clean it before importing into Power BI.
| A | B | C | D | E |
|---|---|---|---|---|
| Vendor ID | Vendor Name | Invoice Date | Amount | Region |
| V-8821 | Skyline Logistics | 15/03/2024 | "$42,500" | APAC |
| V-7743 | NexGen Tech | 2024-03-17 | "$18,950" | APAC |
| V-8821 | Skyline Logistics | 15/03/2024 | "$42,500" | APAC |
| V-9105 | TerraForm Ltd | 04/04/2024 | "$67,200" | EMEA |
| V-7743 | NexGen Tech | 2024-03-17 | "$18,950" | APAC |
| V-8821 | Skyline Logistics | 15/03/2024 | "$42,500" | APAC |
| V-9218 | Orion Dynamics | 2024-04-05 | "$31,400" | APAC |
| V-7743 | NexGen Tech | 2024-03-17 | "$18,950" | APAC |
| V-8821 | Skyline Logistics | 15/03/2024 | "$42,500" | APAC |
The Challenge
You need to:
- Remove exact duplicates (rows 2 & 4, 3 & 5, etc.) — but only after standardizing dates and cleaning text-numbers
- Convert column D (“Amount”) from text to number — without losing the $ sign or commas
- Standardize invoice dates in column C to ISO format (YYYY-MM-DD)
- Check for hidden characters in Vendor Name (column B) that cause false-duplicate flags
The trap? People assume ‘Tools’ means one place. It doesn’t. Data Validation sits under Data > Data Tools. Text-to-Columns is under Data > Data Tools. Remove Duplicates is under Data > Data Tools — but only if you’ve selected data first. Goal Seek? That’s under Data > Forecast > What-If Analysis. Solver? Under Data > Analyze. And ‘Error Checking’? That’s tucked inside Formulas > Formula Auditing. You’ll waste 12 minutes hunting if you don’t know which tab holds what.
Walking Through It
We’ll fix this in order: clean text → standardize dates → remove duplicates. Each step uses a tool that used to live under Tools.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select D2:D10. Press Alt + H + F + F. Then press Enter. | All " values stripped. Numbers still formatted as text (green triangle appears in top-left of each cell). | Alt+H+F+F |
| 2 | With D2:D10 still selected, go to Data > Text to Columns. Choose Delimited → Next → uncheck all delimiters → Finish. | Values convert to true numbers. Green triangles vanish. Currency formatting remains intact. | Alt+A+T |
| 3 | Select C2:C10. Press Ctrl + 1, choose Category = Date, Type = 2012-03-14, OK. | All dates become serial numbers internally, display as YYYY-MM-DD. “15/03/2024” becomes “2024-03-15”. | Ctrl+1 |
| 4 | Select A1:E10. Go to Data > Remove Duplicates. Check all columns. Click OK. | Excel removes 5 duplicate rows. Returns “5 duplicate values removed, 5 unique values remain.” | Alt+A+M |
Wait — here’s the counterintuitive part: Never run Remove Duplicates before cleaning text and dates. If “2024-03-15” and “15/03/2024” both exist in column C, Excel treats them as different values. Same for “$42,500” (text) vs 42500 (number). You’ll keep duplicates thinking you’ve cleaned them.
Also: The green triangle in D2:D10? That’s Excel’s Error Checking tool — formerly under Tools > Error Checking. Now it’s buried in Formulas > Formula Auditing > Error Checking (Alt+M+X+E), but you don’t need it here. Just use Text to Columns.
The Result
After all four steps, your cleaned dataset looks like this — ready for pivot tables or export:
| A | B | C | D | E |
|---|---|---|---|---|
| Vendor ID | Vendor Name | Invoice Date | Amount | Region |
| V-8821 | Skyline Logistics | 2024-03-15 | 42500 | APAC |
| V-7743 | NexGen Tech | 2024-03-17 | 18950 | APAC |
| V-9105 | TerraForm Ltd | 2024-04-04 | 67200 | EMEA |
| V-9218 | Orion Dynamics | 2024-04-05 | 31400 | APAC |
What Could Go Wrong
Three mistakes I see weekly in live training sessions — with fixes you can apply in under 10 seconds:
- Mistake #1: Running Remove Duplicates on unselected data. Excel says “No data found.” You panic. Reality: Excel requires an active selection. Fix: Click any cell inside your range first (e.g., click C5), then Alt+A+M.
- Mistake #2: Using Find & Replace to delete “$” before converting text to numbers. That strips formatting but leaves quotes — so “"42,500"” becomes “"42,500”, still text. Fix: Skip Find & Replace. Use Text to Columns (Alt+A+T) — it auto-strips quotes and converts in one step.
- Mistake #3: Applying Date formatting (Ctrl+1) to cells containing text like “15/03/2024” without first converting them to real dates. Excel treats it as text and just changes display — no internal conversion. Fix: Select C2:C10, press Alt+H+I+D (Format Cells > Date), then click OK. Or better: use DATEVALUE(C2) in a helper column, then copy-paste values back.
Here’s your quick-reference cheat sheet — print it or pin it to your taskbar:
| Legacy Tool | Where It Lives Now | Alt Shortcut |
|---|---|---|
| Text to Columns | Data > Data Tools | Alt+A+T |
| Remove Duplicates | Data > Data Tools | Alt+A+M |
| Goal Seek | Data > Forecast > What-If Analysis | Alt+A+W+G |
| Solver | Data > Analyze | Alt+A+Y+S |
| Error Checking | Formulas > Formula Auditing | Alt+M+X+E |
| Data Validation | Data > Data Tools | Alt+A+V |
| Spell Check | Review > Proofing | F7 |
