Blog · Board and management reporting · Sales teams
Build a reusable CRM review workbook from opportunity and activity exports. This layout includes input columns, Excel formulas, worked rows, and checks that prevent activity records from multiplying pipeline value.
A dashboard CRM template organizes opportunity and activity exports into a repeatable sales review. Keep the input records separate, define the reporting window once, and calculate each summary from its correct source. The layout below is a workbook specification you can build from your exports.
Create worksheets named Controls, Opportunities, Activities, and Review. Preserve the original exports separately so you can explain any exclusions or corrections. The broader CRM dashboard guide covers the management questions; this page specifies the workbook cells and checks.
Use these controls for an illustrative US supplier reviewing October 2026:
| Controls cell | Purpose | Excel value |
|---|---|---|
| B1 | Reporting start, inclusive | =DATE(2026,10,1) |
| B2 | Reporting end, exclusive | =DATE(2026,11,1) |
| B3 | Opportunity snapshot date | =DATE(2026,10,23) |
The exclusive end includes timestamps throughout the final reporting day. Keep the opportunity snapshot label beside the review: a current pipeline balance and a month's completed activities describe different time bases. Freeze the source exports if you need to reproduce this review later.
Use one row per opportunity ID, with headings in row 1. Map source statuses into Open, Won, or Lost, while preserving the more detailed stage in its own column. Actual close dates are blank for open opportunities.
All dates below are in 2026, and all amounts are USD. These records occupy rows 2 through 6.
| A: Opportunity ID | B: Account | C: Owner | D: Status | E: Stage | F: Amount (USD) | G: Expected close | H: Actual close |
|---|---|---|---|---|---|---|---|
| O101 | Acorn | East | Open | Proposal | 60,000 | October 30 | |
| O102 | Birch | East | Open | Qualified | 40,000 | November 15 | |
| O103 | Cedar | West | Won | Closed won | 30,000 | October 12 | October 12 |
| O104 | Dune | West | Lost | Closed lost | 20,000 | October 18 | October 18 |
| O105 | Elm | West | Open | Proposal | 50,000 | October 28 |
Store dates as actual spreadsheet dates and amounts as numbers. Confirm whether an amount means contract value, annual value, or another basis before summing it. Do not mix those bases simply because they use the same currency.
For records from multiple sales teams, agree on owner mappings before loading. A repeatable review also needs an explicit rule for reassigned opportunities: this example uses the owner shown in the snapshot, not an inferred historical owner.
Use one row per activity ID. An opportunity ID can appear repeatedly here without creating duplicate opportunity records. These activities occupy rows 2 through 7; dates are again in 2026.
| A: Activity ID | B: Opportunity ID | C: Owner | D: Activity date | E: Type | F: Status |
|---|---|---|---|---|---|
| A501 | O101 | East | October 5 | Meeting | Completed |
| A502 | O102 | East | October 8 | Call | Completed |
| A503 | O103 | West | October 10 | Meeting | Completed |
| A504 | O104 | West | October 16 | Call | Completed |
| A505 | O105 | West | October 20 | Meeting | Completed |
| A506 | O101 | East | October 25 | Meeting | Planned |
Use the completion date for completed activities. Scheduled dates belong to planned work and should not count as evidence that a meeting happened. The planned activity remains visible in the input for review but is excluded from completed-work measures.
An activity owner can differ from the opportunity owner in real exports. This template credits completed work to its activity owner and pipeline to its opportunity owner. Record that distinction rather than silently replacing one field with the other.
Place East in Review A2 and West in A3. Use the column layout below. Microsoft documents SUMIFS for summing values that meet several criteria, which supplies the owner, status, and date filters here.
Open pipeline in B2:
=SUMIFS(Opportunities!$F$2:$F$10000,Opportunities!$C$2:$C$10000,$A2,Opportunities!$D$2:$D$10000,"Open")
Open pipeline expected to close during the reporting month in C2:
=SUMIFS(Opportunities!$F$2:$F$10000,Opportunities!$C$2:$C$10000,$A2,Opportunities!$D$2:$D$10000,"Open",Opportunities!$G$2:$G$10000,">="&Controls!$B$1,Opportunities!$G$2:$G$10000,"<"&Controls!$B$2)
Won value with an actual close date in the month in D2:
=SUMIFS(Opportunities!$F$2:$F$10000,Opportunities!$C$2:$C$10000,$A2,Opportunities!$D$2:$D$10000,"Won",Opportunities!$H$2:$H$10000,">="&Controls!$B$1,Opportunities!$H$2:$H$10000,"<"&Controls!$B$2)
Completed activities in E2:
=COUNTIFS(Activities!$C$2:$C$10000,$A2,Activities!$F$2:$F$10000,"Completed",Activities!$D$2:$D$10000,">="&Controls!$B$1,Activities!$D$2:$D$10000,"<"&Controls!$B$2)
Completed meetings in F2:
=COUNTIFS(Activities!$C$2:$C$10000,$A2,Activities!$F$2:$F$10000,"Completed",Activities!$E$2:$E$10000,"Meeting",Activities!$D$2:$D$10000,">="&Controls!$B$1,Activities!$D$2:$D$10000,"<"&Controls!$B$2)
Microsoft's COUNTIFS reference confirms that all supplied criteria must be met. Calculate closed wins in G2 and closed losses in H2 from opportunities, rather than counting their related activities:
=COUNTIFS(Opportunities!$C$2:$C$10000,$A2,Opportunities!$D$2:$D$10000,"Won",Opportunities!$H$2:$H$10000,">="&Controls!$B$1,Opportunities!$H$2:$H$10000,"<"&Controls!$B$2)
=COUNTIFS(Opportunities!$C$2:$C$10000,$A2,Opportunities!$D$2:$D$10000,"Lost",Opportunities!$H$2:$H$10000,">="&Controls!$B$1,Opportunities!$H$2:$H$10000,"<"&Controls!$B$2)
Win rate in I2 is:
=IF(G2+H2=0,"",G2/(G2+H2))
Fill the formulas down for West. Expand all source ranges together when necessary, and reject exports that exceed the covered ranges.
| Owner | Open pipeline (USD) | Due this month (USD) | Won value (USD) | Completed activities | Meetings | Closed wins | Closed losses | Win rate |
|---|---|---|---|---|---|---|---|---|
| East | 100,000 | 60,000 | 0 | 2 | 1 | 0 | 0 | N/A |
| West | 50,000 | 50,000 | 30,000 | 3 | 2 | 1 | 1 | 50.0% |
| Total | 150,000 | 110,000 | 30,000 | 5 | 3 | 1 | 1 | 50.0% |
Sum the amount and count columns for the total. Recalculate total win rate as total wins divided by total wins plus losses; do not average owner percentages. Rates are displayed to one decimal place. East's missing denominator means no closed outcomes, not poor conversion.
The opportunity export reconciles as $150,000 open + $30,000 won + $20,000 lost = $200,000. Of the open balance, $110,000 is due in October and $40,000 is due later. The activity export contains five completed records and one planned record; the review must not count the planned meeting.
Confirm every record ID is unique within its own input, every owner appears in the review, and every activity's opportunity ID resolves to an opportunity or a documented exception. Test duplicates in a spare input column with =COUNTIFS(A:A,A2) and investigate results greater than one.
Closed-won value is a commercial measure, not automatically revenue. For the forecast implications of individual changes, use the deal-by-deal forecast bridge. For combining the surrounding business context, see sales intelligence from existing files.
Covirage is not affiliated with Microsoft.
Bring your opportunity and activity exports with their date and status definitions. Covirage maps the columns, and its tools compute and check the supplied figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own export.
See Excel analysis for the supporting workflow.
Yes. Use separate worksheets for opportunities, activities, controls, and the review. The formulas below use SUMIFS and COUNTIFS to summarize source rows by owner, status, and date. Paste fresh exports into the input sheets and check the ranges before reviewing results.
Keep them separate when an opportunity can have several activities. Joining each activity to its opportunity repeats the opportunity amount and can inflate pipeline totals. Summarize the two exports independently, using opportunity IDs to investigate detail when necessary.
Closed-won value measures the CRM outcome under your sales definitions. It may represent a contract, a booking, or another commercial amount. Use the finance ledger and its recognition policy when the review needs accounting revenue rather than won opportunity value.