Eoin Derry
the Excel path · ep 01
face goes here
16:45
EPISODE 01
The 4:45pm export:
cleaning messy data
without tears
q3-export-final-FINAL.xlsx
| | Customer | Region | Amount | Date |
|---|
| 2 | niamh BYRNE | dub | 915 | 07/03/2026 |
|---|
| 3 | SEAN o'brien | Cork | 1480 | 2026-03-08 |
|---|
| 4 | aoife murphy | GALWAY | 1240 | Mar 9th |
|---|
RULE ZERO
Never work on
the original
q3-export-working.xlsx
then: Ctrl + T → a proper Table
the untouched original stays one click away —
and the Table keeps formulas honest while rows move
STEP 1 OF 5
Hunt the invisible spaces
| A | what it looks like |
|---|
| 2 | "Dublin " |
|---|
| 3 | "Dublin" |
|---|
| 4 | "Dublin " ← char 160 |
|---|
=TRIM(CLEAN(A2))
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
three strangers —
to every lookup,
sum and filter
STEP 2 OF 5
Fix the casing
| arrives as | function | becomes |
|---|
| niamh BYRNE | =PROPER(A2) | Niamh Byrne |
| SEAN o'brien | =UPPER(A2) | SEAN O'BRIEN |
| Aoife MURPHY | =LOWER(A2) | aoife murphy |
PROPER does 95% of the work — the O'brien / Mcdonald
exceptions are easy to spot once everything else is consistent
STEP 3 OF 5
Numbers that refuse
to be numbers
| C | Amount |
|---|
| 2 | 915 |
|---|
| 3 | 1480 |
|---|
| 4 | 1240 |
|---|
| | Sum: — |
|---|
left-aligned = the tell-tale
1 type 1, copy it
2 select the column
3 Paste Special → Multiply
×1 forces re-evaluation —
text-numbers snap back to real ones
STEP 4 OF 5
Dates that are
secretly text
a date is a number in costume — days since 1 Jan 1900
Data → Text to Columns → step 3
| choose | and say the INCOMING order |
|---|
| ◉ Date | DMY ▾ (how to READ it, not display it) |
displaying it is just a number format afterwards
STEP 5 OF 5
Duplicates —
count before you cut
| Customer | Order date | count |
|---|
| Niamh Byrne | 07/03 | 1 |
| Sean O'Brien | 08/03 | 2 |
| Aoife Murphy | 09/03 | 1 |
| Sean O'Brien | 08/03 | 2 |
=COUNTIFS(B:B, B2, D:D, D2)
filter to >1,
then LOOK —
eyes open,
not on faith
THE ROUTINE, ON A CARD
Six steps. Same order.
Every time.
1copy the file · make it a Table
2TRIM(CLEAN(…)) the text columns
3PROPER / UPPER the casing
4multiply-by-1 the fake numbers
5Text to Columns the fake dates
6count duplicates before removing them
The data doesn't get less messy.
You stop being afraid of it.
eoinderry.com/roadmap
the written guide, the workbook, and the whole path — free