Every month you export the customer list from your invoicing software or the subscriber list from your newsletter, and you need to know what has changed since last month: who is new, who is gone and whose email has been updated. With two files of hundreds of rows, comparing by hand isn’t an option.
Here’s an orderly method to spot additions, removals and changes with Excel formulas, with sample data and the mistakes that cause the most false results.
Before you start: choose a unique key
To compare two lists you need a field that identifies each customer unambiguously and doesn’t change over time. That’s the key. The usual options, from best to worst:
- A customer code (C-001, C-002…). This is ideal: it doesn’t repeat and doesn’t change even if the customer updates their email.
- The email address. It works for subscriber lists, but if someone changes their email they’ll show up as one removal and one addition.
- The name. This is the worst option: names repeat and the same person can be written in several ways.
Copy both versions of the list into the same workbook, on sheets named Previous and Current. Working in a single file simplifies the formulas and avoids references to closed workbooks.
The sample data
Previous sheet (data in A2:C5):
| A: ID | B: Name | C: Email |
|---|---|---|
| C-001 | Customer A | customera@example.com |
| C-002 | Customer B | customerb@example.com |
| C-003 | Customer C | customerc@example.com |
| C-004 | Customer D | customerd@example.com |
Current sheet (data in A2:C5):
| A: ID | B: Name | C: Email |
|---|---|---|
| C-001 | Customer A | customera@example.com |
| C-002 | Customer B | sales.b@example.com |
| C-004 | Customer D | customerd@example.com |
| C-005 | Customer E | customere@example.com |
At a glance: C-005 is an addition, C-003 is a removal and C-002 has changed email. Let’s see how Excel detects it.
Spot additions and removals with COUNTIF
Additions: in Current but not in Previous
On the Current sheet, type the header “Status” in D1 and this formula in D2. Then fill it down:
=IF(COUNTIF(Previous!$A$2:$A$5,A2)=0,"New","Existing")
COUNTIF counts how many times the ID appears on the Previous sheet. If the result is zero, the customer is new. Only C-005 shows as “New”.
Removals: in Previous and no longer there
Do the same on the Previous sheet, looking in the other direction:
=IF(COUNTIF(Current!$A$2:$A$5,A2)=0,"Removed","Still active")
C-003 is marked as “Removed”. With your real data, extend the ranges to the last row or, better, turn each list into a table with Ctrl+T so the ranges grow automatically.
Spot changes in the data
A customer still being on the list doesn’t mean their details are the same. To check the email, bring back the previous value with VLOOKUP and compare it with the current one. This formula, in column D of the Current sheet, replaces the additions formula and handles everything at once:
=IF(COUNTIF(Previous!$A$2:$A$5,A2)=0,"New",IF(VLOOKUP(A2,Previous!$A$2:$C$5,3,FALSE)=C2,"No changes","Email changed"))
First it checks whether the ID exists in the previous list. If it does, it looks up the email it had (column 3 of the range) and compares it with the one in the current row. Result: C-001 and C-004 “No changes”, C-002 “Email changed” and C-005 “New”.
Checking several fields
To track the name as well, add another column with the same idea, changing the column number and the cell being compared:
=IF(COUNTIF(Previous!$A$2:$A$5,A2)=0,"",IF(VLOOKUP(A2,Previous!$A$2:$C$5,2,FALSE)=B2,"","Name changed"))
Keep in mind that comparing with = isn’t case-sensitive: “Customer b” and “Customer B” are treated as equal. For an email, that’s exactly what you want.
Automatic lists of additions, removals and changes in Excel 365
If you have Excel 365 or Excel 2021, FILTER returns each group in full, with all its columns, on a summary sheet. Additions:
=FILTER(Current!A2:C5,COUNTIF(Previous!A2:A5,Current!A2:A5)=0,"No additions")
Removals:
=FILTER(Previous!A2:C5,COUNTIF(Current!A2:A5,Previous!A2:A5)=0,"No removals")
Email changes, using XLOOKUP:
=FILTER(Current!A2:C5,IFERROR(XLOOKUP(Current!A2:A5,Previous!A2:A5,Previous!C2:C5)<>Current!C2:C5,FALSE),"No changes")
IFERROR stops the additions, which don’t exist in the previous list and would return #N/A, from breaking the result. Leave free space below each formula: if the destination cells are occupied, you’ll see the #SPILL! error.
Email lists without a customer code
In a subscriber list, the email is usually the only key. Before comparing, normalise it, because “Ana@Example.com ” and “ana@example.com” are the same address to you, but not always to Excel:
- Insert a helper column next to the email on both sheets.
- Type
=LOWER(TRIM(B2)), assuming the email is in column B, and fill it down. - Use that column as the key in the
COUNTIFformulas from the previous sections. - Review additions and removals with matching names: they may be the same person with a new email.
TRIM removes extra spaces and LOWER converts everything to lowercase, so the comparison becomes much more reliable.
Common mistakes and how to avoid them
- Duplicates in one of the lists. If a customer appears twice, the counts become unclear and
VLOOKUPonly looks at the first match. Clean them first with our duplicate remover. - IDs in different formats. “C-001” and “C001”, or 1 and “0001”, don’t match. Standardise the format before comparing.
- Ranges that stop short. If you add rows and don’t extend the range, new customers will always show up as false additions or removals.
- Forgetting FALSE in VLOOKUP. Without that last argument, the lookup is approximate and may return another customer’s data.
- Working on the original file. Make a copy. This is personal data: store it carefully and don’t share it more than necessary.
When the lists have many columns (phone, address, category, assigned sales rep…), writing one formula per field gets tedious. For those cases you can use our free tool to compare Excel files: upload the previous version as the original and the new one as the modified file, and you’ll get a file with the changes between them. You can then use the formulas in this article to classify whatever interests you.
Conclusion
Comparing two customer lists comes down to three questions: who is new, who has left and what has changed. With a unique key, COUNTIF answers the first two and VLOOKUP or XLOOKUP the third. If you normalise emails, remove duplicates and lock your ranges properly, you’ll have a reliable monthly check you can repeat in a few minutes.
Frequently asked questions
What happens if a customer changes email and I don’t have a customer code?
Excel will see it as a removal (the old email) and an addition (the new one). No formula can guess they’re the same person; manually review additions and removals that share the same name or phone number.
Can I compare lists that are in two different files?
Yes, formulas can reference another workbook, but both should be open to update without problems. It’s easier to copy both sheets into the same workbook before you start.
Are the formulas case-sensitive for emails?
No. Neither COUNTIF nor comparing with = is case-sensitive, which is convenient because email addresses ignore case in practice. Spaces do count, which is why TRIM is worth using.
Does it work with lists of thousands of rows?
Yes. COUNTIF and VLOOKUP handle tens of thousands of rows without trouble, although calculation may take a little longer. If the workbook gets slow, copy the results and paste them as values when you’re done.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
