# Lead List Template: The Columns Every B2B List Needs (Excel)

> A lead list template for Excel you can rebuild in ten minutes: the columns every B2B list needs, the status values to use, phone and domain key formulas, an opt-out sheet and a pipeline count with COUNTIF.

Published 11 October 2026 · About Rovero · 9 min read
Source: https://leadsadd.com/blog/lead-list-template-excel

A lead list template is a spreadsheet with a fixed set of columns for every business you plan to contact: who they are, how to reach them, where they stand and who is working on them. The template in this guide is an Excel workbook with three sheets, Leads, Opt-out and Summary, which you can rebuild from this page in about ten minutes. It gives the columns with an example for each, the status values, two key formulas that catch repeats, a pipeline count with COUNTIF, and the signs that it is time to move to a CRM.

## The short answer

1. Make a Leads sheet with the 16 working columns in the table below, one row per business.
2. Use a fixed list of status values, entered through a drop-down, and no others.
3. Add a phone key and a domain key column so repeats show up at once.
4. Keep an Opt-out sheet and check every row against it before a campaign.
5. Count the pipeline with COUNTIF on a Summary sheet, and share one master file.

## What columns does a lead list template need?

Sixteen working columns plus three helper columns (phone key, domain key and opt-out check) at the right-hand end. The column letters assume this order on the Leads sheet.

| Column | Example value | Why |
| --- | --- | --- |
| A. ID | 0001 | Keeps its number when sorted or shared |
| B. Business name | Example Interiors LLC | As published, with the legal ending |
| C. Trade | Interior fit-out | Filter one trade for a campaign |
| D. Area | Business Bay | The unit you search, call and report by |
| E. City | Dubai | Needed once the list covers two cities |
| F. Phone | +971 4 123 4567 | The main published number |
| G. Mobile | +971 50 123 4567 | For WhatsApp, with the country code |
| H. Email | info@example-interiors.com | The published address; one per row |
| I. Website | example-interiors.com | The duplicate key and where to verify |
| J. Contact and role | Owner | Blank until a conversation gives a name |
| K. Source | Area search Oct 2026 | Where the row came from |
| L. Status | New | One value from the fixed list below |
| M. Owner | Rep A | The person working the row |
| N. Last contacted | 2026-10-11 | Year-month-day so dates sort |
| O. Next action | Call back 14 Oct | What happens next and when |
| P. Notes | Asked for a quote in November | Short, dated lines, newest first |
| Q. Phone key | 97141234567 | Formula; digits only with country code |
| R. Domain key | example-interiors.com | Formula; lower-case domain |
| S. Opt-out check | STOP or blank | Formula; matches the Opt-out sheet |

Freeze the top row, turn on filters, and set the ID column to text so leading zeros stay.

## Which status values should you use?

Status is where most lists fall apart, because each person invents their own words. Fix the list and enter it through a drop-down (Data, Data Validation, List):

- **New:** not yet contacted.
- **Contacted:** a call, email or message sent, no reply yet.
- **Replied:** the business answered; the conversation is open.
- **Qualified:** it fits and has a need.
- **Meeting:** a date is in the diary.
- **Proposal:** a quote has been sent.
- **Won** and **Lost:** a customer, or a clear no.
- **Not now:** interested later; put the month in Next action.
- **Wrong number:** the phone did not reach the business.
- **Opted out:** also added to the Opt-out sheet.

## How do you add a phone key and a domain key?

Repeats hide behind formatting: +971 4 123 4567 and 04-123-4567 are the same line. The phone key in column Q strips spaces, brackets, dashes and the plus sign, then swaps a leading 0 or 00 for the country code (971 here; 966 for Saudi Arabia, 44 for the UK):

```
=LET(d,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"+","")," ",""),"-",""),"(",""),")",""),IF(LEFT(d,2)="00",MID(d,3,20),IF(LEFT(d,1)="0","971"&MID(d,2,20),d)))
```

The domain key in column R lower-cases the website, removes http, https and www, and cuts at the first slash:

```
=LOWER(TEXTBEFORE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I2,"https://",""),"http://",""),"www.","")&"/","/"))
```

Both need Excel 365 or 2021. To flag a repeat, add a count in a spare column:

```
=IF(COUNTIF(Q:Q,Q2)>1,"repeat","")
```

Keep the fullest row. Our guide on how to [remove duplicate leads](/blog/remove-duplicate-leads) covers branches and shared reception numbers, which are not repeats.

## How do you keep an opt-out sheet?

The second sheet, Opt-out, has five columns: Date, Channel, Phone key, Email and Domain. When a business asks you to stop, add a row the same day with all three identifiers, so a second row for the same business under another spelling is still caught. Column S checks each lead against it:

```
=IF(OR(COUNTIF('Opt-out'!C:C,Q2)>0,COUNTIF('Opt-out'!E:E,R2)>0),"STOP","")
```

Before any campaign, filter column S for STOP and remove those rows from the send. The sheet is never pruned, and the owner should protect it from edits. UK PECR, the UAE rules on marketing messages from the TDRA, Saudi Arabia's PDPL and the US federal rules for commercial email all expect opt-outs to be honoured. This is general information, not legal advice; check the current rules for the country you send to.

## How do you count your pipeline with COUNTIF?

The third sheet, Summary, reads the Leads sheet. With status in column L, owner in M and last contacted in N:

```
=COUNTIF(Leads!L:L,"New")
=COUNTIF(Leads!L:L,"Contacted")
=COUNTIF(Leads!L:L,"Qualified")
=COUNTIFS(Leads!L:L,"Meeting",Leads!M:M,"Rep A")
=COUNTIFS(Leads!N:N,">="&TODAY()-7)
```

The first three give the shape of the pipeline, the fourth splits a stage by owner, and the fifth counts rows touched in the last seven days, the quickest measure of whether the list is being worked.

## How do you share the template with a small team?

1. **One master file** in a shared folder (OneDrive, SharePoint, Google Drive or a network drive). Nobody keeps a private copy; copies are where opt-outs get lost.
2. **One owner of the structure.** Reps edit their own rows' status, notes and dates; the owner adds columns, keeps the Opt-out sheet and runs the checks in our [lead list hygiene checklist](/blog/lead-list-hygiene-checklist).
3. **Filter by owner.** Each rep filters column M to their name; sorting the whole sheet is for the owner only.
4. **Never delete a row.** Lost, Wrong number and Opted out rows stay, so the business is not added again as New; move old rows to an Archive sheet once a quarter and save a dated backup monthly.

## Worked example: a Jeddah supplier's first 400 rows

Say a wholesaler of cleaning supplies in Jeddah builds its first list: 400 restaurants, clinics and small hotels in Al Hamra, Al Rawdah and Al Salamah, with two reps splitting the rows by area. The phone and domain keys flag 23 repeats on day one, which are merged, and the Opt-out sheet starts with 6 rows from last year's notebook.

Four weeks later the Summary sheet reads, for the example: New 180, Contacted 150, Replied 35, Qualified 18, Meeting 7, Proposal 4, Won 2, Wrong number 9 and Opted out 4. Rows touched in the last seven days: 120 for one rep and 40 for the other, a conversation the owner can now have with numbers.

## When should you move to a CRM?

The template holds up for one or two people and a few thousand rows. Move to a CRM when three or more people edit the list on the same day, when you need the history of every call on each contact rather than a notes cell, or when follow-up tasks are being missed. The columns map onto any CRM's fields, so the move is an import, not a rebuild. Our guide to [lead management](/blog/lead-management) covers the move, and the guide on [how to build a B2B lead list](/blog/how-to-build-a-b2b-lead-list) covers where the rows come from.

## Where Rovero fits

Rovero is B2B lead generation software for Windows. It finds the businesses of a chosen type in a chosen city, area by area, and collects the emails and phone numbers they publish on their listings, websites and social pages. [One list per search](/rovero/features#exports) gives Excel, CSV and JSON files in the columns you choose, and those columns line up with this template: business name, category (your Trade column), address and area, phone numbers, website, rating, emails and extra mobile numbers. The marketing-ready lists put WhatsApp numbers in international format and leave landlines out, which is the Mobile column ready-made. Paste an export into the Leads sheet, fill Source, Status and Owner, and the keys do the rest.

One honest limit: details change over time, so check a lead before an important campaign, and treat the Last contacted and Notes columns as the record of what you confirmed.

## Frequently asked questions

### What should a B2B lead list template include?

Identity columns (ID, business name, trade, area, city), contact columns (phone, mobile, email, website, contact and role), working columns (source, status, owner, last contacted, next action, notes) and helper columns (phone key, domain key, opt-out check). An Opt-out sheet and a Summary sheet complete it.

### Is Excel good enough for a lead list template?

Yes, for one or two people and a few thousand rows. Fixed columns, a status drop-down, two key formulas and one master file cover most small teams.

### How do I stop duplicates in my lead list template?

Add a phone key column (digits only with the country code) and a domain key column (lower-case domain), then flag any key that appears more than once with COUNTIF or a duplicate-values conditional format. Keep the fullest row and copy the extra details into it.

### Where do the rows for the lead list template come from?

The city's business listings, trade directories, exhibitor lists and lead generation software that searches area by area. Paste each batch under the last row, run the keys and the opt-out check, and give every row a Source value.

## Next step

Open Excel, create the three sheets and type the nineteen column headings from the table today; the formulas take five more minutes. When you want rows to fill it, [start a free trial](/register) of Rovero and export your first area search straight into the Leads sheet.