Excel Sales Tracking Template with Commission: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can build a sales tracking template with commission in Excel using a few core formulas: a lookup to pull the commission rate, a multiplication for the payout, and a SUMIF or pivot table to total by salesperson. The simplest version uses a rate table and the VLOOKUP or XLOOKUP function; below is a complete walkthrough, plus common pitfalls and alternatives.

Why Use Excel for Sales Commission Tracking

Excel remains a popular choice for small and mid-sized sales teams because it is flexible, inexpensive, and already installed on most office computers. A well-designed template lets you track individual deals, calculate payouts automatically, and produce a monthly summary without buying a dedicated CRM or commission software. You control the logic, so you can handle tiered rates, split commissions, or quota bonuses without waiting for a vendor to add a feature.

That said, Excel is not a database. It works best when you have a few dozen to a few hundred rows per month, and when the commission structure is relatively stable. If you have thousands of transactions or complex approval workflows, a purpose-built tool may be better—but for many teams, a spreadsheet is enough.

Core Components of a Commission Template

Every sales commission template needs the same basic building blocks, regardless of your exact structure:

  • Sales log: one row per sale, with columns for date, salesperson, customer, product/service, sale amount, and commission rate (or tier).
  • Rate table: a separate area that maps salesperson names to their base rate, or maps sales amounts to tiered rates.
  • Commission formula: calculates the payout for each row.
  • Summary section: totals by salesperson, by month, or by product, using SUMIF or a pivot table.
  • Dashboard (optional): a few charts showing top performers and monthly trends.

You can start with a single sheet, but separating the log, rate table, and summary onto different tabs makes the template easier to maintain and less prone to accidental overwrites.

Step-by-Step: Build a Basic Commission Tracker

Here is a concrete example that assumes a flat commission rate per salesperson. Open a new workbook and create three sheets: SalesLog, Rates, and Summary.

1. Set up the Rates sheet (columns A and B):

  • A1: Salesperson, B1: Rate
  • A2: Alice, B2: 0.10 (10%)
  • A3: Bob, B3: 0.08 (8%)
  • A4: Carol, B4: 0.12 (12%)

2. Set up the SalesLog sheet (columns A–E):

  • A1: Date, B1: Salesperson, C1: Customer, D1: Sale Amount, E1: Commission
  • Enter a few sample rows, e.g., A2: 2025-01-05, B2: Alice, C2: Acme Corp, D2: 10000

3. Add the commission formula in E2:

With the rate table on the Rates sheet (names in A2:A4, rates in B2:B4), enter this in E2:

=D2*VLOOKUP(B2,Rates!$A$2:$B$4,2,FALSE)

This looks up Alice’s rate (0.10) and multiplies it by the sale amount (10000), returning 1000. Copy the formula down for all sales rows. If you use Excel 2021 or Microsoft 365, you can replace VLOOKUP with XLOOKUP:

=D2*XLOOKUP(B2,Rates!$A$2:$A$4,Rates!$B$2:$B$4)

On Mac, the formulas and syntax are identical; just use the same cell references.

4. Build the Summary sheet to total commissions per person. In A2:A4 list the names, and in B2 enter:

=SUMIF(SalesLog!$B$2:$B$100,A2,SalesLog!$E$2:$E$100)

This sums the commission column for every row where the salesperson matches. For the sample data, Alice’s total is 1000 (assuming only one sale).

For a monthly breakdown, add a helper column in SalesLog that extracts the month from the date, e.g., in F2: =TEXT(A2,"YYYY-MM"), then use SUMIFS with two criteria (salesperson and month).

Handling Tiered and Variable Commission Rates

Flat rates are simple, but many sales plans use tiers: for example, 5% on the first $50,000, 7% on the next $50,000, and 10% above that. To calculate tiered commission on a single sale amount, you can use a nested IF formula, but it gets messy. A cleaner approach is to create a tier table and use a formula that sums the incremental amounts.

Assume the tier table is on a sheet named Tiers with thresholds in A2:A4 (0, 50000, 100000) and rates in B2:B4 (0.05, 0.07, 0.10). For a sale amount in D2, the commission is:

=SUMPRODUCT((D2>Tiers!$A$2:$A$4)*(D2-Tiers!$A$2:$A$4)*(Tiers!$B$2:$B$4-Tiers!$B$1:$B$3))

This works by calculating the amount in each bracket and multiplying by the rate difference. For a $120,000 sale, it returns 9,500 (5% of 50k = 2,500, 7% of 50k = 3,500, 10% of 20k = 2,000). Note: this formula assumes the rate applies to the entire bracket, not just the excess—adjust if your plan uses a different method.

If you have a quota-based bonus (e.g., extra $1,000 if monthly sales exceed $100,000), use an IF formula in the summary: =IF(B2>=100000,B2+1000,B2).

Common Mistakes and How to Avoid Them

Even a simple template can produce wrong numbers if you are not careful. Here are the most frequent issues:

  • Using VLOOKUP with approximate match (TRUE) when you need exact match. Always use FALSE or 0 for exact lookup, or you may get the wrong rate.
  • Hardcoding rates in formulas instead of referencing a rate table. When a rate changes, you would have to edit every formula; a table lets you update one cell.
  • Not locking cell references when copying formulas. Use $ signs (e.g., $B$2:$B$4) so the range does not shift.
  • Mixing text and numbers in the salesperson column (e.g., extra spaces) causes lookups to fail. Use Data Validation to create a dropdown list of names.
  • Forgetting to recalculate if you have manual calculation mode. Press F9 (Windows) or Cmd+= (Mac) to refresh.

Another misconception is that Excel cannot handle split commissions (two reps on one deal). It can—just add a “Split %” column and multiply the commission by that percentage. For example, if Alice gets 60% and Bob gets 40%, enter 0.6 in one row and 0.4 in another, both referencing the same sale amount.

Comparing Excel to Dedicated Commission Software

Excel is not the only option. Dedicated commission tools like CaptivateIQ, Spiff, or Performio automate calculations, handle complex plans, and provide audit trails, but they cost money and require setup time. A simple comparison:

  • Excel: free (if you already have Office), fully customizable, no learning curve for basic use. Downside: manual data entry, version control issues, and it is easy to break formulas.
  • Google Sheets: similar to Excel but cloud-based, so multiple people can edit simultaneously. The functions are mostly the same, but some advanced Excel functions (e.g., XLOOKUP) are not available; use VLOOKUP or INDEX/MATCH instead.
  • CRM-integrated tools: automatically pull data from your CRM (like Salesforce), reducing manual entry. Best for larger teams with complex plans.

If you are just starting, Excel is a reasonable first step. If you find yourself spending more than a few hours a month fixing spreadsheets or reconciling errors, it may be time to evaluate a dedicated system.

Frequently Asked Questions

Can I use a pivot table to summarize commissions? Yes. Select your sales log data (including the Commission column), go to Insert > PivotTable, then drag Salesperson to Rows and Commission to Values. To show monthly totals, add the Date field to Columns or Rows and group by month (right-click a date in the pivot table > Group > Months).

How do I handle commission rates that change over time? Add a “Rate Effective Date” column to your rate table and use a lookup that checks the sale date. A simple way is to use XLOOKUP with a range lookup (if your table is sorted by date), or use SUMPRODUCT to find the rate that was active on that date. This is more complex but doable.

What if I need to track commissions for a team with different plans? Create separate rate tables for each plan, or add a “Plan” column in the sales log and use nested IFs or a lookup that references both the salesperson and plan. For example, use INDEX/MATCH with two criteria to find the correct rate.

Is it better to use a template from the internet or build my own? Pre-built templates save time, but they may not match your exact structure. If you use one, check the formulas carefully—many free templates have errors or are locked. Building your own from scratch (as shown above) takes about 30 minutes and ensures you understand every formula.

Can I automate data entry from my CRM into Excel? Yes, many CRMs allow you to export sales data to CSV, which you can then import into Excel. You can also use Power Query (Data > Get Data) to connect directly to a database or online source, but that requires some technical setup. For most small teams, a manual copy-paste once a week is sufficient.

How do I protect the formulas from being accidentally changed? Select the cells with formulas, right-click > Format Cells > Protection, and check “Locked”. Then go to Review > Protect Sheet, and set a password. This prevents others from editing those cells while still allowing them to enter data in the unlocked input cells.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.