The Case, five steps guide
Forty sign-ups typed in by the customers: stray spaces, odd capitals, and some people who signed up twice with the same email written slightly differently. Clean the names and emails, mark the repeats, count the real customers, and list the emails that came up more than once.
Why the raw list lies
"[email protected]" and "[email protected] " are the same customer. COUNTIF and UNIQUE ignore capitals, but not the space at the end, so on the raw column they count two people. Clean first, then count.
Step by step
- Names.
=PROPER(TRIM(A2)). TRIM removes spaces at the ends and turns double spaces inside into one; PROPER gives each word a capital. See TRIM. - Emails.
=LOWER(TRIM(B2)). One form for every address, so they compare as equal. - Duplicates.
=IF(COUNTIF($E$2:E2,E2)>1,"Duplicate",""). Only the start of the range is locked, so it grows as you fill down: each row counts how often its email has come up so far. The first sign-up counts 1 and stays unmarked. - Customers.
=COUNTA(UNIQUE(E2:E41)), on the clean column. See UNIQUE. - The repeated emails.
=UNIQUE(FILTER(E2:E41,F2:F41="Duplicate")): the duplicate rows, each email once. See FILTER.
Where it goes wrong
- TRIM without PROPER, or the other way round. Either the spaces or the capitals stay.
- PROPER on an email. It capitalises every part: [email protected].
- COUNTIF over the whole column.
COUNTIF($E$2:$E$41,E2)>1marks every copy, the first one too, so deleting the marked rows deletes the customer. - Counting the raw column. The trailing space makes 37 customers out of 35.
- FILTER without UNIQUE. An email signed up three times has two Duplicate rows and comes out twice.
PROPER isn't perfect
PROPER capitalises after anything that isn't a letter, so o'brien becomes O'Brien, which is right, and McAllister becomes Mcallister, which isn't. Check names like these by hand. TRIM also doesn't remove the non-breaking space (character 160) that web pages paste in; SUBSTITUTE(A2,CHAR(160)," ") turns it into a normal space first.
FAQ
- How do I clean names in Excel?
- Use =PROPER(TRIM(A2)): TRIM removes extra spaces and PROPER capitalises each word. Check names like McAllister by hand.
- How do I mark duplicates in Excel but keep the first one?
- Use a range that grows as you fill down: =IF(COUNTIF($E$2:E2,E2)>1,"Duplicate",""). The first occurrence counts 1 and stays unmarked.
- How do I count unique customers in Excel?
- Clean the column first (TRIM, LOWER), then =COUNTA(UNIQUE(range)). On raw data, a trailing space makes the same email count twice.
- Is COUNTIF case-sensitive?
- No. COUNTIF and UNIQUE treat "Ada" and "ada" as the same, but a space at the end makes values different.



































































