How to Build a Sales Pipeline in Excel That Stays Clean

An Excel sales pipeline that stays clean needs six things:

1. One table with one row per deal.
2. Five stages, each with a written exit rule.
3. A probability for each stage, calculated from your own closed deals.
4. A weighted value for each deal: deal size times that probability.
5. A next step and a next step date on every open row.
6. A weekly cleanup.

Decide the stop rule before you start. When two people work the same deals, or replies come from more inboxes than one person can read, the pipeline moves to a CRM set up for outbound.

The stage split matters more than any formula. Here are the numbers from one 35-day campaign my team ran for an IT company in Germany that sells projects of about EUR 250,000: 712 replies, 59 interested prospects, 34 booked calls, 28 calls that actually happened and 5 contracts worth about EUR 1,250,000.

Two things in those numbers shape the sheet. Only 59 of the 712 replies were real prospects, so the pipeline starts at "interested", not at "replied". And 6 of the 34 booked calls never happened. A sheet with one "meeting" stage would have shown 34. That is how a pipeline looks healthy while nothing closes.

What should a small B2B pipeline in Excel track?

Use one table. Do not make a tab per rep or a tab per stage.

Select your header row and data, then choose Home > Format as Table. Excel tables add a header row with filters, calculated columns that apply one formula to the whole column, and structured references such as [@Value] in place of cell addresses (Microsoft Support). Name the table tblDeals.

These are the columns you need, and no more:

Build the Stage, Source and Lost at dropdowns with Data > Data Validation > List. That stops people from typing "held", "Held " and "Call done" for the same stage (Microsoft Support).

Leave contacted counts, open rates and message history in your sending tools.

Keep personal data thin too. Under GDPR, personal data must be "adequate, relevant and limited to what is necessary" for its purpose (Regulation (EU) 2016/679, Article 5(1)(c)). A name, a title and a work email cover a pipeline. Notes about someone's family do not belong in it. This is not legal advice.

What are the five stages, and when does a deal move?

Define each stage by an event that either happened or did not. "Feels warm" is not an exit rule.

A polite reply is not stage 1. The lead has to be qualified before it gets a calendar slot.

I treat booking a call and closing a deal as separate work, so I never let one stage stand for both. Each month, count booked calls with COUNTIFS on the Booked on column. For August 2026 that is =COUNTIFS(tblDeals[Booked on],">="&DATE(2026,8,1),tblDeals[Booked on],"<"&DATE(2026,9,1)). Swap in the Held on column to count held calls. Held divided by booked is your show rate. When it drops, remember that a booked call is worth little until it closes.

How do you set stage probabilities without guessing?

Template percentages describe someone else's buyers. Calculate your own instead.

Make a second table, tblStages, with three columns: Order (1 to 4), Stage, and Probability. A won deal passed through every stage. A lost deal passed through every stage up to the one recorded in Lost at. So the chance of winning from a given stage is:

won deals / (won deals + deals lost at that stage or later)

In Excel, the Probability column of tblStages is:

=COUNTIFS(tblDeals[Stage],"Won")/(COUNTIFS(tblDeals[Stage],"Won")+COUNTIFS(tblDeals[Lost at],">="&[@Order]))

Here is a made-up history of 11 closed deals: 4 won, 3 lost at Interested, 1 lost at Booked, 2 lost at Held and 1 lost at Proposal. The formula gives:

• Interested: 4/11 = 36%
• Booked: 4/8 = 50%
• Held: 4/7 = 57%
• Proposal: 4/5 = 80%

Use XLOOKUP, which returns the matching value from another table (Microsoft Support), to pull those percentages into tblDeals:

=XLOOKUP([@Stage],tblStages[Stage],tblStages[Probability],0)

The last argument returns 0 instead of #N/A when a new row has no stage yet.

Weighted is then =[@Value]*[@Probability]. Add Won and Lost rows to tblStages and type 100% and 0% over the formula there, so closed rows return a number too.

Treat the percentages as rough until a few dozen deals have closed. And never delete the losses. Founders see the few contracts they signed. I see the prospects they lost. That is where these percentages come from. Recalculate them every quarter.

How do you keep the sheet from rotting every week?

A deal is stale when it is still open and either the next step is empty or the next step date has passed:

=AND([@Stage]<>"Won",[@Stage]<>"Lost",OR([@[Next step]]="",[@[Next step date]]<TODAY()))

Every Monday, filter Stale to TRUE. Each of those rows leaves the meeting with one of two things:

• a new next step and a date, or
• Stage set to Lost, with Lost at filled in.

That pass is worth the time. Ebsta, a sales software vendor, reported in its H1 2024 benchmarks that opportunities updated weekly were 17% likelier to finish as closed won (Ebsta). Treat it as the vendor's own figure.

Do not kill slow deals. Buyers do not decide fast in every space, and some pipelines are only getting started, so a deal can sit at Held for a long time and still be alive. A dated next step keeps that deal honest, and an empty one is how it quietly disappears. The numbers worth reviewing each week are the ones a weekly outbound report should show.

How do you see the pipeline at a glance?

Build a PivotTable first. When the deal data changes, the PivotTable must be refreshed with Refresh or Refresh All (Microsoft Support).

Put Stage in Rows, and Company (as a count) and Weighted (as a sum) in Values. Add Source as a column, and you can see which channel produces deals that reach Proposal.

For a picture, put the stage counts in two columns (stage, count) and choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar chart > Funnel. The funnel chart needs Excel 2021 or Microsoft 365 (Microsoft Support).

When should you stop using Excel and move to a CRM?

Leads must never get lost. Once a sheet cannot promise that, I move the pipeline into a CRM. Which CRM you pick matters much less than making the move.

Volume can reach that line quickly. Once replies arrive from several LinkedIn profiles and mailboxes, I put an inbox manager on them so nothing gets missed. One spreadsheet can't keep up with replies spread across that many accounts. If someone else should handle those replies for you, that is the job of appointment setting.

Spreadsheets also break quietly. Ray Panko's review of fifteen years of spreadsheet research found that spreadsheet errors are common and non-trivial. In nine experiments where people built spreadsheets alone, 1.1% to 5.6% of cells in the finished sheet were wrong on average. That is a few bad cells in every hundred, before anyone is rushing through replies.

What does a finished example look like?

Four made-up open deals, using the calibrated probabilities above:

The open pipeline is $205,000, and the weighted forecast is $125,100 at the rounded percentages shown. Excel, using the exact fractions, will show about $125,331. Harbor Steel Frames shows as stale, because its next step date has passed. It gets fixed before anything else on Monday.

The seven-step build

Build the sheet so a stale deal cannot hide, and move it to a CRM the day a second person needs to edit it.

1. Create tblDeals with the columns listed above.
2. Add dropdowns for Stage, Source and Lost at.
3. Create tblStages and add the calibration formula.
4. Add the Probability, Weighted and Stale formulas.
5. Build one PivotTable by stage and source.
6. Put the Monday cleanup in your calendar.
7. Write the stop rule at the top of the sheet.

Spreadsheet getting crowded as replies come in? Book a call and share your column list. I will tell you what to cut and when I would move the pipeline into a CRM.