Build a Sales Pipeline Tracker in Excel
By ExcelReady TeamUpdated 7 min read
The short answer
A sales pipeline tracker in Excel is a table with one row per deal: owner, stage, value, win probability and expected close date. Add a weighted value (value times probability), then summarise with COUNTIFS and SUMIFS by stage and owner. Use a drop-down for the stage and conditional formatting to flag deals past their close date.
Key takeaways
- Keep one row per deal and one fact per column: owner, stage, value, probability, close date.
- Weighted value = deal value × probability, so a 50% deal counts for half its value.
- SUMIFS with "<>Won" and "<>Lost" totals only the deals that are still open.
- A drop-down list for the stage stops typos that would break your COUNTIFS and SUMIFS totals.
- A formula rule in conditional formatting can highlight open deals whose close date has passed.
On this page+
- What a sales pipeline tracker in Excel needs
- Step 1: Set up the deal list
- Step 2: Add a stage drop-down and probabilities
- Step 3: Calculate the weighted value
- Step 4: Summarise the pipeline by stage with COUNTIFS and SUMIFS
- Step 5: Add win rate and deals closing this month
- Step 6: Highlight overdue deals with conditional formatting
- Practise it
A sales pipeline tracker in Excel shows every open deal, what stage it is at and how much it is likely to be worth. You need one table of deals and a small summary built with COUNTIFS and SUMIFS. This guide builds the whole tracker in six steps on sample data, so you can copy it for your own team or use it as a portfolio project for a sales operations job.
What a sales pipeline tracker in Excel needs#
A pipeline tracker is a list of deals with one row per deal and one fact per column. Keep it simple: every extra column is something people must keep up to date.
| Column | What it holds | Example |
|---|---|---|
| Deal | Customer or deal name | Cedar Foods |
| Owner | Salesperson responsible | Ana |
| Stage | Where the deal is now | Proposal |
| Value | Expected deal value | 20,000 |
| Probability | Chance of winning, from the stage | 50% |
| Close date | When you expect to sign | 28 Nov 2026 |
| Weighted | Value × probability | 10,000 |
The stage is the heart of the tracker. This guide uses six: Lead, Qualified, Proposal, Negotiation, Won and Lost.
Step 1: Set up the deal list#
Type the headers in row 1 and one deal per row below. Here is the sample data, in A1:G9:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Deal | Owner | Stage | Value | Probability | Close date | Weighted |
| 2 | Acme Ltd | Ana | Lead | 12,000 | 10% | 15 Dec 2026 | 1,200 |
| 3 | Brightway | Tom | Qualified | 8,500 | 25% | 20 Nov 2026 | 2,125 |
| 4 | Cedar Foods | Ana | Proposal | 20,000 | 50% | 28 Nov 2026 | 10,000 |
| 5 | Delta Print | Raj | Negotiation | 15,000 | 75% | 10 Nov 2026 | 11,250 |
| 6 | Evergreen | Tom | Proposal | 6,000 | 50% | 5 Dec 2026 | 3,000 |
| 7 | Fairline | Raj | Won | 9,000 | 100% | 30 Oct 2026 | 9,000 |
| 8 | Granite Co | Tom | Lost | 11,000 | 0% | 22 Oct 2026 | 0 |
| 9 | Harbor Tech | Tom | Negotiation | 18,000 | 75% | 18 Nov 2026 | 13,500 |
Then press Ctrl+T (Mac: ⌘T) to turn it into an Excel Table. New deals added at the bottom will pick up every formula and format automatically.
Step 2: Add a stage drop-down and probabilities#
A drop-down list makes sure everyone types the stage the same way. "Proposal" and "proposal sent" look similar to a person but are different values to a formula.
- Put the stage list in I2:I7 (Lead, Qualified, Proposal, Negotiation, Won, Lost) and the matching probabilities in J2:J7 (10%, 25%, 50%, 75%, 100%, 0%).
- Select C2:C9 and go to Data > Data Validation.
- Under Allow, choose List, set Source to
=$I$2:$I$7, and click OK.
Now fill the probability from the stage instead of typing it. In E2, use a lookup and copy it down:
=XLOOKUP(C2,$I$2:$I$7,$J$2:$J$7)XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use =VLOOKUP(C2,$I$2:$J$7,2,FALSE).
Step 3: Calculate the weighted value#
The weighted value is the deal value multiplied by the probability. It turns a list of hopeful numbers into a realistic forecast. In G2, type =D2*E2 and fill it down.
Add up the weighted values of open deals only:
=SUMIFS(G2:G9,C2:C9,"<>Won",C2:C9,"<>Lost")The result is 41,075. The full open pipeline, before weighting, is =SUMIFS(D2:D9,C2:C9,"<>Won",C2:C9,"<>Lost"), which returns 79,500. The <> means "not equal to", so the formula skips closed deals.
Step 4: Summarise the pipeline by stage with COUNTIFS and SUMIFS#
A summary table answers the first question every manager asks: how many deals are at each stage, and how much are they worth? Next to the stage list in I2:I7, add two formulas and fill them down:
=COUNTIFS($C$2:$C$9,I2)
=SUMIFS($D$2:$D$9,$C$2:$C$9,I2)| Stage | Deals | Value |
|---|---|---|
| Lead | 1 | 12,000 |
| Qualified | 1 | 8,500 |
| Proposal | 2 | 26,000 |
| Negotiation | 2 | 33,000 |
| Won | 1 | 9,000 |
| Lost | 1 | 11,000 |
The $ signs lock the data ranges so they don't move as you fill down, while I2 changes to I3, I4 and so on. Add more pairs for more conditions. Ana's open pipeline is 32,000:
=SUMIFS(D2:D9,B2:B9,"Ana",C2:C9,"<>Won",C2:C9,"<>Lost")The guide to COUNTIFS and SUMIFS with multiple conditions explains the argument order, and the SUMIFS function guide and COUNTIFS function guide are quick references.
Step 5: Add win rate and deals closing this month#
Two more numbers make the summary useful. The win rate is won deals divided by closed deals:
=COUNTIF(C2:C9,"Won")/(COUNTIF(C2:C9,"Won")+COUNTIF(C2:C9,"Lost"))Here that is 1 ÷ 2 = 50%. Format the cell as a percentage.
The value expected to close in November 2026 uses a date range in SUMIFS:
=SUMIFS(D2:D9,F2:F9,">="&DATE(2026,11,1),F2:F9,"<="&DATE(2026,11,30))The result is 61,500 (Brightway, Cedar Foods, Delta Print and Harbor Tech). DATE builds the date, and & joins it to the operator. Put these numbers on a KPI summary at the top of the sheet, or chart them in a sales dashboard.
Step 6: Highlight overdue deals with conditional formatting#
An open deal whose close date has passed needs attention. A formula rule can colour the whole row.
- Select A2:G9.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter this formula, choose a light red fill and click OK:
=AND($F2<TODAY(),$C2<>"Won",$C2<>"Lost")The $ before F and C keeps every cell in a row looking at the same columns. TODAY() updates each day, so the highlights stay current. A second rule with =$C2="Won" and a green fill makes wins easy to spot.
The same structure works for other trackers: one row per item, a status column with a drop-down, and COUNTIFS on top. See the leave tracker and the attendance tracker for two more examples.
Practise it#
The summary formulas are the part employers test. Practise COUNTIFS and SUMIFS in a live spreadsheet: you build multi-condition totals with locked ranges, and each formula is checked instantly. Then colour rows with a formula rule in the conditional formatting lesson. Start with Tom's open pipeline in the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is Tom's open pipeline? In H2, use SUMIFS to total Value (D2:D9) where Owner is Tom and Stage is neither Won nor Lost.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Value | ||||||||
| 2 | Tom open pipeline | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Tip: Start with the Value range, then add three pairs: Owner = "Tom", Stage "<>Won" and Stage "<>Lost".
Practise this in a lesson
Frequently asked questions
How do I create a sales pipeline in Excel?
List each deal in a row with columns for owner, stage, value, probability and close date. Add a weighted value column, then build a summary with COUNTIFS and SUMIFS for each stage.
How do you calculate a weighted pipeline in Excel?
Multiply each deal's value by its probability of closing, then add the results. For open deals only, use SUMIFS on the weighted column with the conditions "<>Won" and "<>Lost".
What stages should a sales pipeline have?
A simple pipeline uses Lead, Qualified, Proposal, Negotiation, Won and Lost. Use whatever stages your team already uses, and keep the list short so the summary stays readable.
How do I calculate the win rate in Excel?
Divide the number of won deals by the number of closed deals: =COUNTIF(C2:C9,"Won")/(COUNTIF(C2:C9,"Won")+COUNTIF(C2:C9,"Lost")). Format the cell as a percentage.