Excel for Sales: Track Pipeline, Targets and Commissions
Practical Excel for sales teams: track pipeline and targets, calculate commissions with tiers, summarise by rep and month, and rank performance.
By Ayoub Farid · · 1 min read
Even with a CRM, sales people end up in Excel: exports to analyse, targets to track, commissions to check. These formulas cover most of it.
Sales by rep and month
Data: Date (A), Rep (B), Product (C), Amount (D). Summary with reps down F and months across G1:R1:
=SUMIFS($D$2:$D$2000,$B$2:$B$2000,$F2,$A$2:$A$2000,">="&G$1,$A$2:$A$2000,"<="&EOMONTH(G$1,0))Each month header is a first-of-month date. See SUMIFS.
Target tracking
- Attainment:
=H2/$I$1(sales ÷ target). See percentage formula. - Status:
=IF(H2>=$I$1,"Hit","Gap: "&TEXT($I$1-H2,"#,##0"))
Commission tiers
With a tier table (minimum sales → rate), sorted ascending in L2:M5:
=H2*XLOOKUP(H2,$L$2:$L$5,$M$2:$M$5,,-1)The -1 match mode finds the highest tier the rep has reached. For marginal tiers (each band paid at its own rate), use a helper column per band.
Pipeline
- Weighted pipeline:
=SUMPRODUCT(D2:D200,E2:E200)(deal value × probability). See SUMPRODUCT. - Deals closing this month:
=COUNTIFS(F2:F200,">="&EOMONTH(TODAY(),-1)+1,F2:F200,"<="&EOMONTH(TODAY(),0))
Rankings
=RANK.EQ(H2,$H$2:$H$20)See RANK.
Growth
Month-over-month change: see percent change.
Frequently asked questions
How do I track sales in Excel?
Keep one row per sale with date, rep, product and amount, then summarise with SUMIFS or a pivot table.
How do I calculate commission in Excel?
Multiply sales by a rate; for tiers, look the rate up from a table with XLOOKUP or VLOOKUP approximate match.
How do I calculate sales target achievement?
Divide actual sales by the target and format as a percentage.
What Excel skills do sales roles need?
SUMIFS, percentages, lookups, ranking and basic charts.