Blog · Board and management reporting · Sales teams
Build a sales leaderboard that compares attainment and growth alongside book size. Use Excel formulas to rank reps, handle ties, and reconcile team results without averaging individual percentages.
A leaderboard dashboard compares sales attainment and growth fairly only when the targets, time periods, and assigned books are comparable. Rank a defined measure, keep the starting base visible, and check the team total before turning the ranking into a coaching decision.
Choose one revenue basis: recognized revenue, invoiced sales, or credited bookings. Match the target to that basis. Use the same quarter and cutoff date for every rep, and keep credits and reversals with the sales they adjust.
Bring three exports: sales transactions with unique IDs and credited owners, approved targets by rep and period, and the opening account roster. Add the comparable prior-year quarter for growth. If the review is partway through a quarter, compare the same elapsed portion and label progress against the full-quarter target clearly.
Split new-business reps, renewal specialists, and ramping reps into comparable groups. An approved target is a useful denominator, but dividing by it does not prove the target was equitable. Check territory potential and inherited opportunities separately. The broader sales dashboard examples cover which measures belong with each reader; this page builds the comparison behind a rep ranking.
Put rep IDs in column A, approved quarterly target in B, current revenue in C, comparable prior-year revenue in D, and opening assigned account count in E. Use stable IDs rather than display names.
On a sheet named Sales, assume credited rep ID is in B, revenue in C, and period label in D. If the review period is entered in L1, calculate current revenue in C2 with:
=SUMIFS(Sales!$C$2:$C$5000,Sales!$B$2:$B$5000,$A2,Sales!$D$2:$D$5000,$L$1)
Fill down and extend the ranges to the full export. Microsoft's SUMIFS documentation describes how it sums values satisfying multiple criteria. Check that shared deals carry allocated revenue, rather than the full deal value repeated for every credited rep.
The worked example is a completed quarter for one US sales team. All four reps have the same role and full-quarter eligibility, and account ownership is unchanged between the comparison periods.
| Rep | Target (USD) | Current revenue (USD) | Prior-year revenue (USD) | Opening accounts |
|---|---|---|---|---|
| Alex | 100,000 | 90,000 | 80,000 | 40 |
| Blair | 60,000 | 66,000 | 50,000 | 20 |
| Casey | 40,000 | 44,000 | 40,000 | 10 |
| Devon | 80,000 | 76,000 | 70,000 | 35 |
| Total | 280,000 | 276,000 | 240,000 | 105 |
These occupy rows 2 through 5, with totals in row 6. Alex has the most revenue, but also the largest target and account count. Raw revenue alone does not answer who delivered most effectively against the assigned expectation.
Use F for attainment, G for growth, H for revenue per opening account, and I for the dollar gap to target:
F2: =IF(B2>0,C2/B2,"")
G2: =IF(D2>0,(C2-D2)/D2,"")
H2: =IF(E2>0,C2/E2,"")
I2: =C2-B2
Fill down. Blank means the comparison is unavailable, not zero performance. Revenue per opening account gives book context, but account size and buying opportunity still differ. It is not a universal productivity score.
| Rep | Attainment | Revenue growth | Revenue per opening account (USD) | Gap to target (USD) |
|---|---|---|---|---|
| Alex | 90.0% | 12.5% | 2,250.00 | -10,000 |
| Blair | 110.0% | 32.0% | 3,300.00 | 6,000 |
| Casey | 110.0% | 10.0% | 4,400.00 | 4,000 |
| Devon | 95.0% | 8.6% | 2,171.43 | -4,000 |
Percentages are rounded to one decimal place and per-account amounts to cents; calculations keep full precision. Blair and Casey have equal attainment, but Blair has stronger growth. Casey has more revenue per account, which may reflect account potential as much as selling effort.
Keep these separate rather than inventing a weighted score after seeing the result. The growth dashboard explains whether book growth comes from new customers or movement within existing customers.
Enter this in J2 and fill down:
=IF(ISNUMBER(F2),RANK.EQ(F2,$F$2:$F$5,0),"")
The descending ranking gives Blair and Casey rank 1, Devon rank 3, and Alex rank 4. Microsoft documents RANK.EQ as assigning equal values the same rank and skipping subsequent positions accordingly. Rank unrounded values; two figures displayed as equal after rounding may differ underneath.
Sort the display by rank, then rep ID for a stable order within ties. The second sort changes the display order only. It does not create a performance difference. A separate growth ranking can support a different coaching discussion without silently changing the attainment ranking.
The team measure divides totals:
=SUM(C2:C5)/SUM(B2:B5)
=SUM(C2:C5)/SUM(D2:D5)-1
=SUM(I2:I5)-(SUM(C2:C5)-SUM(B2:B5))
These return 98.6% attainment, 15.0% growth, and a zero gap-check difference. The team is $4,000 below target: 276,000 − 280,000. Averaging individual attainment instead gives 101.25%, wrongly suggesting the team exceeded target because smaller targets receive equal weight.
Match the $276,000 to the filtered sales export, the $280,000 to approved targets, and the opening account count of 105 to the roster. Investigate unassigned transactions and account transfers before publication. For broader measures around the ranking, see sales KPIs for B2B sales teams.
Bring revenue, target, and account-assignment files to Covirage: it maps the columns, its tools compute and check the comparisons, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; account creation takes about a minute.
See sales insights for the supporting context. Covirage is not affiliated with Microsoft.
No single metric makes every comparison fair. Quota attainment is useful when targets reflect opportunity and role, while growth needs a visible starting base. Compare similar reps and show both measures alongside account assignments, tenure, and known exceptions.
Yes, when their underlying results are equal. Preserve the shared rank or publish a secondary ranking rule before the period starts. Do not break ties using hidden decimals in one review and another measure in the next.
Show them in a ramp cohort with the approved ramp target and eligible dates. Leave unavailable growth comparisons blank. A rep who inherited no opening book should not receive an artificial growth percentage based on a substituted denominator.
Use the approved compensation plan and its crediting rules for pay decisions. A coaching leaderboard may use different timing or revenue definitions. Reconcile those differences explicitly before anyone interprets the displayed rank as a commission entitlement.