A commission spreadsheet can feel like the easiest way to manage sales commissions. Open Excel. Add the sales rep name. Add the deal value. Add the commission rate. Add a formula. Done.
At least, that is how it starts.
For small teams with simple compensation plans, a spreadsheet can work for a while. It gives sales teams a basic way to organise sales data, calculate commissions, and track payouts without buying new software. You can use Microsoft Excel, Google Sheets, or an excel template to build a basic commission sheet in an afternoon.
The problem is that sales compensation rarely stays simple.
The team grows. The commission structure changes. New products get added. Sales reps move territories. Quotas change mid-year. Finance teams need better reporting. Reps start asking why their commission amounts do not match what they expected. Suddenly, the sales commission spreadsheet that was supposed to save time becomes a monthly mess.
This guide explains how to create a commission spreadsheet, what fields to include, which formulas to use, and how different commission structures work in spreadsheet format. More importantly, it explains where spreadsheets start to fail and when it makes sense to move to commission tracking software.
What Is a Commission Spreadsheet?
A commission spreadsheet is a spreadsheet used to calculate, track, and report sales commissions.
At a basic level, it helps sales teams connect sales activity to commission payouts. It usually includes deal values, revenue, commission rate, commission amounts, payment status, and the sales rep name attached to each deal.
A simple commission sheet might work for one or two sales representatives. But once you have multiple sales reps, different commission rates, monthly quota rules, accelerators, clawbacks, residual commissions, or split deals, the spreadsheet becomes harder to trust.
That matters because sales compensation is not just an admin task. If sales reps do not trust their numbers, they lose confidence in the commission plan. If finance teams cannot audit the payout, the business loses control. Commission errors can quickly erode profits and trust, and if managers cannot see sales performance clearly, compensation plans stop helping the business grow.
A spreadsheet can help you get started. It should not be your long-term commission tracking system.
How to Create a Commission Spreadsheet
A good commission spreadsheet needs structure. Do not put everything into one giant tab and hope for the best. That is how hidden rows, broken formulas, manual errors, and calculation errors creep in.
The preferred format is to use separate tabs:
Raw data tab
This is where you import or enter sales data. It should include the sales rep name, Deal ID, customer name, close date, product type, revenue, deal size, and payment status.Commission rate table tab
This is where you define commission rates based on role, product, monthly quota, specific revenue thresholds, or other rules. Lock this tab so people cannot accidentally change the commission rate.Commission calculations tab
This is where the spreadsheet applies formulas to calculate commissions. This tab should pull from the raw data and rate table rather than relying on manual calculations.Summary tab
This is where managers and finance teams can see total sales, total payout, commission amounts by sales rep, and payout schedules.Audit or approval tab
This tracks who approved the numbers, when they were reviewed, and whether any adjustments were made.
At minimum, your commission sheet should include the following columns:
Column | Purpose |
|---|---|
Deal ID | Prevents duplicate sales entries |
Sales rep name | Assigns credit to the right person |
Close date | Supports monthly or quarterly reporting |
Customer name | Gives context for each deal |
Product or service | Supports different commission rates |
Revenue | Shows the dollar amounts used for commissions |
Commission rate | Defines the percentage used |
Commission amount | Shows what the rep earns |
Payment status | Tracks paid, unpaid, clawed back, or pending |
Approval status | Confirms whether the payout has been reviewed |
Freeze the header row and first column. Use data validation for fields like sales rep name and product type. This prevents typos that break formulas. If “Jon Smith” and “John Smith” both exist in the sheet, your SUMIFS formula may miss sales commissions that should be paid.
Basic Commission Formula
The simplest formula for calculating commissions is:
Revenue x Commission Rate = Commission Amount
For example:
$10,000 x 10% = $1,000
In Excel or Google Sheets, that might look like:
=D2*E2
If revenue is in column D and the commission rate column is column E, the formula returns the commission amount.
For flat-rate sales commissions, this is fine. But most sales compensation plans are not that simple. Many compensation plans use tiered commission, quota attainment, bonuses, draw against commission, residual commissions, or different commission rates based on product margin.
That is where the spreadsheet starts getting messy.
Commission Structures You Can Build in a Spreadsheet
Different commission structures need different spreadsheet logic. Some are relatively easy to model. Others become error prone fast.
Here are the main types.
Flat-Rate Commission Structure
A flat-rate commission structure pays the same commission percentage on every eligible sale.
Example:
A sales rep earns 8% on all closed-won revenue.
If the rep closes $50,000 in total sales, the commission calculation is:
$50,000 x 8% = $4,000
Spreadsheet format
Flat-rate commissions are the easiest to manage in a commission sheet. You need columns for sales rep name, revenue, commission rate, and commission amount.
Pros
Simple to explain to sales reps
Easy to calculate in Excel or Google Sheets
Low setup effort for small teams
Useful when all products have similar margins
Easy to summarise with a pivot table
Cons
Does not reward over-performance as well as tiered commission
Can overpay on low-margin deals
Does not reflect deal quality
Does not work well when sales teams sell multiple products with different margins
Still depends on accurate sales data and manual upkeep
Breaks down when you add exceptions, bonuses, or different commission rates
When to use a spreadsheet
A spreadsheet can work here if the team is small, the commission plan is simple, and the same commission rate applies to every deal.
If you have multiple rates, territories, product lines, or manual overrides, it is time to be careful.
Tiered Commission Structure
A tiered commission structure increases the commission rate as sales reps hit specific revenue thresholds.
Example:
Revenue band | Commission rate |
|---|---|
$0 to $50,000 | 5% |
$50,001 to $100,000 | 8% |
$100,001+ | 12% |
Tiered commission is popular because it rewards reps for exceeding quota. It can enhance sales performance when the structure is clear and achievable.
Spreadsheet format
A tiered commission structure usually needs a commission rate table and formulas that automatically calculate the right rate.
You can use XLOOKUP or VLOOKUP to pull the correct values from a setup tab. You can also use nested IF formulas, but those get ugly quickly.
Example nested IF formula:
=IF(D2<=50000,D2*5%,IF(D2<=100000,D2*8%,D2*12%))
That works for a basic model. It does not work well when tiers apply progressively.
For example, if the first $50,000 pays 5%, the next $50,000 pays 8%, and anything above $100,000 pays 12%, the formula needs to calculate each band separately. This is where many sales commission spreadsheet errors happen.
Pros
Rewards over-performance
Helps motivate sales reps to exceed monthly quota
Good for sales teams with clear targets
Useful for modelling quota attainment
Can support different commission rates based on sales volume
Cons
Formulas become complex quickly
Easy to miscalculate partial tiers
Hard for reps to audit
Manual errors can create overpayments or underpayments
Mid-period quota changes are painful
Hidden rows or copied formulas can break commission calculations
Disputes become more likely when reps cannot see how the number was calculated
When to use a spreadsheet
Use a spreadsheet for tiered commission only when tiers are simple, rates rarely change, and someone owns the audit process.
If your sales teams use accelerators, partial tiers, retroactive rates, or complex compensation plans, commission tracking software is the safer option.
Residual Commissions
Residual commissions pay sales representatives ongoing commission on recurring revenue.
Example:
A sales rep earns 3% of recurring revenue for as long as the customer stays active.
This is common in subscription businesses, account management roles, and customer retention models.
Spreadsheet format
Residual commissions need more than a closed-won deal list. You need customer status, billing dates, renewal status, cancellations, payment history, and future commissions.
This usually means pulling data from the CRM, billing system, or subscription platform. If you are copying and pasting that data manually, the risk goes up.
Pros
Rewards long-term customer value
Encourages reps to sell deals that stay
Works well for recurring revenue models
Can align sales compensation with customer retention
Helps track revenue beyond the first sale
Cons
Very hard to maintain manually
Requires clean customer and billing data
Cancellations, downgrades, and late payments create payout issues
Future commissions are hard to forecast in spreadsheets
Sales rep ownership changes can create disputes
Audit trails are weak unless manually documented
Commission calculations depend on data from multiple systems
When to use a spreadsheet
A spreadsheet can work for a basic residual model with a small number of accounts and stable customer data.
If customer status changes often, or if residual commissions continue for months or years, a spreadsheet becomes a bad fit.
Draw Against Commission
A draw against commission gives a sales rep an advance payment that is later offset against earned sales commissions.
Example:
A rep receives a $4,000 monthly draw. If they earn $6,000 in commission, they receive the $2,000 difference. If they earn $3,000, the remaining $1,000 may carry forward, depending on the commission plan.
Spreadsheet format
This model needs fields for base salary, draw amount, earned commission, repayment balance, and net payout.
A basic formula might be:
=Earned Commission – Draw Amount
But that is too simple for many compensation plans. Some draws are recoverable. Some are non-recoverable. Some reset monthly. Some carry forward.
Pros
Gives sales reps income stability
Useful for new reps during ramp-up
Can support longer sales cycles
Helps sales teams manage cash flow uncertainty
Cons
Easy to confuse earned commission with net payout
Carry-forward balances require careful tracking
Reps may dispute repayment logic
Manual calculations can create payroll issues
Formula errors can compound month after month
Finance teams need a clear audit trail
When to use a spreadsheet
A spreadsheet can work for a short-term draw during onboarding, especially for small teams.
If draws carry forward, vary by rep, or connect to quota attainment, you should not rely on manual spreadsheets.
Gross Margin Commission
Gross margin commission pays reps based on profit margin rather than total revenue.
Example:
A rep sells a deal worth $50,000. The cost to deliver is $30,000. Gross margin is $20,000. If the commission rate is 10%, the rep earns $2,000.
Spreadsheet format
You need columns for revenue, cost, margin, commission rate, and commission amount.
Formula:
=(Revenue – Cost) * Commission Rate
Pros
Encourages profitable selling
Reduces the risk of overpaying on low-margin deals
Good for sales teams with discounting flexibility
Helps align sales compensation with business profitability
Cons
Requires accurate cost data
Reps may not have visibility into margin
Cost changes can affect payout after the deal closes
Finance teams need to validate inputs carefully
Manual errors can cause major payout issues
Spreadsheets struggle when margin data comes from another system
When to use a spreadsheet
A spreadsheet can work if gross margin is easy to calculate and cost data is stable.
If costs change, discounts vary, or margin data comes from finance systems, use commission tracking software.
Split or Team-Based Commissions
Split commissions apply when multiple people contribute to one deal.
Example:
An AE receives 70% credit and an SDR receives 30% credit.
Spreadsheet format
You need one row per credited person or separate columns for each rep. One row per credited person is usually cleaner.
Columns might include Deal ID, sales rep name, role, split percentage, revenue credit, commission rate, and commission amount.
Pros
Supports team selling
Useful when SDRs, AEs, account managers, or channel partners share credit
Helps recognise work from other reps involved in the deal
Can improve collaboration across sales teams
Cons
Duplicate Deal IDs can inflate total sales if not handled properly
Split rules are often inconsistent
Formula logic gets complicated
Sales reps may dispute credit allocation
Manual work increases with every shared deal
Reporting can become unreliable if the same revenue is counted twice
When to use a spreadsheet
Use a spreadsheet only if split rules are rare and simple.
If split commissions are common, spreadsheet-based commission tracking becomes risky fast.
Bonus and MBO Commission Plans
Some compensation plans include bonuses for specific goals, such as new logo wins, product launches, customer expansion, or MBOs.
Example:
A sales rep earns a $2,500 bonus for hitting 120% quota attainment or closing five high value deals in a quarter, which should fit into broader sales compensation best practices for revenue growth.
Spreadsheet format
You need columns for target, actual result, attainment percentage, bonus eligibility, approval status, and payout amount.
Conditional formatting can highlight rows where the rep qualifies for a bonus.
Pros
Useful for short-term goals
Helps direct behaviour toward strategic priorities
Can support product pushes or quarterly campaigns
Easy to model at a basic level
Cons
Often requires manual approval
Hard to standardise across roles
Eligibility rules can be subjective
Reps may not understand how bonuses are calculated
Spreadsheets do not provide real time insights
Manual tracking creates disputes when goals are unclear
When to use a spreadsheet
A spreadsheet can work for one-off bonuses.
If MBOs or bonuses are part of your regular sales compensation plans, build them into a proper commission tracking system.
How to Do Commission in Excel
To do commission in Excel, start with a clean structure.
Use one tab for raw data, one tab for commission rates, and one tab for commission calculations. Avoid typing commission rates directly into every row. That creates too much room for error.
For a flat commission rate, the formula is simple:
=Revenue * Commission Rate
For a rate lookup, use XLOOKUP or VLOOKUP.
Example:
=XLOOKUP(Product Type, Rate Table Product Column, Rate Table Commission Rate Column)
You can also use SUMIFS to calculate total commissions for a specific agent over a specific period.
Example:
=SUMIFS(Commission Amount Column, Sales Rep Column, Sales Rep Name, Date Column, “>=1/1/2026”, Date Column, “<=1/31/2026”)
Use a pivot table to summarise total earnings by rep, month, product, or region. Pivot tables are useful because they let you view total sales volume and commission amounts without writing complex formulas.
Still, Excel spreadsheets are not built to manage approvals, audit trails, CRM sync, payment status, real time visibility, and ongoing plan changes. They can calculate. They cannot govern the process properly.
Google Sheets vs Excel for Commission Tracking
Google Sheets is useful when multiple people need access to the same commission tracker. It is easier to collaborate in real time, and you can protect sheets, lock ranges, and use basic integrations.
Excel is often stronger for advanced modelling, large data sets, and finance workflows. Many finance teams still prefer Excel because it is familiar and powerful.
But both tools have the same core issue: they rely on people keeping the data, formulas, permissions, and approvals clean.
Google Sheets makes collaboration easier. It does not remove the risk of manual errors.
Excel gives you powerful formulas. It does not automatically validate whether the commission structure is still right.
Best Practices for Commission Spreadsheets
If you are going to use a spreadsheet, at least make it hard to break.
Use these best practices:
Keep raw data separate from formulas
Lock the commission rate table
Use data validation for sales rep name and product type
Add a unique Transaction ID or Deal ID
Use XLOOKUP or VLOOKUP instead of manual rate entry
Wrap formulas with IFERROR to avoid messy error messages
Use SUMIFS for monthly or rep-level totals
Freeze the header row
Avoid hidden rows
Document every commission structure
Record approval dates
Archive monthly snapshots
Reconcile against CRM totals every month
Spot-check high value deals before payout
Limit editing access to the right people
These steps help with reducing errors. They do not eliminate the bigger issue.
A spreadsheet still needs someone to babysit it.
When a Commission Spreadsheet Makes Sense
A spreadsheet can make sense when:
You have a very small sales team
The commission plan is simple
You use one flat commission rate
You have low transaction volume
You do not need real time insights
You are testing a new commission plan
You need a temporary free template before software is implemented
For example, a two-person sales team with one product and one commission rate can probably use a basic excel template for a while, whereas larger organisations need to think more deliberately about simple vs complex sales compensation plan design.
The moment you add tiers, multiple roles, split commissions, residual commissions, approval workflows, or CRM data sync, you are moving beyond what spreadsheets handle well.
When to Move Beyond Commission Spreadsheets
You should move beyond spreadsheets when commission tracking starts creating more work than it saves.
Watch for these signs:
Sales reps are questioning payouts every month
Finance teams are spending days checking formulas
Commission calculations depend on multiple systems
You have different commission rates by product, role, or territory
You need real time visibility for reps or managers
Plan changes are hard to model
You rely on manual calculations
You have recurring revenue, clawbacks, or residual commissions
The team grows and the spreadsheet becomes slower, larger, or more fragile
You cannot easily audit who changed what
This is where commission tracking software becomes worth it.
Good commission tracking software can automate sales commission calculations across complex plans, apply the right commission structure, sync with CRM data, produce commission statements, and give reps visibility into what they have earned.
It also gives finance teams stronger controls, cleaner approval workflows, and better reporting.
The big win is not just that it saves time. It helps sales teams trust the compensation process.
What Is the Best Way to Track Commission?
The best way to track commission depends on your team size, compensation plans, and how complex your sales commissions are.
For a small team with a basic commission plan, a spreadsheet may be enough.
For growing sales teams, the better option is choosing the right sales commission software connected to your CRM, payroll, and finance systems.
The reason is simple: sales compensation needs accuracy, transparency, and trust. Spreadsheets can help calculate commissions, but they are weak at workflow, governance, version control, and audit trails, especially once you try to maximize sales commissions for higher earnings at scale.
Once commission calculations affect multiple reps, multiple departments, and real money, you need more than formulas and should focus on maximising the ROI of your incentive compensation program.
Free Template or Commission Software?
A free template can help you understand the moving parts of a commission sheet.
It can show you the key elements:
Sales rep name
Deal ID
Revenue
Commission rate
Commission amount
Monthly quota
Quota attainment
Payment status
Approval status
That is useful.
But a free template will not fix a messy commission structure or ensure concepts like on-target earnings (OTE) are modelled correctly. It will not clean your sales data. It will not stop people from changing formulas. It will not automatically calculate complex payouts across multiple compensation plans. It will not give sales reps real time insights. It will not provide a reliable audit trail.
A template is a starting point. It is not a commission operations system.
Final Takeaway
A commission spreadsheet is fine when the commission plan is simple and the stakes are low.
But sales commissions do not stay simple for long.
Once your sales teams have different roles, products, quotas, accelerators, split credit, residual commissions, clawbacks, or approval workflows, spreadsheets become a liability. They are convenient at first, then painful later.
If you are still trying to manage serious sales compensation in spreadsheets, it is probably time to talk to someone who works with this every day.
OnCentive helps companies design, implement, and optimise sales
Compensation systems across leading SPM and ICM platforms. If your commission process depends on fragile spreadsheets, manual work, and late-night formula checks, get expert advice before the next payout cycle turns into another clean-up job.
FAQs
Create separate tabs for raw data, commission rates, commission calculations, and summary reporting. Add columns for sales rep name, Deal ID, revenue, commission rate, and total payout. Use formulas to calculate commissions, and use data validation to prevent errors.
The basic formula is:
Revenue x Commission Rate = Commission Amount
For example, if a sales rep closes $20,000 in revenue at a 10% commission rate, the commission amount is $2,000.
Tiered commission calculations depend on whether the higher rate applies to all revenue once the threshold is reached or only to revenue above each threshold. This distinction matters. Many spreadsheet errors happen because the tier logic is not clearly documented, so using a dedicated sales commission calculator with common structures and formulas can help you validate your math.
Yes. Google Sheets can automatically calculate commissions using formulas, lookup tables, SUMIFS, pivot tables, and protected ranges. But it still relies on clean data, correct formulas, and manual oversight.
For very small teams, a structured spreadsheet can work. For growing sales teams with complex compensation plans, commission tracking software is usually the better option because it reduces manual work, improves accuracy, and gives reps clearer visibility.