TL;DR:
Decide the columns first, then collect, so every row has the same fields.
Dedupe on a single key such as the email address, after trimming spaces and lowercasing it.
Record the source of every lead so you can see which sources are worth using.
A lead list is just a table of people or companies you might contact. It becomes useful or useless in the first ten minutes, depending on whether you decide the columns before you start filling rows. Messy lists are almost always lists where each person typed whatever came to mind.
What you need before collecting
A one-sentence description of who counts as a good lead, such as "clinics with more than one location in your region".
A list of sources you are allowed to use: your own website enquiries, event sign-ups, public company directories, referrals.
A spreadsheet with a header row and one row per lead. Freeze the top row so the headers stay visible.
Knowledge of the rules on contacting people where you work. Only collect what you have a reasonable basis to use.
Step by step
Fix the columns: Choose them now and do not add new ones halfway. A sensible set: Company, Name, Role, Email, Source, Date added, Status.
Give each column one format: Dates all in the same format, Status chosen from a short list (New, Contacted, Replied, Not interested). Use data validation for Status if your spreadsheet offers it.
Collect from one source at a time: Paste each batch below the last one and fill the Source column for every row in that batch.
Clean the key column: In a helper column, normalise the email:
=LOWER(TRIM(D2)). Copy it down and use this column for matching.Flag duplicates: In the next column, if the cleaned emails are in H2:H200, use
=COUNTIF($H$2:$H$200, H2)>1. TRUE means this email appears more than once.Remove the extras: Sort or filter on TRUE, keep the row with the most information and delete the others. Check a handful by eye before you delete.
Worked example: 50 leads with consistent columns
Say you gathered leads from three sources and pasted them all into one sheet. The numbers below are an example.
Source | Rows pasted | Duplicates removed | Kept |
|---|---|---|---|
Website enquiries | 20 | 0 | 20 |
Event sign-up sheet | 25 | 6 | 19 |
Public directory | 17 | 6 | 11 |
Total | 62 | 12 | 50 |
Check the arithmetic: 20 + 25 + 17 = 62 rows pasted, 0 + 6 + 6 = 12 removed, and 62 − 12 = 50 kept, which also equals 20 + 19 + 11. The table tells you something beyond the total: the directory gave the most duplicates, because many of its contacts were already in your event list.
To produce the Source totals yourself, count rows per source with =COUNTIF(E2:E63, "Public directory") where column E holds the source. Wrong spellings of the source name will make the count miss rows, which is why the Source column should use the same words every time.
A finished row looks like this:
Company,Name,Role,Email,Source,Date added,Status
North Hill Clinic,Dana Ortiz,Operations manager,dana@example.com,Event sign-up sheet,2026-11-12,NewMistakes to avoid
Matching on name instead of email. Two people can share a name, and one person can be spelt two ways.
Forgetting to trim spaces. "dana@example.com " with a trailing space does not match "dana@example.com".
Merging two columns into one, such as putting role and company in the same cell. You cannot sort or filter it later.
Deleting duplicates without looking. Two rows may carry different information, such as one with a phone number.
Leaving out the Source column, then not knowing which channel produced replies.
Keeping the list alive
Update Status as soon as something happens, and add a Last contacted date. Once a month, sort by that date and remove or update the rows that went stale. A list that shrinks to the right people is better than one that only grows.
Choosing sources you can trust
The best leads come from people who already showed interest: enquiries on your own site, sign-ups at events you ran, and introductions from existing contacts. These have a reason to expect your message, which makes replies far more likely than a list of strangers.
Public directories are useful for finding companies but weaker for finding the right person, so expect to do more research per row. Whatever the source, check that the data is current. A role from two years ago may be wrong now, so add a Date added column and treat old rows with suspicion.
Avoid buying lists of email addresses. You do not know where they came from, how old they are, or whether the people agreed to be contacted, and the rules about this differ by place.
Doing it faster
Set up the sheet once and copy it for each new list. Use a filter on the header row to check one column at a time. If you are stuck on a formula, DuckyHelper can see your spreadsheet on screen and talk you through what the formula is doing; it works with whichever spreadsheet app you already use.
Frequently asked questions
Should I use a spreadsheet or a CRM?
A spreadsheet is fine to start and to clean a list. Move to a CRM when several people need to work the same leads at once.
How do I find duplicates when there is no email?
Use a combination that is unlikely to repeat, such as company name plus person name, joined in a helper column with =LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2)), then count that column.
Get started
