Sales pipeline tracking is one of the most common Excel use cases in any B2B company. The right workbook replaces the spreadsheet that the team is already using - the one with 15 tabs, three pivot tables, and a different number from the CRM. The eight columns below are the structure, the five metrics are the output, and the five stages are the probability table.
The eight columns
Deal name
The deal identifier. The senior uses a unique name that includes the account (e.g. "Acme - Dashboard Project"). The unique name is the key for any downstream pivot or summary.
Account
The customer or prospect name. The senior uses a separate Account column so the same account can have multiple deals. The account is the key for account-level summaries.
Owner
The sales rep responsible. The senior uses the rep's full name (not initials) for clarity. The owner is the key for rep-level forecasts and quota tracking.
Stage
The pipeline stage - Prospecting, Qualified, Proposal, Negotiation, Closing, Closed-Won, Closed-Lost. The senior uses a Data Validation drop-down for the stage. The stage drives the probability and the forecast.
Value
The expected revenue from the deal. The senior uses annual contract value (ACV) for SaaS, total contract value (TCV) for services, deal size for transactional. The value is the input to the weighted pipeline.
Probability
The win rate at the current stage. The senior uses a stage probability table on a hidden tab and XLOOKUP for the rate. The probability is the multiplier for the weighted pipeline.
Expected close
The date the deal is expected to close. The senior uses this for the time-based forecast (deals expected to close in Q1, Q2, etc.).
Actual close
The date the deal actually closed (or empty if still open). The senior uses this for the win/loss analysis and the forecast accuracy calculation.
The five stages and the probability table
Prospecting
10%Initial outreach. The deal is in the CRM but the prospect has not engaged. The senior uses 10% as the baseline win rate.
Qualified
25%The prospect has confirmed need, budget, and timeline. The senior uses 25% based on the historical conversion at this stage.
Proposal
50%A proposal has been sent. The senior uses 50% - the deal is real but the close is not certain.
Negotiation
75%Commercial terms are being discussed. The senior uses 75% - the close is likely but the value is uncertain.
Closing
90%The contract is in legal review or signature pending. The senior uses 90% - the close is expected in the period.
The six metrics
Pipeline coverage
Open pipeline / Quota
3x for enterprise, 4x for mid-market, 5x for SMB. Below 2x means the team will miss the quarter.
Weighted pipeline
SUM(value × probability) by stage
Should equal the forecast for the period. If it does not, the probability table is wrong.
Win rate
Closed-Won deals / Closed deals
Above 25% for SMB, above 20% for mid-market, above 15% for enterprise. Below 10% is a quality problem.
Average deal size
Total Closed-Won value / Closed-Won count
Track by quarter. An upward trend is good (selling larger deals). A downward trend needs investigation.
Sales cycle
AVG(actual close - deal created) for Closed-Won
Track by quarter. A shortening cycle is a leading indicator of efficiency. A lengthening cycle is a leading indicator of risk.
Forecast accuracy
1 - ABS(forecast - actual) / actual
Above 80% is good. Below 60% means the process is not working. The senior tracks this by rep and by month.
How the senior ships it
The senior ships the pipeline workbook in one day. The pattern: the master deal list on the main tab, the stage probability table on a hidden tab, a Pivot Table summary by stage, a forecast sheet by rep, and a forecast accuracy tracker. The team is trained in 30 minutes. The CRM integration is optional - for a 20-rep team, manual entry is fine. For a 50-rep team, the senior wires the workbook to the CRM via Power Query and the pipeline becomes real-time.
