The 8 Columns Every Commission Tracking Sheet Needs

Most commission tracking spreadsheets are built the same way: deal, amount, rate, paid, done. Five columns, and they work fine — right up until a real deal goes through them. Then the sheet keeps producing a number, the number is wrong, and nothing tells you.

So rather than list best practices, here is one deal, followed from signature to final check, with the columns it needs at each step. It is a roof, because roofs pay in draws and get charged back, which is where most sheets fall over. If your spreadsheet handles this deal correctly it will handle almost anything an HVAC, bath, kitchen or window rep throws at it.

The eight columns

Here is the whole answer up front. These eight are the deal-level columns — one row per job. Two of them are fed by separate tabs, which is the point of columns five and seven, and there is a ninth tab for expenses at the end. The rest of this post is why each column is there and what goes wrong without it.

The deal

A $52,000 tear-off and replacement. Signed February 20th. Your pay plan is tiered per deal — 5% on the first $20,000, 8% on the next $20,000, 11% on anything above that.

Columns 1–4: what the deal is actually worth

The first three columns are easy: the deal, the type of work, the contract total. The fourth is where sheets start losing money, because a tier ladder will not fit in a rate cell.

There is no percentage you can type in one cell that gets you to $3,920 from $52,000 — well, 7.54% does, but you would have to work out the answer first to know that, and it is a different number on every deal because the blend moves with contract size.

So reps round. Type 8% and your sheet says $4,160: you are $240 over, and you will go and argue for money you were never owed. Type 7% and it says $3,640: you are $280 under. That second one is the one that costs you. A sheet that under-states what you are owed does not feel broken. It feels fine. It just quietly stops catching short payments, because every check looks generous.

The fix is not a cleverer formula. It is three small cells for the band amounts and a fourth that adds them up. Ugly, correct, and you can see it working.

If you are paid a flat percentage of the sale — common on windows and doors — none of this applies and one rate cell is genuinely fine. If your plan pays a different rate on company leads than on self-generated ones, which is common in exterior remodeling, you need the rate to live on the deal row rather than in a header, because it changes deal to deal.

Columns 5–6: payments are rows, and the date is half the point

The money arrives in pieces. A deposit at signing, a draw at install, the balance after final inspection — weeks or months apart. Most sheets hold that in a single “paid to date” cell you overwrite each time.

Overwriting works for the total and destroys everything else. Once the deposit and the draw are one number, you cannot answer “when did they last pay me?” — and that question is the entire early-warning system. Give each payment its own row on a second tab, then sum them into the deal.

That second tab is what feeds column five. It costs you one extra line per payment and it buys back every date you would otherwise have thrown away.

Then add a last payment column. The roof went on in April and the draw cleared on the 28th. Nothing has arrived since — not in May, June, July or August. That is 123 days of silence on a job that was finished in the spring, and a “paid to date: $2,700” cell shows you none of it. The cell looks identical whether the last check came yesterday or four months ago.

Sort by that column and the deals that need a phone call rise to the top on their own. It is the single most useful column in the sheet and almost nobody has it.

Column 7: a chargeback goes against what you are OWED, not what you were PAID

This is the one that costs the most money, and nearly every sheet gets it backwards.

Say $600 comes back on this job — a supplement the insurer denied, a discount the owner negotiated after the fact, whatever. Your company recovers it by taking it off your next check. What most reps do is subtract $600 from paid-to-date, because that is the column money seems to live in:

That is wrong twice over. You actually received $2,700, not $2,100 — the $600 never left your pocket. And now the sheet says you are owed $1,820, which is not close.

Here is the rule, and it is worth reading twice. When a chargeback is withheld from a future commission payment, it comes off what you are still owed. It does not touch Paid to Date. Paid to Date is a record of money that reached you, and $2,700 reached you — the company simply intends to hand over $600 less of what is left. Same deal, done properly:

$620, not $1,820. A $1,200 difference on a $52,000 job, from putting one number in the wrong column. And it errs upward, so you would chase a company for twelve hundred dollars you were never owed — the conversation that makes a rep look like they cannot count.

The one exception: if you actually wrote a check back to the company, or they took it out of a deal that was already closed and paid, then it did leave your pocket and it comes off what you kept. Two different things, which is why the Adjustments column needs a note beside the amount saying which it was. Withheld, or out of pocket. Never assume.

Column 8, and the tab most sheets are missing

“Still owed” is just column 4 minus column 5 minus anything withheld. If it is a formula you can trust, you never have to do this arithmetic in your head again.

What almost no commission tracking spreadsheet has is expenses. Fuel to run the territory, ladders and tools, samples, the phone, sometimes permits. Commission is the top line and it is not what you keep. How much tax comes off before it reaches you depends on how you are paid — a W-2 rep has it withheld, a 1099 rep is setting it aside themselves — but either way the gap between commission and take-home is wider than most people carry in their head. A tab with a date, an amount and a category is enough to close it.

How to keep track of commissions without trusting the sheet blindly

Three checks. Do them once a month and the sheet is much less likely to drift.

Check three is the one people skip and it is the one that catches everything. If your sheet says you have collected $41,000 this year and your account says $38,600, one of those is a fact and the other is a spreadsheet. Find the $2,400 before you need it in an argument.

When the sheet stops being enough

Everything above is doable in Excel or Google Sheets and costs nothing. Build it and it will serve you for years. The honest limit is that every one of those checks is manual: nothing in the file tells you the tier bands were typed wrong, or that a chargeback went in the wrong column, or that a deal has been silent for four months. You have to go looking.

There is a point where that stops being worth your evening. We wrote about exactly where that line falls in Commission Tracking Spreadsheet vs. App — worth reading before you decide either way.

If you would rather not build it from scratch, our free commission tracker template already has these columns, the payment rows and the formulas in it. It is a spreadsheet, it is free, and it is yours to keep.

SaleTrakk does the same job with the checks running on their own. Either way, the columns are the part that protects you — build them somewhere.