Free Commission Tracker Template (Spreadsheet + Live Tracker)

Most commission tracking starts the same way: a spreadsheet built in twenty minutes at the start of a quarter, and then quietly abandoned by week three. The math was never the hard part. Keeping it current is.

This post gives you the template that works — the columns, the formulas, and the tiered-rate setup that trips most people up — and then covers the part nobody plans for, which is what happens when the file stops being updated.

A commission number that only finance can see doesn't motivate anyone. It just gets reconciled.

What a commission tracker template needs

Seven columns, and one of them is the one everyone forgets.

The close date is the one to insist on. Without it you can't filter to a month or a quarter, so the sheet becomes an ever-growing flat list that has to be rebuilt by hand every time payroll asks for a number.

Keeping the rate on each line rather than in the formula matters for a different reason: split deals, channel deals and one-off SPIFFs all pay something other than the standard rate. If the rate lives in the formula, the first exception breaks the model.

The free spreadsheet template

Two blocks on one sheet. A deal log, and a per-rep summary that reads from it.

Block 1 — the deal log (columns A–F)

Format column E as a percentage and column F as currency. Fill =D2*E2 down the whole column.

Block 2 — the per-rep summary (columns H–K)

Put the plan settings somewhere fixed — say N2 for quota, N3 for the base rate, N4 for the accelerator rate — then:

To restrict the totals to one month or quarter, add a start date in N5 and an end date in N6 and extend the SUMIFS:

=SUMIFS($D:$D, $A:$A, $H2, $C:$C, ">="&$N$5, $C:$C, "<="&$N$6)

That one change is what turns a flat log into something you can run payroll from.

Tiered commission: the formula most people get wrong

A tiered plan pays a base rate up to quota and a higher accelerator rate above it. The formula is a split, not a lookup:

=MIN($I2,$N$2)*$N$3 + MAX(0,$I2-$N$2)*$N$4

MIN takes the portion at or below quota and pays the base rate. MAX(0, ...) takes only the portion above quota and pays the accelerator. Set the accelerator equal to the base rate and it behaves as a flat plan, which makes it safe to use as your default.

Worked example, with a $120,000 quota, 5% base and 8% above: a rep who closes $150,000 earns 5% of the first $120,000, which is $6,000, plus 8% of the $30,000 over the line, which is $2,400. Total $8,400 — against $7,500 on a flat 5%. That $900 difference is the part that changes what people do in the last week of a quarter.

Marginal tiers vs. cliff plans

Here is the distinction that causes most commission disputes. The formula above is marginal: each portion of revenue is paid at its own rate. Some plans are instead cliff plans, where crossing quota re-rates everything at the higher percentage:

=$I2 * IFS($J2>=1.5, 0.10, $J2>=1, 0.08, TRUE, 0.05)

Under that version, our rep at $150,000 earns 8% of the whole $150,000 — $12,000, not $8,400. Both are legitimate plans and both are common. What causes arguments is a comp plan that says one thing and a spreadsheet that calculates the other. Decide which you're running before you write the formula, and put the answer in a note on the sheet.

Setting it up in Google Sheets

Create the two blocks above, then select the deal log range and use Data → Create a filter so you can slice by rep or close date without touching the formulas. Freeze the header row from View → Freeze → 1 row.

If your deals already live somewhere else — a CRM export, another sheet, a form — pull them in with IMPORTRANGE rather than pasting, so the log updates itself. Our guide to building a leaderboard from Google Sheets covers connecting a sheet to a live display once the numbers are flowing.

Setting it up in Excel

Identical formulas — SUMIFS, MIN, MAX and IFS all behave the same way. The one upgrade worth making: select the deal log and press Ctrl+T to convert it to a real Table. Formulas then extend to new rows automatically, so a rep adding a deal at the bottom doesn't quietly fall outside your SUMIFS range. If you also want the result on a screen rather than in a file, see publishing an Excel leaderboard online.

Where the spreadsheet breaks down

Two things kill commission spreadsheets, and neither is arithmetic.

The first is maintenance. Someone owns the file, and updating it isn't actually anyone's job. It stays accurate for two or three weeks and then drifts, until it's rebuilt in a hurry the day before payroll.

The second is visibility, and it's the expensive one. The rep who would push for one more deal can't see how close they are to the accelerator without asking someone to send them the file. A commission number that lives in a spreadsheet only finance opens has no motivational effect at all — it's a reconciliation record, not a scoreboard.

That second problem is worth taking seriously, because the accelerator only works if people can see where they stand relative to it. A tiered plan nobody can check is just a flat plan with extra steps.

Tracking commissions live instead

A live commission board is the same numbers with the visibility problem solved. Add each rep, set the score format to currency, and share one link. Reps log their own deals from their phone, the ranking re-sorts, and the board opens on a TV on the sales floor or gets pinned in a Slack channel.

Because the people with the newest information are the ones entering it, the maintenance problem mostly solves itself too.

Teams that would rather not display individual earnings track deal count, revenue, or quota attainment instead and keep the dollar figures in the admin view — the competitive effect comes from the ranking, not the amounts. Our free commission tracker does the tiered math for you first, so you can check a plan before you commit to it.

Which to use

Use the spreadsheet when you need an auditable record: line-by-line deals, adjustments, clawbacks, something finance can reconcile against payroll. That's what it's genuinely good at.

Use a live commission tracker for the running number the team actually looks at during the quarter. Most teams end up with both, and that's the right answer — a monthly reconciliation file, plus a board that keeps the target in view while there's still time to hit it.

For the wider setup — picking metrics, deciding what belongs on the display, and avoiding the leaderboard designs that demotivate the bottom half of the team — see how to create a sales leaderboard.