How to Calculate Win Rate and Sales Cycle in Dynamics 365

You can calculate your team’s win rate and average sales cycle with a Dynamics 365 export and a few Excel formulas. Build the report manually first, then see how Pipeline Pulse provides the review automatically inside Dynamics 365.

1. Export closed opportunities to Excel

1. In Dynamics 365, open Opportunities and use Edit filters to select (Status = Won OR Status = Lost) AND Actual Close Date in your reporting period. Set the owner or sales-team filter explicitly.
2. Include Opportunity, Status, Created On, Actual Close Date, and Owner. Add Actual Revenue if you also want to compare won value.
3. Select Export to Excel → Static Worksheet, where available. Export all matching records you intend to review, rather than only selected rows or the current page.
4. Open the saved file. Keep an untouched copy, then use a working sheet for your calculations.

For an October report, include deals with Actual Close Date from October 1 through October 31. A deal created in May and won in October belongs in that export. Filtering Created On to October would leave it out and could make the sales cycle look artificially short.

Use the same reporting timezone and date boundaries throughout. Keep one row per opportunity so related activities do not duplicate your counts.

2. Decide what the Excel report will measure

If no deals closed, report win rate as “N/A,” not 0%. If none were won, report the won-deal sales cycle as “N/A.” A revenue-weighted rate answers a different question; label it separately rather than calling both figures win rate.

3. Add the Excel formulas

In the working sheet, select the exported range and choose Insert → Table, confirming that the range has headers. Under Table Design, name the table Deals. Use the column headers Status, Created On, and Actual Close Date exactly as shown below, or adjust the formulas to your exported headers.

Add a column named Cycle Days. In its first data row, enter:

Excel should fill the table column automatically. This calculates whole elapsed days for won deals with valid dates and leaves other rows blank. Confirm that Excel recognizes the exported dates as dates rather than text. Normalize timestamps to the same timezone before subtracting them; if you prefer calendar days, use that convention consistently.

Put these summary formulas in cells outside the table:

These formulas assume the sheet contains only your selected closed-period cohort and the exported status labels are Won and Lost. Use the equivalent labels if your app uses different ones. Check missing or invalid durations separately, and show the count of valid won-deal durations beside the cycle average.

Do not average each salesperson’s win-rate percentage to get the team result. Add the team’s wins and losses first, then calculate the rate from those totals.

4. Check your spreadsheet against a worked example

Illustrative source records for October 2026, with date-only values for easy arithmetic. The open deal below would be excluded from the closed-opportunity export. These are not customer results.

• Win rate: 2 ÷ (2 + 1) = 66.7%, with three closed deals.
• Average won cycle: (30 + 60) ÷ 2 = 45 days, with two won deals.
• Median won cycle: (30 + 60) ÷ 2 = 45 days for this two-record example.

Show the sample size beside the metric. Two wins are too few to establish a reliable performance trend or justify ranking salespeople.

5. Repeat the report for the next review

To compare owners or months, build a PivotTable from the working sheet: count opportunities by Status and Owner for win/loss analysis, and average valid won-deal Cycle Days for duration. Recalculate each group’s win rate from its won and lost counts.

A static worksheet is a snapshot. For the next review, export fresh records, confirm the date and team filters, check the formulas, and rebuild or refresh the summaries. New wins, losses, and corrections in Dynamics 365 do not automatically appear in the saved static export.

That repeated preparation is the work Pipeline Pulse removes from the review.

6. Get the automatic views in Pipeline Pulse

With Pipeline Pulse installed, open Pipeline Insights and select the period and salespeople. The reporting views calculate the results from your CRM data without a separate Excel export or a workbook to maintain.

1. Select Won vs Lost to review closed opportunity counts, values, average deal sizes, and overall win rate.
2. Select Opportunity Sales Cycle to review average days to close and how closing duration changes by month, quarter, or business unit.
3. Use the underlying records to investigate changes before deciding on a coaching action.

Validate the records included in each view before comparing it with a custom report. If the selected cohort, close-date field, or duration convention differs, the numbers can differ even when both calculations are internally consistent. Segment the analysis by comparable sales motions before deciding where to coach.

Accurate statuses and close dates still matter. Pipeline Pulse automates the reporting work; your team interprets the patterns and decides what to do next.

7. Avoid common reporting mistakes

Manager’s review checklist

• Are the period, team, timezone, and date convention explicit?
• Are sample sizes shown beside percentages and averages?
• Does the change remain after separating sales motions and deal sizes?
• Is the mean distorted by a small number of old records?
• Which deal review or coaching action follows from the pattern?

If the issue is stalled open pipeline, continue with stalled and aging opportunities. Use close-date pushback tracking when timing repeatedly changes.

Use historical win rates to challenge the assumptions in your probability-weighted forecast. For open deals needing follow-up, look for relevant account news before choosing the next conversation.